查询总分

问题描述

输入表:提交内容

提交的内容包含四列，Contestant_id表示针对不同问题提交的不同参赛者的ID.一个参赛者可以针对一个问题提交多次，因此，对一个Contestant_id的挑战ID可能会出现多次.

Submission contains four columns , Contestant_id indicates id of different contestants who submit against different problems.One contestant can submit against a problem more than one time.So,challenge_id may appear more than once against a Contestant_id.

 submission_id  Contestant_id challenge_id    score
    11                 1             333        90
    22                 2             333        60
    33                 3             333        80
    44                 4             333         0
    112                1             333        45
    113                1             444        80
    114                2             444        70

输出表:总分

Contestant_id     score 
    1              170
    2              130
    3               80 

在这里，我们将总分视为-

Here, we take total score as-

 for contestant_id 1 :  total score = max(90,45)+ 80 = 170
 for contestant_id 2 :  total score = 60 + 70 = 130
 for contestant_id 3 :  total score = 80 
 for contestant_id 4 :  total score = 0 ;so we exclude it 

要取得总分，我必须最多获得一个参赛者ID，如果有多个相同的Challenge_ID，则必须取一个总和.但是，我被困在该怎么做.

for taking total score, I have to take maximum of a single contestant_id,if there is more than one same challenge_id,than take a sum.But,I am stuck at it,how to do.

选择Contestant_id，得分为(选择总和(max(Select ....)

Select Contestant_id,Score as (Select sum (max(Select .... )

推荐答案

您似乎需要两个聚合级别.您需要每个参赛者和挑战的最高分数.然后，您要将它们加起来.

You seem to need two levels of aggregation. You need the maximum score for each contestant and challenge. Then you want to add these up.

这是一种方法:

select Contestant_id, sum(max_score)
from (select Contestant_id, challenge_id, max(score) as max_score
      from Submissions
      group by Contestant_id, challenge_id,
     ) t
group by Contestant_id;

如果要创建输出表，可以在 select 之后将添加到total_scores 中.

If you want to create an output table, you can add into total_scores after the select.

这篇关于查询总分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！