首页 > 试题广场 >

获得积分最多的人(三)

[编程题]获得积分最多的人(三)

热度指数：61870 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

牛客每天有很多用户刷题，发帖，点赞，点踩等等，这些都会记录相应的积分。

有一个用户表(user)，简况如下：

id	name
1	tm
2	wwy
3	zk
4	qq
5	lm

还有一个积分表(grade_info)，简况如下:

user_id	grade_num	type
1	3	add
2	3	add
1	1	reduce
3	3	add
4	3	add
5	3	add
3	1	reduce

第1行表示，user_id为1的用户积分增加了3分。

第2行表示，user_id为2的用户积分增加了3分。

第3行表示，user_id为1的用户积分减少了1分。

.......

最后1行表示，user_id为3的用户积分减少了1分。

请你写一个SQL查找积分最高的用户的id，名字，以及他的总积分是多少(可能有多个)，查询结果按照id升序排序，以上例子查询结果如下:

id	name	grade_num
2	wwy	3
4	qq	3
5	lm	3

解释:

user_id为1和3的先加了3分，但是后面又减了1分，他们2个是2分，

其他3个都是3分，所以输出其他三个的数据。

示例1

输入

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');

输出

2|wwy|3
4|qq|3
5|lm|3

算法知识视频讲解

Mysql

一个好懒的人

with temp as
(
select 
user_id,
name,
sum(if(type='add',grade_num,(-1)*grade_num)) as grade_num
from grade_info gi
join user u on gi.user_id=u.id
group by user_id,name
)

select
user_id as id,
name,
grade_num
from
(select *,
rank() over(order by grade_num desc) as rk
from temp) t1
where rk = 1
order by id

相对于上题，只改动了这里

sum(if(type='add',grade_num,(-1)*grade_num)) as grade_num

发表于 2022-06-30 15:36:52 回复(0)

Mysql

想吃烤肉的四郎在做ppt

select uid,name,grade from(
    select uid,u.name as name ,grade,rank() over(order by grade desc) as t_rank from(
        select 
            user_id as uid,
            sum(if(type='add',grade_num,-1*grade_num)) as grade
        from 
            grade_info
        group by user_id    
    )  as t left join user as u on t.uid=u.id
) t2 where t2.t_rank=1

发表于 2021-10-21 10:53:59 回复(0)

Mysql

牛客106248132号

select t0.user_id,t3.name,t0.lastnum
from
(select t1.user_id,sum(t1.num) lastnum
from (select user_id,if(type='add',grade_num,0-grade_num) num from grade_info ) t1
group by user_id
) t0 --获得每个用户及其最终得分
join
(select sum(t1.num) lastnum
from (select user_id,if(type='add',grade_num,0-grade_num) num from grade_info ) t1
group by user_id
order by lastnum desc limit 1) t2 --获得最高得分
on t0.lastnum=t2.lastnum
join user t3 on t3.id=t0.user_id

和上一题的思路是一样的，只是这里多加了一列数据，将type为add的num标为正数，将type为reduce的num标为负数。然后再分组相加获得最终的分数。

发表于 2021-10-12 15:55:49 回复(0)

Mysql

晓_

另类解法，两次select，第一次查出总分和对应的排名，第二次筛选总分第一的，关联一下拿出用户名，搞定

SELECT
    u.id,u.name,tmp.grade
FROM
(
select
    user_id,sum(case when type='add' then grade_num else -1*grade_num end) as grade,dense_rank() over(order by sum(case when type='add' then grade_num else -1*grade_num end) desc) as r
from
    grade_info
group BY
    user_id
) tmp
left JOIN
    user u
ON
    tmp.user_id=u.id
WHERE
    tmp.r=1
order BY
    u.id

发表于 2021-07-14 19:00:23 回复(0)

夏朗

思路：

1. 计算每个user_id的最大得分：sum(if())

2. 用where找到整体的最大得分：where grade_sum in (select max())

3. 连接user表，获得user_id的名称

代码：

with t as (
    select user_id,sum(if(type='add',grade_num,-1*grade_num)) grade_sum
    from grade_info
    group by user_id)
select user_id,u.name,grade_sum
from t,user u
where t.grade_sum in (select max(grade_sum) from t)
and u.id=t.user_id
order by user_id

编辑于 2021-06-30 10:35:43 回复(0)

牛客386311360号

select a.id,a.name,b.grade_sum
from user as a join (
select user_id,grade_sum,rank() over(order by grade_sum desc) as flag
from (
select user_id,
/*针对积分增减需特殊处理下*/
sum(case when type="reduce" then grade_num*(-1) else grade_num end) as grade_sum
from grade_info
group by user_id)/* 计算每个用户增加的积分*/
) as b/*将用户的积分排名*/
on a.id=b.user_id and b.flag=1/*匹积分最高的用户信息*/
order by a.id;

发表于 2021-06-20 21:53:03 回复(0)

eggman

查找积分增加最高的用户

没留意到作者的解释，

做的时候在思考：是算单次增加最高的积分，还是累计增加的积分的最高那个用户，然后再看该用户实际积分，结果写了好几个表。。

看别人代码的时候才发现，原来求的是实际积分里面最高积分的人。。。。。。

特此记录，希望不要再有人走弯路了🙃

with 
t1 as (select * from user a join grade_info b on a.id=b.user_id), 
t2 as (select id,name,sum(if(type='add',grade_num,-grade_num)) as real_num from t1 group by id),      # 实际最高
t3 as (select id,sum(if(type='add',grade_num,0)) as add_num from t1 group by id),           # 加分最高
t4 as (select *,dense_rank() over(order by real_num desc) as rk from t2)               # 排名

select t4.id,t4.name,t4.real_num from t4 join t2 where t4.rk=1 and t4.id = t2.id order by t4.id

编辑于 2021-04-12 11:10:39 回复(0)

牛客421553514号

select id, name, grade_sum
from(select user_id, grade_sum, rank() over(order by grade_sum desc) ranking
from(select user_id, sum(if(type = 'add', grade_num, -grade_num)) grade_sum
from grade_info
group by user_id) n1) n2 
join user
on n2.user_id = user.id
where ranking = 1
order by id

利用if或case when end创建新列后不需要创建新表即可用group by

编辑于 2021-04-12 09:02:11 回复(0)

Mysql

wspjh

sum聚合函数里加以判断是增是减即可，having里的子查询使用ALL关键字

select b.id,b.name,sum(ifnull(case when type='add' then grade_num else -grade_num end,0)) as grade_sum from grade_info a right join user b on a.user_id=b.id
group by b.id,b.name
having grade_sum>=
ALL(select sum(case when type='add' then grade_num else -grade_num end) from grade_info group by user_id)

发表于 2022-01-26 15:11:47 回复(0)

Mysql

在冲浪的小飞象很犯困

with a as (
select u.id, u.name, sum(if(g.type ='add', g.grade_num, (- g.grade_num))) as num from grade_info g left join user u on u.id = g.user_id
group by u.id, u.name
)
select * from a where a.num = (SELECT max(num) from a )

发表于 2021-08-22 22:10:42 回复(0)

BWTNjIrMFoan

最开始是求出用户所有新增的积分减去所有扣除的积分求得总数，写的很长。借鉴讨论区答案，使用case as 优化后更加易读明确

## 改进前
with T as (
    select A.user_id, (A.add_num - ifnull(A2.reduce_num, 0) ) grade_sum
    from (
        select user_id, sum(grade_num) add_num
        from grade_info
        where type = 'add'
        group by user_id
    ) A
    left join (
        select user_id, sum(grade_num) reduce_num
        from grade_info
        where type = 'reduce'
        group by user_id
    ) A2 on A2.user_id = A.user_id
)
select id, name, grade_sum
from T
join user T2 on T2.id = T.user_id
where grade_sum = (select max(grade_sum) from T)
order by id

## 使用case as 优化后
with T as (
    select user_id, sum(case when type = 'add' then grade_num else -grade_num end) grade_sum
    from grade_info
    group by user_id
)
select id, name, grade_sum
from T
join user T2 on T2.id = T.user_id
where grade_sum = (select max(grade_sum) from T)
order by id

发表于 2021-04-11 12:59:38 回复(0)

牛客80957172号

1、创建t1表，取出user_id，和通过if添加正负号的分数grade_num。

2、分组求和得到user_id及对应的总分数grade_sum，并通过WITH……AS……打包成临时表t，方便后续多次引用

3、WHERE筛选出grade_sum=最大值的行，连接并取出题目所需列

WITH t AS (
    SELECT  user_id,SUM(grade_num) AS grade_sum
    FROM (SELECT user_id,grade_num * IF(type= 'add',1,-1) AS grade_num
          FROM grade_info )AS t1
    GROUP BY user_id )
SELECT id,name,grade_sum
FROM t
JOIN user u ON u.id = t.user_id
WHERE grade_sum  = (SELECT MAX(grade_sum) FROM t)

发表于 2021-03-30 23:12:30 回复(9)

Mysql

牛客986720969号

SELECT a.user_id, b.name, a.cnt as grade_sum FROM (
    SELECT 
        user_id, 
        sum(IF(type = "add", grade_num, - grade_num)) cnt,
        rank() over(order by sum(IF(type = "add", grade_num, - grade_num)) desc) num
    FROM grade_info
    GROUP BY user_id
) as a
INNER JOIN user as b ON a.user_id = b.id and a.num = 1
order BY a.user_id

和上题基本一样，只不过计算积分有加有减，其他语句不变，只需要将sum里的内容改一下即可。

加一个判断，如果type是add，则加分，否则减分

发表于 2021-09-16 17:34:31 回复(0)

欢乐马201910261605520

窗口函数排序
(case...when)或者(if...)条件分支判断求和
通用表达式更简洁

with g as
    (select id, g_sum,
    rank()over(order by g_sum desc)rk
     from
         (select user_id id,
          sum(case type when 'reduce' then -1*grade_num
                  else grade_num end)g_sum
          from grade_info
          group by 1
         )t1
     )
select u.id, name, g_sum
from `user` u
join g
on u.id = g.id
where g.rk = 1
order by 1 ;

发表于 2021-04-14 11:48:28 回复(0)

Mysql

Qiuxj

SELECT u.id, u.name, g.grade_sum
FROM user u,(
    select user_id, sum(if(type='add',grade_num,-grade_num)) grade_sum,
    dense_rank() over (order by sum(if(type='add',grade_num,-grade_num)) desc) rk
    from grade_info
    group by user_id) g
WHERE u.id=g.user_id and g.rk=1
ORDER BY u.id;

发表于 2021-10-17 23:10:24 回复(0)

Mysql

Stzolo

# 思路如果type不是add 就取反。以下两种写法

# 第一种方法
# 采用子查询先将grade_num 取反，最后求和
select a.user_id, b.name, sl
from (
    select user_id, sum(grade_num) as sl, max(sum(grade_num)) over() as t_sl
    from (select user_id, case when type='add' then grade_num else -grade_num end as grade_num from grade_info) a
    group by user_id) a
join user b on a.user_id = b.id
where sl = t_sl
order by a.user_id

# 第二种方法
# 直接利用IF在求和过程中判断，不是ADD的直接取反
select a.user_id, b.name, sl
from (
    select user_id, sum(if(type='add',grade_num, -grade_num)) as sl, max(sum(if(type='add',grade_num, -grade_num))) over() as t_sl
    from  grade_info
    group by user_id) a 
join user b on a.user_id = b.id
where sl = t_sl
order by a.user_id

发表于 2022-04-21 14:34:20 回复(0)

MenziKit

加上开窗就会简单明了多了：

select t1.id,t1.name,t.grade
from(
select user_id,grade,rank()over(order by grade desc) as rn
from(
select user_id,sum(if(type='add',grade_num,-1*grade_num)) as grade
from grade_info
group by user_id
) gra
) t
join user as t1
on t.user_id = t1.id
where rn=1

发表于 2021-06-16 17:07:25 回复(0)

小黄人呀小黄人

跟上一道题差不多

with t as

(select id,name,sum(case when type='add'then grade_num else -grade_num end)as point
from grade_info as g,user as u
where u.id=g.user_id
group by id)

select id,name,point
from t
where point=(select max(point) from t)
order by id asc;

发表于 2021-04-06 09:32:40 回复(0)

好奇的打工鸭在泡澡

select id,name,grade_num 
from
(select id,name,sum(if(type='add',grade_num,(-1)*grade_num)) grade_num,
dense_rank()over(order by sum(if(type='add',grade_num,(-1)*grade_num)) desc) rk
from user u
join
grade_info g
on u.id=g.user_id
group by id,name
)t
where rk=1
order by id;

编辑于 2024-04-12 10:35:51 回复(0)

不要香菜的丹尼尔很想去深圳

1. 先计算积分，将2种情况add/reduce都写进case when 而不是用else代替reduce情况时有原因的，在日常应用中往往会存在其它情况，必须预料此种情况。

2. 中国人习惯用dense_rank函数，重复积分不会影响排名顺序跳过，如题目要求取值积分第2名的用户，则直接把c.rank_no值改为2即可。

select b.id,b.name,c.grade_num
from user b 
left join (
    select a.*,dense_rank() over (order by a.grade_num desc) 'rank_no'
    from (
        select user_id,
            sum(case when type = "add" then grade_num when type = "reduce" then -1 * grade_num end)'grade_num'
        from grade_info
        group by user_id
    )a
)c on b.id = c.user_id 
where c.rank_no = 1
order by b.id

发表于 2022-11-16 16:12:06 回复(0)