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

WITH t1 AS (
	SELECT
    	user_id,
    	IF(type='add',grade_num,-grade_num) AS grade_num
    FROM grade_info
),
t2 AS (
	SELECT
    	user_id,
    	SUM(grade_num) AS grade_num
    FROM t1
    GROUP BY user_id
),
t3 AS (
	SELECT
    	user_id,
    	grade_num,
    	RANK() OVER(ORDER BY grade_num DESC) AS ranking
    FROM t2
),
t4 AS (SELECT 
       u.id,
       name,
	   grade_num
	FROM t3
	LEFT JOIN user u
	ON t3.user_id = u.id
	WHERE ranking = 1
)
SELECT * FROM t4;