代码之家 › 专栏 › 技术社区 › ila

SQL查询以从列表中获取最高分“n”

puzzle sql-server sql

ila · 技术社区 · 16 年前

我想找到不同的方法来解决我在现实生活中遇到的问题:想象一场比赛,或者一场游戏,在比赛中,用户可以收集分数。您必须构建一个查询,以显示具有最佳“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 回复 | 直到 9 年前

Espo 16 年前

未经测试,但应有效:

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

crucible 16 年前

这里有一个有效的方法——我不知道它是否更有效,它是SQLServer2005+

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 results as…”开始。

Matt Hamilton 16 年前

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

不确定“带领带”是否适用于SQL Server上的其他任何东西。

在SQL Server 2005及更高版本上,可以将“top”数字作为int参数传递:

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

Chris Lampley Chris Lampley 16 年前

实际上,对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

Tom Gruff 16 年前

像这样的方法应该会奏效:

select userid, points 
   from scores 
   where points in (select top 3 points 
                       from scores 
                       order by points desc) 
   order by points desc

Espo 16 年前

@罗布#37760:

select top N points from users order by points desc

如果N为3,此查询将仅选择3行,请参见问题。“Top3”应返回5行。

Nick 16 年前

@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,但尚未进行测试。

AviD 16 年前

@马特·汉密尔顿

NakedBrunch 16 年前

Crucible得到了它(假设SQL2005是一个选项)。

Krishna Gupta 11 年前

嘿,我发现所有其他答案都有点长而且效率低我的答案是:

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

Sabyasachi Mishra 9 年前

试试这个

select top N points from users order by points desc