要求:用一条sql查出每个学生选了多少门课程,分别是什么
一、先建三张表:分别是学生表students、课程表classes、学生选课表stuclass
二、先分别查出每个学生选了多少门课程和每个学生分别选了哪些课程,sql语句
#每个学生选了多少门课程
SELECT
s.id,
s. NAME,
count(*) count
FROM
students AS s
LEFT JOIN stuclass AS sc ON s.id = sc.stu_id
GROUP BY
s.id
# 每个学生选了哪些课程
SELECT
stu_id,
GROUP_CONCAT(c.classname) class
FROM
stuclass AS sc
LEFT JOIN classes AS c ON c.id = sc.class_id
GROUP BY
sc.stu_id
然后只要把两条sql合并成一条就可以了
SELECT
*
FROM
(
SELECT
s.id,
s. NAME,
count(*) count
FROM
students AS s
LEFT JOIN stuclass AS sc ON s.id = sc.stu_id
GROUP BY
s.id
) a
LEFT JOIN (
SELECT
stu_id,
GROUP_CONCAT(c.classname) class
FROM
stuclass AS sc
LEFT JOIN classes AS c ON c.id = sc.class_id
GROUP BY
sc.stu_id
) b ON b.stu_id = a.id
查询结果如下: