参考:https://www.cnblogs.com/deng-cc/p/6515166.html
题目来自链接中文章,修正了其中不规范、错误的地方,并按照MySQL的风格重新命令了表和字段,并进行了在MySQL5.7中进行了测试。
这里有三张表:
student(student_id,student_name)
teacher(teacher_id,teacher_name)
course(course_id,course_name,teacher_id)
sc(student_id,course_id,score)
SELECT
a.student_id
FROM
sc a
INNER JOIN sc b ON a.student_id = b.student_id
WHERE
a.score < b.score
AND a.course_id = 1
AND b.course_id =2
SELECT
student_id,
avg( score )
FROM
sc
GROUP BY
student_id
HAVING
avg( score ) > 60
SELECT
student.student_id,
student.student_name,
count( sc.course_id ),
sum( sc.score )
FROM
student
LEFT JOIN sc ON student.student_id = sc.student_id
GROUP BY
sc.student_id
J
开头的教师的个数SELECT
count( teacher_id )
FROM
teacher
WHERE
teacher_name LIKE 'J%'
注意:LIKE后面用的单引号。
Jack
老师课的学生的学号和姓名SELECT
student.student_id,
student.student_name
FROM
student
WHERE
student.student_id NOT IN (
SELECT
student.student_id
FROM
student
LEFT JOIN sc ON student.student_id = sc.student_id
INNER JOIN course ON sc.course_id = course.course_id
INNER JOIN teacher ON course.teacher_id = teacher.teacher_id
WHERE
teacher.teacher_name = 'Jack'
)
SELECT
a.student_id,
a.student_name
FROM
(
SELECT
student.student_id,
student.student_name
FROM
student
INNER JOIN sc ON student.student_id = sc.student_id
WHERE
sc.course_id = 1 UNION ALL
SELECT
student.student_id,
student.student_name
FROM
student
INNER JOIN sc ON student.student_id = sc.student_id
WHERE
sc.course_id = 2
) a
GROUP BY
a.student_id
HAVING
count( a.student_id ) =2
注明:这道题展示了如何求两个查询结果的交集,我们知道并集使用UNION
和UNION ALL
连接两个查询结果,但是MySQL中并没有求两个查询结果的交集和差集的操作,实现思路就稍微绕弯了:
UNION ALL
得到不去重并集,然后对并集中一个ID出现的次数进行判断,出现次数等于并集操作集合数,这个元素就在所求的交集中Jack
老师所教的所有课的学生的学号和姓名SELECT
student.student_id,
student.student_name
FROM
student
INNER JOIN sc ON student.student_id = sc.student_id
INNER JOIN course ON sc.course_id = course.course_id
INNER JOIN teacher ON course.teacher_id = teacher.teacher_id
WHERE
teacher.teacher_name = 'Jack'
GROUP BY
student.student_id
HAVING
count( student.student_id ) = (
SELECT
count( * )
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.teacher_id
WHERE
teacher.teacher_name = 'Jack'
GROUP BY
teacher.teacher_id
)
SELECT
student.student_id,
student.student_name
FROM
student
INNER JOIN sc ON student.student_id = sc.student_id
WHERE
sc.score < 60
SELECT
student.student_id,
student.student_name
FROM
student
LEFT JOIN sc ON student.student_id = sc.student_id
GROUP BY
student.student_id
HAVING
count( student.student_id ) < ( SELECT count( * ) FROM course )
SELECT DISTINCT
student.student_id,
student.student_name
FROM
student
INNER JOIN sc ON student.student_id = sc.student_id
WHERE
sc.course_id IN ( SELECT sc.course_id FROM sc WHERE sc.student_id = 1 )
SELECT DISTINCT
student.student_id,
student.student_name
FROM
student
INNER JOIN sc ON student.student_id = sc.student_id
GROUP BY
sc.student_id
HAVING
count( sc.student_id ) = ( SELECT count( * ) FROM sc WHERE student_id = 1 )