交集MySql

Question

我正在尝试获取几个SQL请求的交集。我尝试了很多命令，但还是卡住了。

以下是3个请求。

有谁有办法帮我吗？

1

SELECT username
FROM mdl_user
INNER JOIN mdl_quiz_grades
ON mdl_user.id=mdl_quiz_grades.userid
WHERE mdl_quiz_grades.quiz = 17;

2

SELECT username
FROM mdl_user
INNER JOIN mdl_quiz_grades
ON mdl_user.id=mdl_quiz_grades.userid
WHERE mdl_quiz_grades.quiz = 16;

3.

SELECT username
FROM mdl_user
INNER JOIN mdl_quiz_grades
ON mdl_user.id=mdl_quiz_grades.userid
WHERE mdl_quiz_grades.quiz = 15;

Answer 1

可以使用Joins轻松重写INTERSECT：

SELECT t1.username
from 
 (
   SELECT username
   FROM mdl_user
   INNER JOIN mdl_quiz_grades
   ON mdl_user.id=mdl_quiz_grades.userid
   WHERE mdl_quiz_grades.quiz = 17
 ) as t1
join
 (
   SELECT username
   FROM mdl_user
   INNER JOIN mdl_quiz_grades
   ON mdl_user.id=mdl_quiz_grades.userid
   WHERE mdl_quiz_grades.quiz = 16
 ) as t
on t1.username = t2.username
join
 (
   SELECT username
   FROM mdl_user
   INNER JOIN mdl_quiz_grades
   ON mdl_user.id=mdl_quiz_grades.userid
   WHERE mdl_quiz_grades.quiz = 15
 ) as t3
on t1.username = t3.username

但是在您的例子中，它是几乎相同的查询，只是不同的WHERE条件。您可以应用聚合逻辑：

SELECT username
FROM mdl_user
INNER JOIN mdl_quiz_grades
ON mdl_user.id=mdl_quiz_grades.userid
WHERE mdl_quiz_grades.quiz in (15,16,17)
GROUP BY username
HAVING COUNT(*) = 3

如果相同的用户If可能多次使用相同的测验，则需要应用DISTINCT：

HAVING COUNT(DISTINCT quiz) = 3