题解 | #获得积分最多的人(三)#
获得积分最多的人(三)
http://www.nowcoder.com/practice/d2b7e2a305a7499fb310dc82a43820e8
sql思路
第一步找出最多积分的人
select sum(case when type='reduce' then -grade_num else grade_num end) as maxgrade from grade_info
group by user_id
order by maxgrade desc limit 1
第二步查询所有积分的人
select u.id,u.name,sum( case when f.type='reduce' then -f.grade_num else f.grade_num end) as maxs from user as u
join grade_info as f on u.id=f.user_id
group by u.id,u.name
第三步把第一步最多积分的人当中(HAVING)条件(子查询)
select u.id,u.name,sum( case when f.type='reduce' then -f.grade_num else f.grade_num end) as maxs from user as u
join grade_info as f on u.id=f.user_id
group by u.id,u.name
HAVING maxs=(select sum(case when type='reduce' then -grade_num else grade_num end) as maxgrade from grade_info
group by user_id
order by maxgrade desc limit 1)
途游游戏公司福利 68人发布
