排名 (RowNumber) 和分组的 SQL Server 查询

2021-12-27 00:00:00 sql group-by rank tsql sql-server

我有一个包含一些列的表:用户、类别、值

I have a table that has some columns: User, Category, Value

并且我想进行一个查询，该查询将按值为我提供所有用户的排名，但针对类别进行重置.

And I want to make a query that will give me a ranking, of all the users by the value, but reset for the category.

示例:

user1   CategoryA 10
user2   CategoryA 11
user3   CategoryA 9
user4   CategoryB 3
user1   CategoryB 11

查询将返回:

Rank  User   Category  
1     user2   CategoryA
2     user1   CategoryA
3     user3   CategoryA
1     user1   CategoryB
2     user4   CategoryB

有什么想法吗?

我编写了查询并指定了类别，它可以工作，但随后我必须编写循环并且它非常慢.

I write the query and specify the Category, It works but then I have to write loops and its very slow.

推荐答案

在排名函数中使用Partition by"OVER 子句

Use "Partition by" in the ranking function OVER clause

SELECT
    Rank() over (Partition by Category Order by Value, User, Category) as ranks,
    Category, User
FROM 
    Table1
Group By
    User, Category, Value 
Order by
    ranks asc

相关文章