mysql - 从给定标识符的多个条目中检索行，仅从一组值中检索一个列值

Question

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

ID | STUDENT_ID | COURSE_ID | STATUS1  |     5      | 12        | Enrolled2  |     5      | 12        | In-Progress3  |     2      | 12        | Enrolled4  |     2      | 12        | Completed5  |     5      | 12        | Completed6  |     2      | 14        | Enrolled

我需要找到给定 STUDENT_ID 和 COURSE_ID 对的所有记录作为标识符，其中 STATUS 属于已注册和已完成(即每个已注册和已完成状态有 2 条记录，或者已注册或已完成状态只有一条记录).

注意 - 对于给定的 STUDENT_ID 和 COURSE_ID，不应存在 STATUS 不是 Enrolled & Completed 的条目。

输出表-

ID | STUDENT_ID | COURSE_ID | STATUS3  |     2      | 12        | Enrolled4  |     2      | 12        | Completed6  |     2      | 14        | Enrolled

更新 -如果我有另一个状态为 In-Progress 的 STUDENT_ID 2 条目，它仍应向我返回状态为已注册和已完成的类(class)。

ID | STUDENT_ID | COURSE_ID | STATUS1  |     5      | 12        | Enrolled2  |     5      | 12        | In-Progress3  |     2      | 12        | Enrolled4  |     2      | 12        | Completed5  |     5      | 12        | Completed6  |     2      | 14        | Enrolled7  |     2      | 14        | In-Progress

输出表-

ID | STUDENT_ID | COURSE_ID | STATUS3  |     2      | 12        | Enrolled4  |     2      | 12        | Completed

Answer 1

使用带空测试的左连接

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)