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

解题思路

1. 通过CASE [字段] WHEN [条件] THEN [返回]语句,将grade_num转换为正负数,方便后面分组计算每个用户的总分(在做这一步操作之前我是用了一个子查询将user_id,SUM(grade_num) AS grade_num,type 这样可以减少数据的CASE操作)
2. 根据第一步完成的正负数转换得到X数据集(g.user_id,grade_num(这个字段已经被转换为正负数)),根据user_id分组,然后SUM(grade_num)计算结果
3. 最后一步与user进行连表(第二步得到的Y数据集),在连表的操作有个AND条件做筛选,这里就是获取最大分数进行筛选(而我这里重复了第二步骤的代码,这里不知道怎么不拷贝第二步的代码直接计算出最大分数,大佬给点意见)

完整SQL

SELECT u.id,u.`name`,g3.grade_sum FROM user u 
INNER JOIN (
SELECT g2.user_id,SUM(g2.grade_num) AS grade_sum FROM (
SELECT g.user_id,
     (CASE g.type 
    WHEN 'reduce' THEN
        -ABS(grade_num)
    WHEN 'add' THEN
        grade_num END) AS grade_num FROM (SELECT user_id,SUM(grade_num) AS grade_num,type FROM grade_info GROUP BY user_id,type) g
) g2 GROUP BY g2.user_id ) g3 ON g3.user_id = u.id AND g3.grade_sum = (SELECT MAX(m.grade_sum) FROM (
SELECT g2.user_id,SUM(g2.grade_num) AS grade_sum FROM (
SELECT g.user_id,
     (CASE g.type 
    WHEN 'reduce' THEN
        -ABS(grade_num)
    WHEN 'add' THEN
        grade_num END) AS grade_num FROM (SELECT user_id,SUM(grade_num) AS grade_num,type FROM grade_info GROUP BY user_id,type) g
) g2 GROUP BY g2.user_id ) AS m)
ORDER BY u.id

SQL优化

在看了讨论区发现可以WITH关键字来创建一张虚拟表,这样可以把步骤三的重复SQL给复用,这样减少网络传输

-- 创建 t 表
WITH t AS (
SELECT g2.user_id,SUM(g2.grade_num) AS grade_sum FROM (
SELECT g.user_id,
     (CASE g.type 
    WHEN 'reduce' THEN
        -ABS(grade_num)
    WHEN 'add' THEN
        grade_num END) AS grade_num FROM (SELECT user_id,SUM(grade_num) AS grade_num,type FROM grade_info GROUP BY user_id,type) g
) g2 GROUP BY g2.user_id 
)
-- 连表查询筛选数据
SELECT u.id,u.`name`,t.grade_sum FROM user u 
INNER JOIN t ON t.user_id = u.id AND t.grade_sum = (SELECT MAX(grade_sum) FROM t)
ORDER BY u.id

最后写点总结

本题的难点就是type的转换,正常情况下设计表结构的时候是不会考虑将type字段作为正负数表示,通常是直接将正负数积分直接写入到数据库中.所以本题的核心问题在于type字段怎么转换,让其方便参与计算