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

发布于 2024-07-27 00:13:13 字数 465 浏览 2 评论 0原文

我有一个表，其中包含一些列：用户、类别、值

我想要进行一个查询，该查询将按值对所有用户进行排名，但会重置类别。

示例：

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

Any ideas?

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

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

转瞬即逝 2024-08-03 00:13:13

在排名函数中使用“分区依据”OVER 子句

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

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

回复收藏 0 原文

梦冥 2024-08-03 00:13:13

 Select User, Category,
     (Select Count(*) From Table 
      Where Category = A.Category 
         And Value <= A.Value) Rank
 From Table A
 Order By Category, Value

如果 Value 可以有重复项，那么您必须决定是否要“计算”重复项（相当于 RANK）（相当于 DENSE_RANK，thanx @shannon）

普通排名：

 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

“密集”排名：

 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

 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

回复收藏 0 原文

~没有更多了~