要求：用一条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

结果如下：（group_concat函数使用参考ysql将查询结果用逗号分隔开来）


现在数据都查出来了，只要把两条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

查询结果如下：