题解 | #实习广场投递简历分析(三)#

SELECT
t.id,t.name,t.num
FROM
    (
        SELECT
         a.id,a.`name`,a.num,DENSE_RANK() OVER(ORDER BY a.num DESC) number
         FROM
        (
        SELECT
    DISTINCT(g.user_id) id,u.`name`,SUM(g.grade_num) over(PARTITION BY g.user_id) AS num
    FROM
    (
        SELECT user_id,
            CASE type
            WHEN 'add' THEN
                grade_num
            ELSE
                -(grade_num)
            END grade_num,type
        FROM
        grade_info
    ) g,`user` u
    WHERE
    g.user_id = u.id
    ORDER BY
    num DESC
    )a
)t
WHERE
t.number = 1
ORDER BY
t.id