题解 | 获得积分最多的人(三)

SELECT b3.id AS user_id, b3.name, b3.grade_num
FROM (
    SELECT b1.id, b1.name, 
    (b1.totalgrade_add-b2.totalgrade_reduce) AS grade_num,
    DENSE_RANK() OVER (ORDER BY (b1.totalgrade_add-b2.totalgrade_reduce) DESC) AS ranking
    FROM (
        # b1: 筛选需要add的分数
        SELECT U.id, U.name, SUM(G.grade_num) AS totalgrade_add
        FROM grade_info G
        JOIN user U ON G.user_id=U.id
        WHERE G.type='add'
        GROUP BY U.id,U.name) AS b1
    JOIN (
        # b2: 筛选需要reduce的分数
        SELECT B.id, B.name, SUM(B.grade_reduce) AS totalgrade_reduce
        FROM (
            SELECT U.id, U.name, 
            CASE WHEN G.type='reduce' THEN G.grade_num
            ELSE 0 END AS grade_reduce
            FROM grade_info G
            JOIN user U ON G.user_id=U.id) AS B
        GROUP BY B.id, B.name
        ) AS b2
    ON b1.name=b2.name
    ) AS b3
WHERE b3.ranking=1
ORDER BY b3.id