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

-- 链接 LINK: https://www.nowcoder.com/practice/d2b7e2a305a7499fb310dc82a43820e8?tpId=82&rp=1&ru=%2Fexam%2Foj&qru=%2Fexam%2Foj&sourceUrl=%2Fexam%2Foj%3Fdifficulty%3D5%26page%3D1%26pageSize%3D50%26search%3D%26tab%3DSQL%25E7%25AF%2587%26topicId%3D82&difficulty=5&judgeStatus=&tags=&title=&gioEnter=menu
-- 标题 TITLE: SQL285 获得积分最多的人(三)
-- 日期 DATE: 2023-02-25

/* 设计思路

**/

-- 题解 SQL
SELECT T1.id, T1.name, T2.grade_num
FROM user T1
INNER JOIN (
    SELECT user_id, SUM(IF(type = 'add', grade_num, -1 * grade_num)) AS grade_num,
            MAX(SUM(IF(type = 'add', grade_num, -1 * grade_num))) OVER() AS grade_num_max
    FROM grade_info
    GROUP BY user_id
) T2 ON T2.grade_num = T2.grade_num_max AND T1.id = T2.user_id
ORDER BY T1.id
;

-- 数据 DATA ===================================================
drop table if exists user;
drop table if exists grade_info;

CREATE TABLE user (
id  int(4) NOT NULL,
name varchar(32) NOT NULL
);

CREATE TABLE grade_info (
user_id  int(4) NOT NULL,
grade_num int(4) NOT NULL,
type varchar(32) NOT NULL
);

INSERT INTO user VALUES
(1,'tm'),
(2,'wwy'),
(3,'zk'),
(4,'qq'),
(5,'lm');

INSERT INTO grade_info VALUES
(1,3,'add'),
(2,3,'add'),
(1,1,'reduce'),
(3,3,'add'),
(4,3,'add'),
(5,3,'add'),
(3,1,'reduce');