有两张表
hackers(hacker_id, name)
submissions(submission_id, hacker_id, challenge_id, score)
要求输出
1、输出hacker在多次挑战中最大值的和(每次挑战可能提交了多次,只取最大值),和为零的不输出
2、和降序、hacker_id升序
思路:
先以hacker_id,challenge_id为分组,计算最大值,再以hacker_id为分组,计算总和,最后与hacker表作连接,输出名字。
以可以作些优化,以hacker_id,challenge_id为分组,与hacker表作连接,最后以hacker_id为分组,作总和
select t2.hacker_id, h.name, t2.sum_score
from
(
select t1.hacker_id as hacker_id, sum(t1.max_score) as sum_score
from
(
select hacker_id, challenge_id, max(score) as max_score from submissions group by hacker_id, challenge_id having max(score) > 0
) t1
group by t1.hacker_id
) t2 inner join hackers h on t2.hacker_id = h.hacker_id order by t2.sum_score desc, t2.hacker_id;