12.10sql每日一题

数据来源公众号 SQL数据库开发

image.png

WITH temp AS (
    SELECT
        A.*, B.classname,
        rank () over (
            PARTITION BY course
            ORDER BY
                score DESC
        ) rnk,
        c.course,
        c.score
    FROM
        t1210A A
    LEFT JOIN t1210B B ON A.classID = b.classID
    LEFT JOIN t1210C C ON A.stuID = C.stuID
    WHERE
        B.classname = "一班"
) SELECT
    course,
    stuName
FROM
    temp
WHERE
    rnk = 1

思路，将一班的成绩排序后取出成绩最高的，大概写繁杂了