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

Question

我需要选择玩家所有组合尝试的最高分，并且我需要使用 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));

记分卡的示例数据:

id       player_name
1        Bob      
2        Steve    
3        Joe    
4        Rob    

分数示例数据:

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

结果看起来像这样:

player_name
Bob
Rob

但只有当 Rob 的总得分低于 95 分时，才会是 Bob。我已经得到了他们使用这个在两列中获得的名称和总分:

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;

但是我如何才能获得最高分(或平分)的名字。

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

Answer 1

告诉您“使用 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;

普通子查询通常比 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.

您可以添加最后一个 ORDER BY c.player_name获得稳定的排序顺序，但这不是必需的。

查询的关键特性是您可以运行 window function like rank() 聚合函数的结果。相关:

• Postgres window function and group by exception
• Get the distinct sum of a joined table column