MySQL 排除和要求

Question

我想将这个问题转化为一个查询。猜测应该是三个leftjoin和两个select

exercises
+-----+---------+
|  id | require | 
+-----+---------+
|  1  |         |
|  2  |    1    |
|  3  |    2    |
|  4  |    3    |
|  5  |         |
|  6  |    5    |
|  7  |         |
+-----+---------+

done
+-----+
|  id |
+-----+
|  1  |
|  2  |
+-----+

练习一和练习二做完了，现在可以做哪些练习？
解:3,5,7

我的逻辑过程，一步一步:
(全部) - SELECT(required - done)-(done)
(1,2,3,4,5,6,7) - 选择((1,2,3,5)-(1,2)) - (1,2)
(1,2,3,4,5,6,7) - 选择 (3,5) - (1,2)
(1,2,3,4,5,6,7) -(4,6) - (1,2)
(3,5,7)

如何写查询？

Answer 1

另一种没有subselect的解决方案

SELECT e.id FROM exercises e 
LEFT JOIN done d ON d.id = e.id 
LEFT JOIN done d_req ON d_req.id=e.require 
WHERE (d.id IS NULL AND d_req.id IS NOT NULL) 
         OR (e.require IS NULL AND d.id IS NULL)

可以简化WHERE(分配性)

WHERE d.id IS NULL AND (d_req.id IS NOT NULL OR e.require IS NULL )