排名 (RowNumber) 和分组的 SQL Server 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1139719/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Query for Rank (RowNumber) and Groupings
提问by bladefist
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.
我想进行一个查询，该查询将按值给出所有用户的排名，但针对类别进行重置。
Example:
例子：
user1 CategoryA 10
user2 CategoryA 11
user3 CategoryA 9
user4 CategoryB 3
user1 CategoryB 11
the query would return:
查询将返回：
Rank User Category
1 user2 CategoryA
2 user1 CategoryA
3 user3 CategoryA
1 user1 CategoryB
2 user4 CategoryB
Any ideas?
有任何想法吗？
I write the query and specify the Category, It works but then I have to write loops and its very slow.
我编写了查询并指定了类别，它可以工作，但是我必须编写循环并且它非常慢。
回答by gbn
Use "Partition by" in the ranking function OVER clause
在排名函数OVER子句中使用“Partition by”
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
回答by Charles Bretana
Select User, Category,
(Select Count(*) From Table
Where Category = A.Category
And Value <= A.Value) Rank
From Table A
Order By Category, Value
If Value can have duplicates, then you must decide whether you want to 'count' the dupes (equivilent to RANK) or not (equivilent to DENSE_RANK, thanx @shannon)
如果 Value 可以有重复项，那么您必须决定是否要“计算”欺骗（相当于 RANK）（相当于 DENSE_RANK，thanx @shannon）
Ordinary Rank:
普通等级：
Select User, Category,
(Select 1 + Count(*) From Table -- "1 +" gives 1-based rank,
Where Category = A.Category -- take it out to get 0-based rank
And Value < A.Value) Rank
From Table A
Order By Category, Value
"Dense" Rank:
“密集”等级：
Select User, Category,
(Select 1 + Count(Distinct Value) -- "1 +" gives 1-based rank,
From Table -- take it out to get 0-based rank
Where Category = A.Category
And Value < A.Value) Rank
From Table A
Order By Category, Value
