SQL Server 分组排序

Input:

GroupID Key

3 a

3 b

3 d

4 b

4 a

5 a

5 e

5 c

5 d

5 b

Output:

GroupID Key SequenceNumber

3 a 1

3 b 2

3 d 3

4 a 1

4 b 2

5 a 1

5 b 2

5 c 3

5 d 4

5 e 5

 

-- 创建表
Create table T1(GroupId int, Keys varchar(2));

-- 准备数据
insert into T1 values(3, 'a');
insert into T1 values(3, 'b');
insert into T1 values(3, 'd');
insert into T1 values(4, 'a');
insert into T1 values(4, 'b');
insert into T1 values(5, 'a');
insert into T1 values(5, 'e');
insert into T1 values(5, 'c');
insert into T1 values(5, 'd');
insert into T1 values(5, 'b');
-- 分组查找
select *,  SequenceNumber = (select count(*) from T1 A where a.Keys <= B.Keys and A.GroupId = B.GroupId )
from T1 B order By GroupId, Keys, SequenceNumber;