表中有A、B、C三个字段数据如下
A B C
1 'A' 5
1 'D' 1
1 'C' 2
3 'XL' 4
3 'RL' 6
3 'JK' 2
7 'U' 4
C字段用来表示按A字段分组B字段的排序
现在要完成以下查询
以A分组,每组中C字段最小的排在最前面，同时查询结果以A排序，第二小的依次往下
结果应为
1 'D' 1
3 'JK' 2
7 'U' 4
1 'C' 2
3 'XL' 4
1 'A' 5
3 'RL' 6
------解决方案--------------------
- SQL code
if OBJECT_ID('tb') is not null Drop table tb;gocreate table tb(A int, B varchar(14), C int);goinsert into tb(A, B, C)select 1, 'A', 5 union all select 1, 'D', 1 union all select 1, 'C', 2 union all select 3, 'XL', 4 union all select 3, 'RL', 6 union all select 3, 'JK', 2 union all select 7, 'U', 4;select t.A,t.B,t.Cfrom ( select rn=ROW_NUMBER() over(partition by A order by A,C) , * from tb ) torder by t.rn, t.A/*A B C----------- -------------- -----------1 D 13 JK 27 U 41 C 23 XL 41 A 53 RL 6*/
------解决方案--------------------
- SQL code
if OBJECT_ID('tb') is not null Drop table tb;gocreate table tb(A int, B varchar(14), C int);goinsert into tb(A, B, C)select 1, 'A', 5 union all select 1, 'D', 1 union all select 1, 'C', 2 union all select 3, 'XL', 4 union all select 3, 'RL', 6 union all select 3, 'JK', 2 union all select 7, 'U', 4;select A, B, Cfrom ( select rn = ( select count(1) from tb b where b.A = a.A and b.C < = a.C ) , a.* from tb a )torder by rn, A