*查找积分最高的用户的id,名字,以及他的总积分是多少(可能有多个)
*查询结果按照id升序排序
*思路:查找最高可以用max
整体思路 # select a.user_id, u.name, MAX(a.总分) grade_sum # from 总分表 a join user u on a.user_id = u.id # group by a.user_id # order by a.user_id
总分表(来源于grade_info)
with grade_score as (select G.user_id,sum(G.grade) grade_sum from (select user_id, grade_num*if(type = 'add', 1, -1) grade from grade_info) G group by G.user_id) # 此处注意可以直接用if为每列添加标签成为新的列然后和存在的列进行运算
合体:
select a.user_id, u.name, a.grade_sum grade_sum from grade_score a join user u on a.user_id = u.id where a.grade_sum = (select max(grade_sum) from grade_score) order by a.user_id