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

with t1 as(
    SELECT user_id,
    sum(
        case WHEN type = 'add' then grade_info.grade_num
            else -grade_info.grade_num end
    ) grade_num
FROM grade_info
GROUP BY user_id
)-- 得到user_id、总的grade_num积分

SELECT t1.user_id,user.name,t1.grade_num
FROM t1
    JOIN user on t1.user_id = user.id
WHERE t1.grade_num = (
    SELECT max(grade_num)
    FROM t1
)
ORDER BY t1.user_id

1、先计算每个用户及其对应的总积分

    SELECT user_id,
    sum(
        case WHEN type = 'add' then grade_info.grade_num
            else -grade_info.grade_num end
    ) grade_num
FROM grade_info
GROUP BY user_id

2、找到最大的积分

    SELECT max(grade_num)
    FROM t1

3、用户积分等于最大积分，完整代码如下

with t1 as(
    SELECT user_id,
    sum(
        case WHEN type = 'add' then grade_info.grade_num
            else -grade_info.grade_num end
    ) grade_num
FROM grade_info
GROUP BY user_id
)-- 得到user_id、总的grade_num积分

SELECT t1.user_id,user.name,t1.grade_num
FROM t1
    JOIN user on t1.user_id = user.id
WHERE t1.grade_num = (
    SELECT max(grade_num)
    FROM t1
)
ORDER BY t1.user_id