仅从一组值中检索给定标识符的多个整体中的行和一个列值

Question

我有一个用于存储审计的数据库表，如下所示（它只是实际表的一个简单表示，其中STATUS可以有许多值中的一个）

ID | STUDENT_ID | COURSE_ID | STATUS
1  |     5      | 12        | Enrolled
2  |     5      | 12        | In-Progress
3  |     2      | 12        | Enrolled
4  |     2      | 12        | Completed
5  |     5      | 12        | Completed
6  |     2      | 14        | Enrolled

我需要找到STUDENT_ID的所有记录

注意-对于给定的学生ID，不应存在条目

输出表-

ID | STUDENT_ID | COURSE_ID | STATUS
3  |     2      | 12        | Enrolled
4  |     2      | 12        | Completed
6  |     2      | 14        | Enrolled

更新-如果我有另一个STUDENT_ID 2条目的状态为“进行中”，它仍应返回状态为已注册并已完成的课程。

ID | STUDENT_ID | COURSE_ID | STATUS
1  |     5      | 12        | Enrolled
2  |     5      | 12        | In-Progress
3  |     2      | 12        | Enrolled
4  |     2      | 12        | Completed
5  |     5      | 12        | Completed
6  |     2      | 14        | Enrolled
7  |     2      | 14        | In-Progress

输出表-

ID | STUDENT_ID | COURSE_ID | STATUS
3  |     2      | 12        | Enrolled
4  |     2      | 12        | Completed

Answer 1

正如您所提到的，您只需要这两种状态-“已注册”，“已完成”，您就可以通过子查询实现您的目标

SELECT t.* 
FROM   students t 
WHERE  student_id NOT IN (SELECT DISTINCT student_id 
                          FROM   students 
                          WHERE  status NOT IN ( 'Enrolled', 'Completed' ));

Answer 2

我只会排除STUDENT_ID

select t.*
from table t
where not exists (select 1 
                  from table t1 
                  where t1.student_id = t.student_id and 
                        t1.course_id = t.course_id and t1.status = 'In-Progress'
                 );

Answer 3

使用带有空测试的左连接

drop table if exists t;
create table t(ID int, STUDENT_ID int, COURSE_ID int, STATUS varchar(20));
insert into t values
(1  ,     5      , 12        , 'Enrolled'),
(2  ,     5      , 12        , 'In-Progress'),
(3  ,     2      , 12        , 'Enrolled'),
(4  ,     2      , 12        , 'Completed'),
(5  ,     5      , 12        , 'Completed'),
(6  ,     2      , 14        , 'Enrolled');


select t.* from t
left join
(select  student_id,course_id,count(*) from t where status not in('enrolled','completed') group by student_id,course_id) s
on t.STUDENT_ID = s.student_id and t.course_id = s.course_id 
where s.student_id is null;

+------+------------+-----------+-----------+
| ID   | STUDENT_ID | COURSE_ID | STATUS    |
+------+------------+-----------+-----------+
|    3 |          2 |        12 | Enrolled  |
|    4 |          2 |        12 | Completed |
|    6 |          2 |        14 | Enrolled  |
+------+------------+-----------+-----------+
3 rows in set (0.00 sec)