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

方法一：

与第一题类似，但是需要构建一个新的同样的查询语句，使用having过滤，在过滤条件中取出最高的积分

select u.id, u.name, sum(g.grade_num) as grade_sum
from user u
join grade_info g
on u.id = g.user_id
group by u.id, u.name
having grade_sum = (select sum(grade_num) as grade_sum
										from grade_info g1
										group by user_id
										order by grade_sum desc
										limit 0, 1)
order by u.id

方法二：

使用 with ... as ... ，将一个查询当作一个表，语句更清晰，思路是和解决方案一是一样的

完整代码：

WITH t AS (
    SELECT  user_id,SUM(grade_num) AS grade_sum
    FROM grade_info
    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)
# 与第一题类似，但是需要构建一个新的同样的查询语句，使用having过滤，在过滤条件中取出最高的积分
# select u.id, u.name, sum(g.grade_num) as grade_sum
# from user u
# join grade_info g
# on u.id = g.user_id
# group by u.id, u.name
# having grade_sum = (select sum(grade_num) as grade_sum
# 										from grade_info g1
# 										group by user_id
# 										order by grade_sum desc
# 										limit 0, 1)
# order by u.id
										

# with t as 解决方案，将一个查询当作一个表，语句更清晰，思路是和解决方案一是一样的
WITH t AS (
    SELECT  user_id,SUM(grade_num) AS grade_sum
    FROM grade_info
    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)
ORDER BY id