Sqlite - 返回某些值不同的行

发布于 2025-01-20 20:19:34 字数 878 浏览 1 评论 0原文

我有一个大表，一个包含学生考试结果的 SQLite 数据库。学生经常重考失败的考试，然后通过考试，如下面的学生 10001 所示。

+─────────────+────────+──────────────+──────────+────────+─────────────+
| student_id  | level  | name         | outcome  | grade  | date        |
+─────────────+────────+──────────────+──────────+────────+─────────────+
| 10001       | Higher | Mathematics  | Pass     | A      | 01/04/2022  |
| 10001       | Higher | Mathematics  | Fail     | F      | 01/02/2022  |
| 10002       | Higher | English      | Pass     | B      | 01/04/2022  |
+─────────────+────────+──────────────+──────────+────────+─────────────+

我希望能够利用这些数据做两件事。

如果还记录了通过条目，则仅返回未通过的考试。换句话说，图像表中从顶部算起的第二行。
返回整个表格，但这次没有最初失败的考试。换句话说，一切都减去 1 的结果。

如果student_id、级别和姓名全部匹配，则考试被视为补考。行中的其他值可以不同。

原文

I have a large table a SQLite database of student exam results. Students often resit failed exams and subsequently pass them as shown with student 10001 below.

+─────────────+────────+──────────────+──────────+────────+─────────────+
| student_id  | level  | name         | outcome  | grade  | date        |
+─────────────+────────+──────────────+──────────+────────+─────────────+
| 10001       | Higher | Mathematics  | Pass     | A      | 01/04/2022  |
| 10001       | Higher | Mathematics  | Fail     | F      | 01/02/2022  |
| 10002       | Higher | English      | Pass     | B      | 01/04/2022  |
+─────────────+────────+──────────────+──────────+────────+─────────────+

I would like to be able to do two things with this data.

Return only the failed exam should it have a also have a pass entry recorded. In other word the row 2nd from the top in the image table.
Return the whole table but this time without the initial failed exam. In other words everything minus the results from 1.

The exam is considered a resit if the student_id, level and name all match. The other values in the rows can be different.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

感性 2025-01-27 20:19:35

对于您想要的第一个结果集，您可以在 WHERE 子句中使用 EXISTS：

SELECT t1.*
FROM tablename t1
WHERE t1.outcome = 'Fail'
  AND EXISTS (
        SELECT 1
        FROM tablename t2
        WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
          AND t2.date > t1.date 
          AND t2.outcome = 'Pass'
      );

对于第二个结果集，因为要求是：

一切减去1的结果

获得它的最简单方法是使用第一个查询和 EXCEPT：

SELECT * 
FROM tablename
EXCEPT
SELECT t1.*
FROM tablename t1
WHERE t1.outcome = 'Fail'
  AND EXISTS (
        SELECT 1
        FROM tablename t2
        WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
          AND t2.date > t1.date 
          AND t2.outcome = 'Pass'
      );

请参阅演示。

注意也就是说，如果表中日期的格式与您问题中的示例数据相同，那么这些日期不具有可比性。
您应该将格式更改为 YYYY-MM-DD。

For the 1st resultset that you want, you can use EXISTS in the WHERE clause:

SELECT t1.*
FROM tablename t1
WHERE t1.outcome = 'Fail'
  AND EXISTS (
        SELECT 1
        FROM tablename t2
        WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
          AND t2.date > t1.date 
          AND t2.outcome = 'Pass'
      );

and for the 2nd resultset, since the requirement is:

everything minus the results from 1

the simplest way to get it is with the 1st query and EXCEPT:

SELECT * 
FROM tablename
EXCEPT
SELECT t1.*
FROM tablename t1
WHERE t1.outcome = 'Fail'
  AND EXISTS (
        SELECT 1
        FROM tablename t2
        WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
          AND t2.date > t1.date 
          AND t2.outcome = 'Pass'
      );

See the demo.

Note that, if the format of the dates in the table is the same as the sample data in your question then these dates are not comparable.
You should change the format to YYYY-MM-DD.

回复收藏 0 原文

~没有更多了~