在 SQL Server 中分配行号，但按值分组 [英] Assigning a Row Number in SQL Server, but grouped on a value

问题描述

我想从表中选择 2 列，并为每个值分配一个 int 值.但是，我希望第一列 ID 对于所有相同的值都相同.

I want to select 2 columns from a table, and assign a int value to each value. However, I want the 1st column ID to be the same for all values that are the same.

对于第二列，我希望每个值也有编号，但按第一列进行分区.我已经弄清楚了这一部分，但我无法使第一部分工作.

For the 2nd column, I want each value to numbered as well, but partitioned by the first column. I have figured this piece out, but I can't get the first part to work.

这是我正在使用的测试场景.

Here is the test scenario I'm using.

DECLARE @TestTable as Table (Column1 char(1), Column2 char(1))

INSERT INTO @TestTable SELECT 'A','A' 
INSERT INTO @TestTable SELECT 'A','B' 
INSERT INTO @TestTable SELECT 'A','C' 
INSERT INTO @TestTable SELECT 'B','D' 
INSERT INTO @TestTable SELECT 'B','E' 
INSERT INTO @TestTable SELECT 'B','F' 
INSERT INTO @TestTable SELECT 'B','G' 
INSERT INTO @TestTable SELECT 'B','H' 
INSERT INTO @TestTable SELECT 'C','A' 
INSERT INTO @TestTable SELECT 'C','B' 
INSERT INTO @TestTable SELECT 'C','C' 


SELECT 
    Row_Number() OVER (Partition BY Column1 ORDER BY Column1) as Column1_ID,
    Column1,
    Row_Number() OVER (Partition BY Column1 ORDER BY Column1, Column2) as Column2_ID,
    Column2
FROM @TestTable

当我运行它时，Column2_ID 中的值是正确的，但我希望 Column1_ID 的值如下所示.

When I run this, the values in Column2_ID are correct, but I would like the values for Column1_ID to be as follows.

Column1_ID  Column1  Column2_ID  Column2
1             A        1           A
1             A        2           B
1             A        3           C
2             B        1           D
2             B        2           E
2             B        3           F
2             B        4           G
2             B        5           H
3             C        1           A
3             C        2           B
3             C        3           C

推荐答案

你只需要使用不同的排名函数，

You just need to use a different ranking function,

dense_rank() OVER (ORDER BY Column1) as Column1_ID

http://msdn.microsoft.com/en-us/library/ms173825.aspx

SQL 小提琴:http://www.sqlfiddle.com/#!6/d41d8/1832

这篇关于在 SQL Server 中分配行号，但按值分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！