按最大总和选择，但结果中没有总和

I need to select the top score of all combined attempts by a player and I need to use a WITH clause.

我需要选择玩家所有组合尝试的最高分,我需要使用WITH子句。

create table scorecard(
 id integer primary key,
 player_name varchar(20));

create table scores(
 id integer references scorecard,
 attempt integer,
 score numeric
 primary key(id, attempt));

Sample Data for scorecard:

记分卡示例数据:

id       player_name
1        Bob      
2        Steve    
3        Joe    
4        Rob

Sample data for scores:

分数的样本数据:

id    attempt    score
1        1         50
1        2         45
2        1         10
2        2         20
3        1         40
3        2         35
4        1         0
4        2         95

The results would simply look like this:

结果将如下所示:

player_name
Bob
Rob

But would only be Bob if Rob had scored less than 95 total. I've gotten so far as to have the name and the total scores that they got in two columns using this:

但如果罗布的得分低于95,那只会是鲍勃。到目前为止,我已经得到了两个列中的名称和总分数:

select scorecard.player_name, sum(scores.score)
from scorecard
left join scores
on scorecard.id= scores.id
group by scorecard.name
order by sum(scores.score) desc;

But how do I just get the names of the highest score (or scores if tied).

但是我如何获得最高分的名称(或者如果并列则得分)。

And remember, it should be using a WITH clause.

请记住,它应该使用WITH子句。

3 个解决方案

#1

Who ever told you to "use a WITH clause" was missing a more efficient solution. To just get the (possibly multiple) winners:

曾经告诉过你“使用WITH子句”的人错过了一个更有效的解决方案。要获得(可能是多个)获胜者:

SELECT c.player_name
FROM   scorecard c
JOIN  (
   SELECT id, rank() OVER (ORDER BY sum(score) DESC) AS rnk
   FROM   scores
   GROUP  BY 1
   ) s USING (id)
WHERE  s.rnk = 1;

A plain subquery is typically faster than a CTE. If you must use a WITH clause:

普通子查询通常比CTE快。如果必须使用WITH子句:

WITH top_score AS (
   SELECT id, rank() OVER (ORDER BY sum(score) DESC) AS rnk
   FROM   scores
   GROUP  BY 1
   )
SELECT c.player_name
FROM   scorecard c
JOIN   top_score s USING (id)
WHERE  s.rnk = 1;

SQL Fiddle.

You could add a final ORDER BY c.player_name to get a stable sort order, but that's not requested.

您可以添加最终的ORDER BY c.player_name以获得稳定的排序顺序,但不会请求。

The key feature of the query is that you can run a window function like rank() over the result of an aggregate function. Related:

查询的关键特性是您可以在聚合函数的结果上运行类似rank()的窗口函数。有关:

Postgres window function and group by exception

Postgres窗口函数和异常分组

Get the distinct sum of a joined table column

获取连接表列的不同总和

#2

Can try something like follows.

可以尝试类似下面的内容。

With (SELECT id, sum(score) as sum_scores
      FROM scores
      group by id) as sumScoresTable,

With (SELECT max(score) as max_scores
      FROM scores
      group by id) as maxScoresTable

select player_name 
FROM scorecard
WHERE scorecard.id in (SELECT sumScoresTable.id 
                       from sumScoresTable 
                       where  sumScoresTable.score = (select maxScoresTable.score from maxScoresTable)

#3

Try this code:

试试这段代码:

   WITH CTE AS (
       SELECT ID, RANK() OVER(ORDER BY SumScore DESC) As R
       FROM (
          SELECT ID, SUM(score) AS SumScore
          FROM scores
          GROUP BY ID )
    )

   SELECT player_name
   FROM scorecard
   WHERE ID IN (SELECT ID FROM CTE WHERE R = 1)

#1