tb1
col1 col2
a1 b1
a2 b2
a3 b3...转换成下列格式tb2col1 col2
a1 b1
b1 b2
b2 b3
...
就是把col2的本行值作为co1的下一行的值。难点有两个,第一 在对tb1整理的时候第一行的数据实际也要取上一行的b0
,但是这个值是不存在的,如何在判断上一个值不存在的情况下给出a1,第二就是正常的如何让col2的值依次成为col1的值了
col1 col2
a1 b1
a2 b2
a3 b3...转换成下列格式tb2col1 col2
a1 b1
b1 b2
b2 b3
...
就是把col2的本行值作为co1的下一行的值。难点有两个,第一 在对tb1整理的时候第一行的数据实际也要取上一行的b0
,但是这个值是不存在的,如何在判断上一个值不存在的情况下给出a1,第二就是正常的如何让col2的值依次成为col1的值了
create table tb1 (col1 char(02),col2 char(02))
insert into tb1 select 'a1','b1'
insert into tb1 select 'a2','b2'
insert into tb1 select 'a3','b3'GO
select isnull(B.col2,A.col1) as col1,A.col2 from
(select col1,col2,row_number() over (order by getdate()) as tmp
from tb1) A
left join
(select col1,col2,row_number() over (order by getdate()) as tmp
from tb1) B
on A.tmp=B.tmp+1
create table tb1 (col1 char(02),col2 char(02))
insert into tb1 select 'a1','b1'
insert into tb1 select 'a2','b2'
insert into tb1 select 'a3','b3'GO
select isnull(B.col2,A.col1) as col1,A.col2 from
(select col1,col2,row_number() over (order by getdate()) as tmp
from tb1) A
left join
(select col1,col2,row_number() over (order by getdate()) as tmp
from tb1) B
on A.tmp=B.tmp+1
create table tb1 (col1 char(02),col2 char(02))
insert into tb1 select 'a1','b1'
insert into tb1 select 'a2','b2'
insert into tb1 select 'a3','b3'
go
select identity(int,1,1) as id ,*into # from tb1 update #
set col1= case when id =1 then col1
else (select col2 from # where ID=t.ID-1) end
from # t
select * from #id col1 col2
----------- ---- ----
1 a1 b1
2 b1 b2
3 b2 b3
create table tb1 (col1 char(02),col2 char(02))
insert into tb1 select 'a1','b1'
insert into tb1 select 'a2','b2'
insert into tb1 select 'a3','b3' select isnull(t2.col2,t1.col1) as col1,t1.col2 from (
select *,id=(select count(*) from tb1 where col1<t.col1)+1 from tb1 t) t1 left join (
select *,id=(select count(*) from tb1 where col1<t.col1)+1 from tb1 t) t2 on t1.id=t2.id+1
/*
col1 col2
---- ----
a1 b1
b1 b2
b2 b3(3 行受影响)
*/
drop table tb1
create table tb1 (col1 char(02),col2 char(02))
insert into tb1 select 'a1','b1'
insert into tb1 select 'a2','b2'
insert into tb1 select 'a3','b3'
go
select identity(int,1,1) as id ,*into # from tb1 update #
set col1= case when id =1 then col1
else (select col2 from # where ID=t.ID-1) end
from # t
select col1 ,col2
from #