問題描述
我想從表中選擇 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.
對于第二列，我希望每個值也有編號，但按第一列進行分區(qū).我已經(jīng)弄清楚了這一部分，但我無法使第一部分工作.
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
推薦答案
你只需要使用不同的排名函數(shù)，
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
這篇關(guān)于在 SQL Server 中分配行號，但按值分組的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！