测试数据:
StartTime EndTime
2011-04-06 08:00:00.000 2011-04-06 08:05:00.000
2011-04-06 09:00:00.000 2011-04-06 09:15:00.000
2011-04-06 10:00:00.000 2011-04-06 10:25:00.000
2011-04-06 11:00:00.000 2011-04-06 11:35:00.000想要的结果:
StartTime EndTime
2011-04-06 08:05:00.000 2011-04-06 09:00:00.000
2011-04-06 09:15:00.000 2011-04-06 10:00:00.000
2011-04-06 10:25:00.000 2011-04-06 11:00:00.000
StartTime EndTime
2011-04-06 08:00:00.000 2011-04-06 08:05:00.000
2011-04-06 09:00:00.000 2011-04-06 09:15:00.000
2011-04-06 10:00:00.000 2011-04-06 10:25:00.000
2011-04-06 11:00:00.000 2011-04-06 11:35:00.000想要的结果:
StartTime EndTime
2011-04-06 08:05:00.000 2011-04-06 09:00:00.000
2011-04-06 09:15:00.000 2011-04-06 10:00:00.000
2011-04-06 10:25:00.000 2011-04-06 11:00:00.000
想要:上一条的EndTime 和下一条的StartTime
想要:一段时间内的,上一条的EndTime 和下一条的StartTime
想要:一段时间内的,上一条的EndTime 和下一条的StartTime
(
id int,
startdate datetime,
enddate datetime
)insert into tb(id,startdate,enddate)
values(1,'2011-04-06 08:00:00.000','2011-04-06 08:05:00.000')
insert into tb(id,startdate,enddate)
values(2,'2011-04-06 09:00:00.000','2011-04-06 09:15:00.000')
insert into tb(id,startdate,enddate)
values(3,'2011-04-06 10:00:00.000 ','2011-04-06 10:25:00.000')
insert into tb(id,startdate,enddate)
values(4,'2011-04-06 11:00:00.000 ','2011-04-06 11:35:00.000')select a.enddate,b.startdate from tb a,tb b where a.id=b.id-1
ID可以用自增长列
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-04-20 10:42:49
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([StartTime] datetime,[EndTime] datetime)
insert [tb]
select '2011-04-06 08:00:00.000','2011-04-06 08:05:00.000' union all
select '2011-04-06 09:00:00.000','2011-04-06 09:15:00.000' union all
select '2011-04-06 10:00:00.000','2011-04-06 10:25:00.000' union all
select '2011-04-06 11:00:00.000','2011-04-06 11:35:00.000'
--------------开始查询--------------------------
select
a.EndTime as StartTime, b.StartTime as EndTime
from
(select id=row_number()over(order by getdate()),* from tb) a,
(select id=row_number()over(order by getdate()),* from tb) b
where
a.id=b.id-1
----------------结果----------------------------
/* StartTime EndTime
----------------------- -----------------------
2011-04-06 08:05:00.000 2011-04-06 09:00:00.000
2011-04-06 09:15:00.000 2011-04-06 10:00:00.000
2011-04-06 10:25:00.000 2011-04-06 11:00:00.000(3 行受影响)*/
insert [tb]
select '2011-04-06 08:00:00.000','2011-04-06 08:05:00.000' union all
select '2011-04-06 09:00:00.000','2011-04-06 09:15:00.000' union all
select '2011-04-06 10:00:00.000','2011-04-06 10:25:00.000' union all
select '2011-04-06 11:00:00.000','2011-04-06 11:35:00.000'select t1.EndTime StartTime ,
EndTime = (select top 1 StartTime from tb t2 where t2.StartTime > t1.EndTime order by t2.StartTime)
from tb t1
where t1.EndTime <> (select max(EndTime) from tb)
drop table tb/*
StartTime EndTime
------------------------------------------------------ ------------------------------------------------------
2011-04-06 08:05:00.000 2011-04-06 09:00:00.000
2011-04-06 09:15:00.000 2011-04-06 10:00:00.000
2011-04-06 10:25:00.000 2011-04-06 11:00:00.000(所影响的行数为 3 行)
*/
UNION
SELECT StartTime='2011-04-06 09:00:00.000',EndTime='2011-04-06 09:15:00.000'
UNION
SELECT StartTime='2011-04-06 10:00:00.000',EndTime='2011-04-06 10:25:00.000'
UNION
SELECT StartTime='2011-04-06 11:00:00.000',EndTime='2011-04-06 11:35:00.000'
declare @Count int
select @Count=Count(1)-1 from Test_Table1
select a.EndTime,b.StartTime
from
(
select top (@Count) row_number() over(order by EndTime) as RowNum,EndTime from Test_Table1
) a,
(
select row_number() over(order by StartTime) as RowNum,StartTime from Test_Table1 where StartTime<>(select min(StartTime) from Test_Table1)
) b
where a.RowNum=b.RowNum
(startdate datetime,
enddate datetime
)insert into tb1(startdate,enddate)
values('2011-04-06 08:00:00.000','2011-04-06 08:05:00.000')
insert into tb1(startdate,enddate)
values('2011-04-06 09:00:00.000','2011-04-06 09:15:00.000')
insert into tb1(startdate,enddate)
values('2011-04-06 10:00:00.000 ','2011-04-06 10:25:00.000')
insert into tb1(startdate,enddate)
values('2011-04-06 11:00:00.000 ','2011-04-06 11:35:00.000')select c.enddate,d.startdate from
(select *,序号=(select count(1) from tb1 a where a.startdate>b.startdate) from tb1 b) c,(select *,序号=(select count(1)+1 from tb1 a where a.enddate>b.enddate) from tb1 b) d
where c.序号=d.序号/*
enddate startdate
------------------------------------------------------ ------------------------------------------------------
2011-04-06 08:05:00.000 2011-04-06 09:00:00.000
2011-04-06 09:15:00.000 2011-04-06 10:00:00.000
2011-04-06 10:25:00.000 2011-04-06 11:00:00.000(所影响的行数为 3 行)
*/