至少有三种方法可以解决SQL中的问题。
- 设置操作(
SELECT
然后排除使用EXCEPT
)。
- 由于MySQL不支持
EXCEPT
,因此我们可以改用NOT IN
。
- 反加入(
LEFT OUTER JOIN
)。
- 加入并歧视
COUNT
。
方法1:SELECT
,然后使用EXCEPT
排除:
SELECT
student_id
FROM
takes
WHERE
course_id = 'CS-101'
EXCEPT
SELECT
student_id
FROM
takes
WHERE
course_id = 'CS-319'
然后将其用作对JOIN
的学生信息的内部查询:
SELECT
iq.student_id,student.name
FROM
(
SELECT
student_id
FROM
takes
WHERE
course_id = 'CS-101'
EXCEPT
SELECT
student_id
FROM
takes
WHERE
course_id = 'CS-319'
) AS iq
INNER JOIN students ON iq.student_id = students.student_id
用于MySQL的方法1(使用NOT IN
):
SELECT
iq.student_id,student.name
FROM
(
SELECT
student_id
FROM
takes
WHERE
course_id = 'CS-101'
AND
student_id NOT IN
(
SELECT
student_id
FROM
takes
WHERE
course_id = 'CS-319'
)
) AS iq
INNER JOIN students ON iq.student_id = students.student_id
方法2:反加入:
SELECT
s.student_id,s.name
FROM
students AS S
LEFT OUTER JOIN
(
SELECT
student_id,COUNT(*) AS Cs101Count
FROM
takes
WHERE
course_id = 'CS-101'
GROUP BY
student_id
) AS c1 ON s.student_id = c1.student_id
LEFT OUTER JOIN
(
SELECT
student_id,COUNT(*) AS Cs319Count
FROM
takes
WHERE
course_id = 'CS-319'
GROUP BY
student_id
) AS c2 ON s.student_id = c2.student_id
WHERE
c1.Cs101Count > 0
AND
c2.cS319Count = 0
方法3:在COUNT
上加入并加以区别:
SELECT
s.student_id,s.name
FROM
students AS S
INNER JOIN
(
SELECT
student_id,COUNT( CASE WHEN course_id = 'CS-101' END ) AS Cs101Count,COUNT( CASE WHEN course_id = 'CS-319' END ) AS Cs319Count
FROM
takes
GROUP BY
student_id
) AS c ON c.student_id = s.student_id
WHERE
c.Cs101Count > 0
AND
c.Cs319Count = 0
更多方法:
还有其他选择使用WHERE EXISTS
,WHERE NOT EXISTS
,依此类推。使用SQL时的问题(或挑战)之一是查询源数据以获得完全相同的结果的不同方法。玩得开心!
本文链接:https://www.f2er.com/3162360.html