SQL左连接（MySQL）

Question

SQL左连接（MySQL）

3

我一直在使用以下查询:

我正在使用两个表:(还提到了其他一些表，但不需要为此问题)

assessment_criteria
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| scheme_of_work_id | mediumint(9) | NO   |     | NULL    |                |
| level             | char(255)    | YES  |     | NULL    |                |
| criteria          | char(255)    | NO   |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

criteria_completed
+------------------------+--------------+------+-----+---------+----------------+
| Field                  | Type         | Null | Key | Default | Extra          |
+------------------------+--------------+------+-----+---------+----------------+
| id                     | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| student_ID             | mediumint(9) | NO   |     | NULL    |                |
| assessment_criteria_id | mediumint(9) | NO   |     | NULL    |                |
| date_marked            | date         | NO   |     | NULL    |                |
| notes                  | varchar(255) | YES  |     | NULL    |                |
| attainment             | varchar(15)  | YES  |     | NULL    |                |
| effort                 | varchar(15)  | YES  |     | NULL    |                |
| marked_by              | varchar(20)  | NO   |     | NULL    |                |
+------------------------+--------------+------+-----+---------+----------------+

我使用了以下查询来显示学生尚未完成的评估标准列表：

SELECT DISTINCT assessment_criteria.id, assessment_criteria.level, assessment_criteria.criteria FROM assessment_criteria, criteria_completed  
WHERE (assessment_criteria.scheme_of_work_id = '17') 
AND (assessment_criteria.id NOT IN (SELECT criteria_completed.assessment_criteria_id FROM criteria_completed WHERE (student_ID = '403'))) 
ORDER BY level;

这个查询变得非常缓慢，我一直在尝试使用LEFT JOIN使其更快。

SELECT DISTINCT a.id, a.level, a.criteria 
FROM assessment_criteria a
LEFT JOIN criteria_completed b
ON a.id = b.assessment_criteria_id
WHERE b.assessment_criteria_id IS NULL

但是当我尝试为项目和学生添加子句时，我没有成功；即：

SELECT DISTINCT a.id, a.level, a.criteria 
FROM assessment_criteria a
LEFT JOIN criteria_completed b
ON a.id = b.assessment_criteria_id
WHERE b.assessment_criteria_id IS NULL
AND (b.student_ID = '403')
AND (a.scheme_of_work_id = '17');

MySQL 报告“空集”。我怀疑我引用了这些外键不正确？

- ICT Teacher

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- StuartLC · Accepted Answer

确认一下，您使用b.assessment_criteria_id IS NULL来检测失败的连接吗？

将在表b上应用过滤器到WHERE子句将过滤掉任何连接失败的记录，我认为这是问题的原因。

您可以尝试将b的过滤器移动到JOIN条件中：

SELECT DISTINCT a.id, a.level, a.criteria 
FROM assessment_criteria a
LEFT JOIN criteria_completed b
  ON a.id = b.assessment_criteria_id
  AND (b.student_ID = 403)
WHERE b.assessment_criteria_id IS NULL
  AND (a.scheme_of_work_id = 17);

尽管个人而言，我不喜欢在JOIN中进行这样的过滤。另一种选择是：

SELECT DISTINCT a.id, a.level, a.criteria 
FROM assessment_criteria a
LEFT JOIN criteria_completed b
  ON a.id = b.assessment_criteria_id
WHERE (a.scheme_of_work_id = 17)
   AND (b.assessment_criteria_id IS NULL OR b.student_ID = 403);