表中有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
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
gocreate table tb(A int, B varchar(14), C int);
go
insert 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.C
from (
select rn=ROW_NUMBER() over(partition by A order by A,C)
, *
from tb
) t
order by t.rn, t.A/*
A B C
----------- -------------- -----------
1 D 1
3 JK 2
7 U 4
1 C 2
3 XL 4
1 A 5
3 RL 6
*/
gocreate table tb(A int, B varchar(14), C int);
go
insert 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, C
from (
select rn = ( select count(1)
from tb b
where b.A = a.A and b.C < = a.C )
, a.*
from tb a
)t
order by rn, A
这个效率没有前面的高