SQL Server Query for Rank (RowNumber) and Grouping

问题:

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.

回答1:

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

回答2:

 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)

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