求SQL语句，疑难！ 数据更新
如下：
表t1
s r t
1 1 a
1 2 b
1 3 c
1 4 d
1 5 e
1 6 f
在这个基础上还想问个问题，（还是那个表）先插入一条，两条……
保持r列数据的连续性
插入(在另一个表中t2)
s r t
1 2 g
1 2 l
1 4 h
结果：
s r t
1 1 a
1 2 g
1 3 l
1 4 b
1 5 c
1 6 h
1 7 d
1 8 e
1 9 f
请教！谢谢！
[解决办法]
Create Table t1
(sInt,
rInt,
tVarchar(10))
Insert t1 Select 1, 1, 'a '
Union All Select 1, 2, 'b '
Union All Select 1, 3, 'c '
Union All Select 1, 4, 'd '
Union All Select 1, 5, 'e '
Union All Select 1, 6, 'f '
Create Table t2
(sInt,
rInt,
tVarchar(10))
Insert t2 Select 1, 2, 'g '
Union All Select 1, 2, 'l '
Union All Select 1, 4, 'h '
GO
Select *, 1 As Flag Into #T1 From T1
Insert #T1 Select *, 2 From T2
Select ID = Identity(Int, 1, 1), * Into #T2 From #T1 Order By r, Flag Desc
Delete From T2
Insert T2 Select s, ID, t From #T2
Select * From T2
Drop Table #T1, #T2
GO
Drop Table T1, T2
/*
srt
11a
12g
13l
14b
15c
16h
17d
18e
19f
*/
[解决办法]
--下面是将数据放入T2表中的代码.
if object_id( 'pubs..t1 ') is not null
drop table t1
go
create table t1(s varchar(10),r int,t varchar(10))
insert into t1(s,r,t) values( '1 ', 1, 'a ')
insert into t1(s,r,t) values( '1 ', 2, 'b ')
insert into t1(s,r,t) values( '1 ', 3, 'c ')
insert into t1(s,r,t) values( '1 ', 4, 'd ')
insert into t1(s,r,t) values( '1 ', 5, 'e ')
insert into t1(s,r,t) values( '1 ', 6, 'f ')
insert into t1(s,r,t) values( '2 ', 1, 'x ')
insert into t1(s,r,t) values( '2 ', 2, 'y ')
insert into t1(s,r,t) values( '2 ', 3, 'z ')
go
if object_id( 'pubs..t2 ') is not null
drop table t2
go
create table t2(s varchar(10),r int,t varchar(10))
insert into t2(s,r,t) values( '1 ', 2, 'g ')
insert into t2(s,r,t) values( '1 ', 2, 'l ')
insert into t2(s,r,t) values( '1 ', 4, 'h ')
insert into t2(s,r,t) values( '2 ', 2, 'k ')
go
select px = identity(int,1,1) , s , r , t into test from
(
select * , id = 2 from t1
union all
select * , id = 1 from t2
) t
order by s , r , id
delete from t2
insert t2(s,r,t)
select s , r = (select count(1) from test where s = n.s and px < n.px)+1 , t from test n order by s , r
select * from t2
drop table t1,t2 , test
/*
s r t
---------- ----------- ----------
1 1 a
1 2 g
1 3 l
1 4 b
1 5 c
1 6 h
1 7 d
1 8 e
1 9 f
2 1 x
2 2 k
2 3 y
2 4 z
（所影响的行数为 13 行）
*/
