首页 / 知识

关于sql server：通过SQL查询从列表中获得最高的” n”分

2023-04-13 19:43:00

SQL query to get the top “n” scores out of a list

我想找到解决现实生活中问题的不同方法：想象一下在用户收集积分的竞赛或游戏中。您必须构建一个查询以显示得分最高的" n"用户列表。

我在举例说明。假设这是用户表，并获得了积分：

1
2
3
4
5
6
7

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

如果我想获得前三名，结果将是：

1
2
3
4
5
6

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

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

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

1 2	SELECT * FROM users WHERE points IN (SELECT DISTINCT top 3 points FROM users ORDER BY points DESC)

这是可行的-我不知道它是否更有效，它是SQL Server 2005

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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 ..."开始。

实际上，使用INNER JOIN对WHERE IN进行的修改会快得多。

1
2
3
4
5
6
7
8
9
10

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
2
3

SELECT top 3 WITH ties points
FROM scores
ORDER BY points DESC

不确定"具有关系"是否可以在其他SQL Server上使用。

在SQL Server 2005及更高版本上，您可以将" top"数字作为int参数传递：

1
2
3

SELECT top (@n) WITH ties points
FROM scores
ORDER BY points DESC

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

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

这将显示前5分

试试这个

1	SELECT top N points FROM users ORDER BY points DESC

坩埚知道了(假设使用SQL 2005)。

@马特·汉密尔顿

您的答案适用于上面的示例，但如果数据集为100、75、75、50、50(仅返回3行)，则该答案将无效。 TOP WITH TIES仅包含返回的最后一行的领带...

@ Rob#37760：

1	SELECT top N points FROM users ORDER BY points DESC

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

@Espo，感谢您的现实检查-添加了子选择来更正此问题。

我认为最简单的回应是：

1 2	SELECT userid, points FROM users WHERE points IN (SELECT DISTINCT top N points FROM users ORDER BY points DESC)

如果要将其放入以N为参数的存储过程中，则必须将SQL读入变量然后执行它，或者执行行计数技巧：

1
2
3
4
5
6

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

或

1
2
3
4
5
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，并且尚未经过测试。

@bosnic，我认为它不能按要求工作，我对MS SQL不太熟悉，但我希望它仅返回3行，而忽略3个用户并列第3位这一事实。

类似的东西应该起作用：

1
2
3
4
5
6
SELECT userid, points
FROM scores
WHERE points IN (SELECT top 3 points
FROM scores
ORDER BY points DESC)
ORDER BY points DESC