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

获得积分最多的人(三)

http://www.nowcoder.com/practice/d2b7e2a305a7499fb310dc82a43820e8

#第一步：统计type为add的数据

SELECT user_id, SUM(grade_num) over(PARTITION BY user_id ) "add"
FROM grade_info
WHERE TYPE = "add"

#第二步:统计type为reduce的数据

SELECT user_id, SUM(grade_num) over(PARTITION BY user_id) "reduce"
FROM grade_info
WHERE TYPE = "reduce"

#第三步：两表合并

SELECT t1.user_id, t1.add, IFNULL(t2.reduce,0) "reduce"
FROM
    (SELECT user_id, SUM(grade_num) over(PARTITION BY user_id ) "add"
    FROM grade_info
    WHERE TYPE = "add") t1
    LEFT JOIN
    (SELECT user_id, SUM(grade_num) over(PARTITION BY user_id) "reduce"
    FROM grade_info
    WHERE TYPE = "reduce") t2    
    ON t1.user_id = t2.user_id

#第四步：汇总数据

SELECT user_id, `add` - `reduce`  "grade_sum"
FROM
(SELECT t1.user_id, t1.add, IFNULL(t2.reduce,0) "reduce"
FROM
    (SELECT user_id, SUM(grade_num) over(PARTITION BY user_id ) "add"
    FROM grade_info
    WHERE TYPE = "add") t1
    LEFT JOIN
    (SELECT user_id, SUM(grade_num) over(PARTITION BY user_id) "reduce"
    FROM grade_info
    WHERE TYPE = "reduce") t2    
    ON t1.user_id = t2.user_id) tt

#第五步：dense_rank排名，第一名无论有几个人都是第一

SELECT ttt.user_id, u.name, grade_sum,
    dense_rank() over(ORDER BY grade_sum DESC) "t_rank"
FROM 
    (SELECT user_id, `add` - `reduce`  "grade_sum"
    FROM
    (SELECT t1.user_id, t1.add, IFNULL(t2.reduce,0) "reduce"
    FROM
        (SELECT user_id, SUM(grade_num) over(PARTITION BY user_id ) "add"
        FROM grade_info
        WHERE TYPE = "add") t1
        LEFT JOIN
        (SELECT user_id, SUM(grade_num) over(PARTITION BY user_id) "reduce"
        FROM grade_info
        WHERE TYPE = "reduce") t2    
        ON t1.user_id = t2.user_id) tt) ttt LEFT JOIN `user` u
        ON ttt.user_id = u.id

#第六步：

SELECT  DISTINCT user_id "id", NAME, grade_sum
FROM 
    (SELECT ttt.user_id, u.name, grade_sum,
        dense_rank() over(ORDER BY grade_sum DESC) "t_rank"
    FROM 
        (SELECT user_id, `add` - `reduce`  "grade_sum"
        FROM
        (SELECT t1.user_id, t1.add, IFNULL(t2.reduce,0) "reduce"
        FROM
            (SELECT user_id, SUM(grade_num) over(PARTITION BY user_id ) "add"
            FROM grade_info
            WHERE TYPE = "add") t1
            LEFT JOIN
            (SELECT user_id, SUM(grade_num) over(PARTITION BY user_id) "reduce"
            FROM grade_info
            WHERE TYPE = "reduce") t2    
            ON t1.user_id = t2.user_id) tt) ttt LEFT JOIN `user` u
            ON ttt.user_id = u.id) tttt
WHERE t_rank = 1