ID Starttime Endtime
1 2009-01-01 12:00 2009-01-01 14:00
2 2009-01-01 13:00 2009-01-01 15:00其中ID 1 & 2 在中午13:00-14:00时间重复,要怎么SELECT出来~?
1 2009-01-01 12:00 2009-01-01 14:00
2 2009-01-01 13:00 2009-01-01 15:00其中ID 1 & 2 在中午13:00-14:00时间重复,要怎么SELECT出来~?
-------------------------------------------> 测试时间:2009-07-29
--> 我的淘宝:http://shop36766744.taobao.com/--------------------------------------------------if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[Starttime] datetime,[Endtime] datetime)
insert [TB]
select 1,'2009-01-01 12:00','2009-01-01 14:00' union all
select 2,'2009-01-01 13:00','2009-01-01 15:00' union all
select 3,'2009-01-01 16:00','2009-01-01 21:00'
select * from TB t where datediff(hh,Starttime,(select top 1 Endtime from TB where T.ID>=ID ))>0/*
ID Starttime Endtime
----------- ------------------------------------------------------ ------------------------------------------------------
1 2009-01-01 12:00:00.000 2009-01-01 14:00:00.000
2 2009-01-01 13:00:00.000 2009-01-01 15:00:00.000(所影响的行数为 2 行)
*/drop table TB
DECLARE @T TABLE(ID int, Starttime DATETIME,Endtime DATETIME)INSERT INTO @T select
1 , '2009-01-01 12:00' , '2009-01-01 14:00'union all select
2 , '2009-01-01 13:00' , '2009-01-01 15:00'union all select
3 , '2009-01-01 15:20' , '2009-01-01 16:00'union all select
4 , '2009-01-01 17:00' , '2009-01-01 18:20'select a.id from @t a
where exists (select id from @t where a.id<>id and a.endtime between starttime and endtime
or endtime between a.starttime and a.endtime and a.id<>id)/*(所影响的行数为 4 行)id
-----------
1
2(所影响的行数为 2 行)*/
DECLARE @T TABLE(ID int, Starttime DATETIME,Endtime DATETIME)INSERT INTO @T select
1 , '2009-01-01 12:00' , '2009-01-01 14:00'union all select
2 , '2009-01-01 13:00' , '2009-01-01 15:00'union all select
3 , '2009-01-01 15:20' , '2009-01-01 16:00'union all select
4 , '2009-01-01 17:00' , '2009-01-01 18:20'union all select
5 , '2009-01-01 18:00' , '2009-01-01 18:40'select a.id,a.starttime,a.endtime from @t a
where exists (select id from @t where a.id<>id and a.endtime between starttime and endtime
or endtime between a.starttime and a.endtime and a.id<>id)/*(所影响的行数为 4 行)id starttime endtime
----------- ------------------------------------------------------ ------------------------------------------------------
1 2009-01-01 12:00:00.000 2009-01-01 14:00:00.000
2 2009-01-01 13:00:00.000 2009-01-01 15:00:00.000
4 2009-01-01 17:00:00.000 2009-01-01 18:20:00.000
5 2009-01-01 18:00:00.000 2009-01-01 18:40:00.000(所影响的行数为 4 行)*/
declare @tb table (id int,starttime datetime,endtime datetime)
insert into @tb select 1,'2009-01-01 12:00','2009-01-01 14:00'
union all select 2,'2009-01-01 13:00','2009-01-01 15:00'
select * from @tb where cast(convert(nvarchar(10),starttime ,108) as datetime) between '13:00' and '14:00'
or CAST( convert(nvarchar(10),endtime ,108) as datetime) between '13:00' and '14:00'(2 行受影响)
id starttime endtime
----------- ----------------------- -----------------------
1 2009-01-01 12:00:00.000 2009-01-01 14:00:00.000
2 2009-01-01 13:00:00.000 2009-01-01 15:00:00.000(2 行受影响)