问题描述
我想从表中选择 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