题解 | #获得积分最多的人(三)#
获得积分最多的人(三)
http://www.nowcoder.com/practice/d2b7e2a305a7499fb310dc82a43820e8
select user.id,user.name,f.s_n from user,
(select user_id, sum(real_value) s_n from
(select user_id ,if(type='reduce',-grade_num,grade_num) real_value from grade_info) g
group by user_id) f where user.id=f.user_id and f.s_n=
(select sum(real_value) s_n from
(select user_id ,if(type='reduce',-grade_num,grade_num) real_value from grade_info) g
group by user_id order by s_n desc limit 1)
order by f.user_id
思路：
1.将type中reduce和add进行分类，并改变grade_num的值（add变为正，reduce变为负）
select user_id ,if(type='reduce',-grade_num,grade_num) real_value from grade_info) g
group by user_id
2.求出grade——info表中中各个user_id的sum
select user_id, sum(real_value) s_n from
(select user_id ,if(type='reduce',-grade_num,grade_num) real_value from grade_info) g
group by user_id
3.求出最大值，在第二部基础上对sum值进行降序排序，并取第一个值
select sum(real_value) s_n from
(select user_id ,if(type='reduce',-grade_num,grade_num) real_value from grade_info) g
group by user_id order by s_n desc limit 1
4.通过where条件将两个表合并，并求出sum值等于第三步求出的最大值即可
select user.id,user.name,f.s_n from user,
(select user_id, sum(real_value) s_n from
(select user_id ,if(type='reduce',-grade_num,grade_num) real_value from grade_info) g
group by user_id) f where user.id=f.user_id and f.s_n=
(select sum(real_value) s_n from
(select user_id ,if(type='reduce',-grade_num,grade_num) real_value from grade_info) g
group by user_id order by s_n desc limit 1)
order by f.user_id
