mysql - 选择集合中缺少子记录的 MYSQL 记录

Question

情况是这样的:如果有人选修了一门类(class)，他们就必须选修一系列其他类(class)作为补充。我如何识别学生尚未修读的类(class)？

这是我的表格:

tbl_course_dependency_lookup
courseid    dependentid
133          57
133          55
133          71
167          57
167          99

tbl_user_course_completed
userid      courseid
 12         133
 12          55
 13          71
 14         133
 15         100

以下是应返回的数据:

userid      courseid      dependentid
12          133           57
12          133           71
14          133           55
14          133           57
14          133           71

Answer 1

 DROP TABLE IF EXISTS course_dependency;

 CREATE TABLE course_dependency
 (course_id    INT NOT NULL
 ,dependent_id INT NOT NULL
 ,PRIMARY KEY(course_id,dependent_id)
 );

 INSERT INTO course_dependency VALUES
 (133          ,57),
 (133          ,55),
 (133          ,71),
 (167          ,57),
 (167          ,99);

 DROP TABLE IF EXISTS user_course;

 CREATE TABLE user_course
 (user_id INT NOT NULL
 ,course_id INT NOT NULL
 ,PRIMARY KEY(user_id,course_id)
 );

 INSERT INTO user_course VALUES
 (12         ,133),
 (12          ,55),
 (13          ,71),
 (14         ,133),
 (15         ,100);

 SELECT uc.* 
      , cd.dependent_id 
   FROM user_course uc 
   JOIN course_dependency cd 
     ON cd.course_id = uc.course_id 
   LEFT 
   JOIN user_course ucx 
     ON ucx.user_id = uc.user_id 
    AND ucx.course_id = cd.dependent_id 
  WHERE ucx.user_id IS NULL;

 +---------+-----------+--------------+
 | user_id | course_id | dependent_id |
 +---------+-----------+--------------+
 |      12 |       133 |           57 |
 |      12 |       133 |           71 |
 |      14 |       133 |           55 |
 |      14 |       133 |           57 |
 |      14 |       133 |           71 |
 +---------+-----------+--------------+