针对Rank（RowNumber）和分组的SQL Server查询 [英] SQL Server Query for Rank (RowNumber) and Groupings

问题描述

我有一个表有一些列：用户，类别，值

我想做一个查询，它会给我一个所有用户的排名

例如：

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

查询将返回：

 排名用户分类
 1 user2类别A 
 2 user1 CategoryA 
 3 user3 CategoryA 
 1 user1 CategoryB 
 2 user4 CategoryB 
  

有什么想法？

我写查询并指定Category，它可以工作，但是我必须编写循环并且它非常慢。

解决方案

在排名功能 OVER子句

  SELECT 
 Rank（）over（Partition by类别按价值，用户，类别排序）作为等级，
类别，用户
 FROM 
表1 
分组由
用户，类别，值
排序
等级asc 
  

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.

解决方案

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

这篇关于针对Rank（RowNumber）和分组的SQL Server查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！