按另一列"分组"返回一列中前三个最高值的SQL查询

Question

假设我有一个像这样的表格：

Player  Score
A       5
B       4
A       3
B       2
A       1
B       1
A       2
B       3
A       4
B       5

我需要一个SQL查询语句，按照每个玩家分组，以降序返回每个玩家的前三高分数。

Player  Score
A       5
A       4
A       3
B       5
B       4
B       3

非常感谢任何提示。

- user1350000

---

请您花些时间撰写问题吗？它应该至少包含您尝试过什么的清晰描述。 - Ben

你使用的是哪个关系型数据库管理系统和版本？ - Lamak

我目前正在使用OpenOffice.org Base 3.3，但最终将在mySQL 5上实现。 - user1350000

Answer 1

这是一种老式的（也就是基本的SQL）分组取前N个的方法。你可以根据分组条件（在这里是player）将表格与自身连接，并选择右侧得分更高的记录；如果有三条或更少的这样的记录，则该行是每个组中前N行之一。

select player.player, player.score
from Player
left join Player p2
on p2.player = player.player
  and p2.score > player.score
group by player.player, player.score
having count(distinct p2.score) < 3
order by 1, 2 desc

你可以检查另一种使用not exists的替代版本：

select player, score
from player
where not exists
(
  select p2.player
  from Player p2
  where p2.player = player.player
  and p2.score > player.score
  group by p2.player
  having count(distinct p2.score) > 3
)
order by 1, 2 desc

这两个版本在关系的展示上有所不同 - 第一个版本返回一行（由于分组），需要与原始表格连接以显示所有记录，而第二个版本直接从原始表格中显示所有数据和连接。

您可以在 Sql Fiddle演示中找到。

- Nikola Markovinović

Answer 2

在SQL Server中：

select p.player, p.score
from PS p
where p.score in (select top 3 score from PS 
                 where player = p.player order by score desc)
order by p.player asc, p.score desc

在MySQL中：

select p.player, p.score
    from PS p
    where p.score in (select score from PS 
                     where player = p.player order by score desc limit 3)
    order by p.player asc, p.score desc

- Jonny

Answer 3

我认为你要找的可以在这里找到：

http://www.sql-ex.ru/help/select16.php

基本上，最好的解决方案使用RANK函数。以下是该网站的示例代码：

SELECT maker, model, type FROM
(
SELECT maker, model, type, RANK() OVER(PARTITION BY type ORDER BY model) num
FROM Product
) X
WHERE num <= 3

您只需要修改Partition By部分，按照您的得分降序排序即可。 编辑： 根据您将使用MySQL的信息，您需要对上述查询进行一些修改（该查询适用于Microsoft SQL）。 您需要使用自己的RANK实现替换RANK函数。 这并不难。完整的说明可以在此处找到：

http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/

这将向您展示如何实现一个可以给出排名数字的计数器。

- IAmTimCorey

Answer 4

根据您使用的DBMS，您可能可以在某种形式上使用row_number。

在SQL Server 2008中，您可以使用。

create table #player
( Player char, Score int )

insert into #player (Player, Score) Values
('A',5),('B',4),('A',3),('B',2),('A',1),('B',1),('A',2),('B',3),('A',4),('B',5)

select * from #player

select Player, Score from 
(
  select *, ROW_NUMBER() over(partition by Player order by Score desc) as rowNo 
  from #player
) as tmp
where tmp.rowNo <= 3

drop table #player

- Albin Sunnanbo