有什么好的方法,可以一条指令重新按A,B更新序号。如下:
declare @tb table(seq int ,a char(10) ,b int)
insert into @tb
select 1,'P100',110
union all select 8,'P100',140
union all select 3,'P100',500
union all select 4,'P100',130
union all select 3,'P200',150
union all select 5,'P200',340
union all select 6,'P200',880select * from @tb
/*
1 P100 110
8 P100 140
3 P100 500
4 P100 130
3 P200 150
5 P200 340
6 P200 880
*/
--用什么最佳方式可以重新生成序号? 想要的结果如下:
select * from @tb order by a,b
/*
1 P100 110
2 P100 130
3 P100 140
4 P100 500
1 P200 150
2 P200 340
3 P200 880
*/
delete @tb
declare @tb table(seq int ,a char(10) ,b int)
insert into @tb
select 1,'P100',110
union all select 8,'P100',140
union all select 3,'P100',500
union all select 4,'P100',130
union all select 3,'P200',150
union all select 5,'P200',340
union all select 6,'P200',880select * from @tb
/*
1 P100 110
8 P100 140
3 P100 500
4 P100 130
3 P200 150
5 P200 340
6 P200 880
*/
--用什么最佳方式可以重新生成序号? 想要的结果如下:
select * from @tb order by a,b
/*
1 P100 110
2 P100 130
3 P100 140
4 P100 500
1 P200 150
2 P200 340
3 P200 880
*/
delete @tb
insert into @tb
select 1,'P100',110
union all select 8,'P100',140
union all select 3,'P100',500
union all select 4,'P100',130
union all select 3,'P200',150
union all select 5,'P200',340
union all select 6,'P200',880update @tb
set seq=(select count(*) from @tb where a=t.a and b<=t.b)
from @tb tselect * from @tb order by a,seqseq a b
----------- ---------- -----------
1 P100 110
2 P100 130
3 P100 140
4 P100 500
1 P200 150
2 P200 340
3 P200 880(7 行受影响)
from @tb