sql在使用order by时分配类别id

I'm new to SQL.

我新的SQL。

When I order by SomeData on my table I get:

当我根据表上的数据点餐时，我得到:

ID      SomeData
6       ABC
3       ABC
12      FG
1       FH
2       GI
4       JU
8       K3
5       K3
11      P7

great. but what i really want on output is

太好了。但是我真正想要的是输出

ID      Category
6       1
3       1
12      2
1       3
2       4
4       5
8       6
5       6
11      7

That is every time SomeData changes on the sort I want to increment Category by one

这是每次我想要增加一个类别的数据变化的时候。

I can't see how to do this. Any help would be greatly appreciated. Thanks.

我不知道怎么做。如有任何帮助，我们将不胜感激。谢谢。

2 个解决方案

#1

If you are on SQL-Server, you can use the DENSE_RANK() ranking function in combination with OVER:

如果您在SQL-Server上，您可以结合使用DENSE_RANK()排序函数:

SELECT ID
     , DENSE_RANK() OVER (ORDER BY SomeData)
       AS Category
FROM myTable
ORDER BY SomeData

See: SQL-Server: Ranking Functions

看到:sql server:排名的功能

#2

SELECT ROW_NUMBER() OVER(ORDER BY SomeData ASC) AS Category, otherfield1..