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

即使他们的分组方式一样。

最后要用的字符串字段都必须加在group by后面。

select user_id,name,sum(a.s)

from

                (select user_id,

                case when type like 'a%' then grade_num else 0-grade_num end s

                from grade_info) a

        left join user on user.id=a.user_id

group by user_id,name,id

having

        sum(a.s) = (select max(b.b)

        from

        (select sum(a.s) b

        from

        (select user_id,

        case when type like 'a%' then grade_num else 0-grade_num end s

        from grade_info) a

        group by user_id) b)

order by id