mysql - 相交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

INTERSECT 可以使用 Joins 轻松重写:

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

如果同一个用户id可能有多次相同的测验你需要应用DISTINCT:

HAVING COUNT(DISTINCT quiz) = 3