postgresql - 为每个ID选择最大值(包括一个ID可能有多个最大值!)

我有一张有身份证和分数的桌子。我想每个ID都有最大的分数。但是，我可能对一个ID有相同的得分，在这种情况下，我想要两个值。假设我有一张桌子

ID    score
1     10
1     10
2     8
2     6

所以我想

ID    score
1     10
1     10
2     8

它将是

 SELECT ID, max(score) FROM tbl GROUP BY ID, score ORDER BY ID

和

select * from tbl where score = (select max (score) from tbl)

我试过了

select * from tbl where score = (select max (score) from tbl GROUP BY ID)

但它当然说我在一个子查询中有多行。我想要多条线，我不想把它限制在1。
我试过了

 SELECT * FROM tbl AS tbl1
 JOIN
(select * from tbl where score = (select max (score) from tbl))
ON tbl1.ID=tbl.ID

但是它说“subquery in FROM must have an alias”我给了所有子查询别名，但仍然有这个错误。

最佳答案

一种方法是使用CTE：

WITH themaxes AS
(SELECT id, max(score) AS maxscore FROM tbl GROUP BY ID)
SELECT t.* FROM tbl t INNER JOIN themaxes m ON m.id = t.id AND m.maxscore = t.score;

另一种方法是使用window function（本例使用带有别名的子查询）：

SELECT id,score FROM
(SELECT rank() OVER (PARTITION BY id ORDER BY score DESC) AS therank, * FROM tbl) t
WHERE therank = 1

关于postgresql - 为每个ID选择最大值(包括一个ID可能有多个最大值!)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40344359/