logo标签列表

从列表中获取前n个最高分数的SQL查询

sqlsql-serverpuzzle
7
我希望找到不同的方法来解决我遇到的现实问题：想象一下有一个竞赛或游戏，在此期间用户可以收集积分。您必须构建查询以显示具有最佳“n”得分的用户列表。
我举个例子来澄清。假设这是用户表，带有所获得的积分：
UserId - Points
1      - 100
2      -  75
3      -  50
4      -  50
5      -  50
6      -  25

如果我想要前三个分数，结果将是：

UserId - Points
1      - 100
2      -  75
3      -  50
4      -  50
5      -  50

这可以在视图或存储过程中实现，根据您的需求。我的目标数据库是Sql Server。实际上我已经解决了这个问题，但我认为还有其他更快更有效的方法来获得结果。
11个回答
11

未经测试，但应该可以工作：

select * from users where points in
(select distinct top 3 points from users order by points desc)
4

这是一个可行的方案 - 我不知道它是否更高效，而且它适用于SQL Server 2005+。

with scores as (
    select 1 userid, 100 points
    union select 2, 75
    union select 3, 50
    union select 4, 50
    union select 5, 50
    union select 6, 25
),
results as (
    select userid, points, RANK() over (order by points desc) as ranking 
    from scores
)
select userid, points, ranking
from results
where ranking <= 3

显然，第一个“with”是为了设置值，这样您就可以测试第二个“with”，最后选择工作 - 如果您正在针对现有表进行查询，您可以从“with results as ...”开始。

我有一个类似的问题，一直在尝试使用MAX函数，然后看到了你的答案，想起了DENSE_RANK函数。这节省了我很多时间。 - DataGirl
1

实际上，使用内连接对WHERE IN进行修改会更快。

SELECT 
   userid, points 
FROM users u
INNER JOIN 
(
   SELECT DISTINCT TOP N 
      points 
   FROM users 
   ORDER BY points DESC
) AS p ON p.points = u.points
1

这样怎么样：

select top 3 with ties points 
from scores
order by points desc

不确定“with ties”是否适用于除SQL Server之外的任何内容。

在SQL Server 2005及以上版本中，您可以将“top”数字作为int参数传递：

select top (@n) with ties points 
from scores
order by points desc
0

试试这个

select top N points from users order by points desc
0
@bosnic，我认为那样做不会按要求工作，我对MS SQL不是很熟悉，但我希望它只返回3行，并忽略3个用户并列第三的事实。
像这样的东西应该可以工作：
select userid, points 
   from scores 
   where points in (select top 3 points 
                       from scores 
                       order by points desc) 
   order by points desc
0

@Espo 感谢你的现实检查 - 添加了子查询来纠正这个问题。

我认为最简单的回应是：

select userid, points from users
where points in (select distinct top N points from users order by points desc)

如果您想将其放入以N为参数的存储过程中，那么您要么必须将SQL读入变量然后执行它，要么就要使用行计数技巧：
declare @SQL nvarchar(2000)
set @SQL = "select userID, points from users "
set @SQL = @SQL + " where points in (select distinct top " + @N
set @SQL = @SQL + " points from users order by points desc)"

execute @SQL

或者

SELECT  UserID, Points
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY points DESC)
         AS Row, UserID, Points FROM Users)
        AS usersWithPoints
WHERE  Row between 0 and @N

两个示例都假定使用 SQL Server，且尚未经过测试。

0

@Rob#37760：

select top N points from users order by points desc

如果N为3，则此查询仅选择3行，请参见问题。 "Top 3" 应返回5行。

0

嘿，我发现其他答案都有点长而且效率低下。我的答案是：

select * from users order by points desc limit 0,5

这将呈现前五个积分最高的用户。

0

@Matt Hamilton

您的答案在上面的示例中可以使用，但如果数据集是100、75、75、50、50（将仅返回3行），则无法使用。TOP WITH TIES仅包括最后一行返回的并列情况……

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接