# 两表之间的数据转换

aohan 2013-05-16 05:28:53
``````
/*规则是:
将T1表中的记录转到T2中，不使用游标是否有办法处理？
T1表中的第1记录填在T2中的startdate列上，第2条记录填写在enddate列上，
第3记录填在T2中的startdate列上，第4条记录填写在enddate列上
......
依次类推，如果是记录条数是奇数时，T2的Enddate列填写Null
*/

--1、测试表
create table T1(sdate datetime )

insert into T1 ( sdate )  VALUES  ( '2008-09-20 22:15:00'  )
insert into T1 ( sdate )  VALUES  ( '2008-09-20 22:16:00'  )
insert into T1 ( sdate )  VALUES  ( '2008-09-20 22:17:00'  )
insert into T1 ( sdate )  VALUES  ( '2008-09-20 23:03:00'  )
insert into T1 ( sdate )  VALUES  ( '2008-09-20 23:04:00'  )
insert into T1 ( sdate )  VALUES  ( '2008-09-20 23:05:00'  )
insert into T1 ( sdate )  VALUES  ( '2008-09-20 23:13:00'  )

create table T2 (startdate datetime ,enddate datetime )

--2、数据转换
/*这里进行@T1到@T2的转换

........

*/

--3、查询数据表T2
select * from T2

--希望转换后查询 select * from T2  的结果如下
/*
startdate                       enddate
2008-09-20 22:15:00             2008-09-20 22:16:00
2008-09-20 22:17:00             2008-09-20 23:03:00
2008-09-20 23:04:00             2008-09-20 23:05:00
2008-09-20 23:13:00             null

*/

--删除测试

drop table T1
drop table T2

``````
...全文
101 3 打赏 收藏 举报

3 条回复

chenjun1634 2013-05-16

• 打赏
• 举报

``````;with a
as
(
select *,ROW_NUMBER()over(order by sdate) as Row
from t1

)
select a.sdate as StartDate,b.sdate as EndDate
from a
left join a as b on a.Row=b.Row-1
where a.Row%2=1
/*
StartDate	EndDate
2008-09-20 22:15:00.000	2008-09-20 22:16:00.000
2008-09-20 22:17:00.000	2008-09-20 23:03:00.000
2008-09-20 23:04:00.000	2008-09-20 23:05:00.000
2008-09-20 23:13:00.000	NULL
*/``````
• 打赏
• 举报

daiyueqiang2045 2013-05-16
``````   with cte as
(
select *,ROW_NUMBER() over(order by T1.sdate) rn
from
T1
)
insert into T2
select t1.sdate startdate,t2.sdate enddate
from
cte t1 inner join cte t2 on t1.rn=t2.rn+1
where t1.rn%2=1

select * from T2``````
• 打赏
• 举报

3.3w+

MS-SQL Server相关内容讨论专区

2013-05-16 05:28