求一SQL语句 疑难
如下结构：
s r
1 1
1 2
1 3
1 4
1 5
1 6
s,r主键
删除1，2 和 1，4行数据，并且保持r连续
结果应为：
s r t
1 1 a
1 2 c
1 3 e
1 4 f
最好批处理
[解决办法]
create table a
(
s int,
r int
)
insert into a
select 1,1 union
select 1,2 union
select 1,3 union
select 1,4 union
select 1,5 union
select 1,6
delete from a where (s=1 and r=2) or (s=1 and r=4)
update a
set a.r= c.cnt
from a inner join
(
select b.s,b.r,cnt=(select count(1) from a where a.s=b.s and a.r <=b.r)
from a b
) c on a.s=c.s and a.r=c.r
select * from a
--结果
s r
----------- -----------
1 1
1 2
1 3
1 4
(4 行受影响)
