求一个SQL语句 个人想了好久都没解决

Question

有一张表  3个字段 人员ID(ID)  进入项目的时间（beginDate）  离开项目的时间（endDate)我想查出所有人空闲的时间段  员ID(ID)  空闲的开始时间（beginDate）  空闲的结束时间（endDate)举个例子  如果一个人在数据库的数据为  2011-6-1 进入项目A  2011-8-1离开项目A   2011-9-1进入项目B 2011-10-1 离开项目B那么应该查出该人的数据为    null到2011-5-31   2011-8-2到 2011-8-31  2011-10-1到NULL  这3段数据。

Answer 1

select a.id,a.enddate as begindate,b.begindate as endDate from tb a inner join tb b on a.id=b.id and a.enddate<b.begindate and not exists(
select 1 from tb where id=a.id and begindate<a.enddate and enddate>b.begindate)

Answer 2

select
 a.id,a.enddate as begindate,b.begindate as endDate 
from
 tb a , tb b 
where
 a.id=b.id 
and
 a.enddate<b.begindate 
and
 not exists(select 1 from tb where id=a.id and begindate<a.enddate and enddate>b.begindate)

Answer 3

这个比较像了，但是好像还没合并完全   我贴下具体的数据
BegindatePlan         EnddatePlan
2005-12-28 23:46:23.000 2006-12-30 00:00:00.000
2005-12-28 23:50:16.000 2006-12-30 00:00:00.000
2005-12-28 23:52:33.000 2006-12-30 00:00:00.000
2005-12-28 23:56:45.000 2006-12-30 00:00:00.000
2005-12-28 23:58:43.000 2006-12-30 00:00:00.000
2005-12-29 00:00:49.000 2006-12-30 00:00:00.000
2005-12-29 00:03:47.000 2006-12-30 00:00:00.000
2005-12-29 00:05:10.000 2006-12-30 00:00:00.000
2005-12-29 00:07:51.000 2006-12-30 00:00:00.000
2005-12-29 00:09:43.000 2006-12-30 00:00:00.000
2005-12-29 00:11:20.000 2006-12-30 00:00:00.000
2005-12-29 00:14:28.000 2006-12-30 00:00:00.000
2005-12-29 11:49:03.000 2006-12-30 00:00:00.000
2005-12-29 18:28:34.000 2006-05-31 00:00:00.000
2006-01-09 23:45:02.000 2006-12-31 00:00:00.000
2006-01-16 18:34:29.000 2006-12-30 00:00:00.000
2006-01-16 18:46:24.000 2006-12-30 00:00:00.000
2006-02-12 16:10:34.000 2006-12-30 00:00:00.000
2006-02-12 16:12:23.000 2006-12-30 00:00:00.000
2006-02-28 17:42:47.000 2006-12-30 00:00:00.000
2006-04-12 17:00:05.000 2006-12-30 00:00:00.000
2006-05-08 08:50:29.000 2006-12-30 00:00:00.000
2009-10-01 00:00:00.000 2009-10-31 00:00:00.000查出结果应该为
2006-12-31 00:00:00.000 2009-10-01 00:00:00.000

Answer 4

有一张表 3个字段 人员ID(ID) 进入项目的时间（beginDate） 离开项目的时间（endDate)我想查出所有人空闲的时间段 员ID(ID) 空闲的开始时间（beginDate） 空闲的结束时间（endDate)举个例子 如果一个人在数据库的数据为 2011-6-1 进入项目A 2011-8-1离开项目A 2011-9-1进入项目B 2011-10-1 离开项目B那么应该查出该人的数据为 null到2011-5-31 2011-8-2到 2011-8-31 2011-10-1到NULL 这3段数据。
create table test(id int ,begintime datetime ,endtime datetime)
go
insert into test
select 1,'2011-06-01','2011-08-01' union all
select 1,'2011-09-01','2011-10-01'select top 1 case when a.begintime=min(a.begintime) then 'NULL-'+convert(varchar(10),a.begintime,120)
end,case when min(b.begintime)=b.begintime then convert(varchar(10),dateadd(day,1,max(a.begintime)),120)+'-'+convert(varchar(10),dateadd(day,-1,min(b.begintime)),120)end,
case when max(b.begintime)=b.begintime then convert(varchar(10),b.begintime,120)+'-NULL' end 
from test a
inner join test b on b.id=a.id
where a.begintime!=b.begintime
group by b.begintime,a.begintime--//结果
--------------- --------------------- ---------------
NULL-2011-06-01 2011-06-02-2011-08-31 2011-09-01-NULL(1 行受影响)

Answer 5

http://topic.csdn.net/u/20090917/16/dc77bfd0-78e9-4837-9b46-388446691676.html?53633http://topic.csdn.net/u/20090917/16/dc77bfd0-78e9-4837-9b46-388446691676.html?53633感觉和上面那两个差不多

Answer 6

方法有点笨，但是结果应该对CREATE TABLE TEST
(
ID int NOT NULL,
BEGINTIME DATETIME NOT NULL,
ENDTIME DATETIME NOT NULL
)
GOINSERT INTO TEST
SELECT 1,'2011-08-01','2011-09-01' UNION
SELECT 1,'2011-10-01','2011-11-01' UNION
SELECT 1,'2011-12-01','2012-01-01'SELECT ID,NULL AS BEGINTIME,MIN(BEGINTIME) AS ENDTIME
FROM TEST
GROUP BY ID
UNION
SELECT a.ID,a.ENDTIME,MIN(b.BEGINTIME)
FROM TEST as a,TEST as b
WHERE a.ENDTIME < b.BEGINTIME
GROUP BY a.ID,a.ENDTIME
UNION
SELECT ID,MAX(ENDTIME),NULL
FROM TEST
GROUP BY IDID BEGINTIME         ENDTIME
1 NULL                 2011-08-01 00:00:00.000
1 2011-09-01 00:00:00.000 2011-10-01 00:00:00.000
1 2011-11-01 00:00:00.000 2011-12-01 00:00:00.000
1 2012-01-01 00:00:00.000 NULL

Answer 7

不用UNION的方法CREATE TABLE TEST
(
ID int NOT NULL,
BEGINTIME DATETIME NOT NULL,
ENDTIME DATETIME NOT NULL
)
GOINSERT INTO TEST
SELECT 1,'2011-08-01','2011-09-01' UNION
SELECT 1,'2011-10-01','2011-11-01' UNION
SELECT 1,'2011-12-01','2012-01-01' UNION
SELECT 2,'2011-08-01','2011-09-01' UNION
SELECT 2,'2011-10-01','2011-11-01' UNION
SELECT 2,'2011-12-01','2012-01-01'SELECT CASE 
   WHEN a.ID IS NULL THEN b.ID
   ELSE a.ID end as ID,
   a.ENDTIME AS BEGINTIME,
   MIN(b.BEGINTIME) AS ENDTIME
FROM TEST as a FULL OUTER JOIN TEST as b ON a.ID = b.ID and a.ENDTIME < b.BEGINTIME
GROUP BY CASE 
   WHEN a.ID IS NULL THEN b.ID
   ELSE a.ID end,a.ENDTIME
ORDER BY CASE 
   WHEN a.ID IS NULL THEN b.ID
   ELSE a.ID end,a.ENDTIMEID BEGINTIME               ENDTIME
1 NULL                 2011-08-01 00:00:00.000
1 2011-09-01 00:00:00.000 2011-10-01 00:00:00.000
1 2011-11-01 00:00:00.000 2011-12-01 00:00:00.000
1 2012-01-01 00:00:00.000 NULL
2 NULL                 2011-08-01 00:00:00.000
2 2011-09-01 00:00:00.000 2011-10-01 00:00:00.000
2 2011-11-01 00:00:00.000 2011-12-01 00:00:00.000
2 2012-01-01 00:00:00.000 NULL

Answer 8

select top 1 case when a.begintime=min(a.begintime) then 'NULL-'+convert(varchar(10),min(a.begintime),120)
end,case when min(b.begintime)=b.begintime then convert(varchar(10),dateadd(day,1,max(a.begintime)),120)+'-'+convert(varchar(10),dateadd(day,-1,min(b.begintime)),120)end,
case when max(b.endtime)=b.endtime then convert(varchar(10),max(b.endtime),120)+'-NULL' end 
from test a
inner join test b on b.id=a.id
where a.begintime!=b.begintime and a.ENDTIME < b.BEGINTIME
group by b.begintime,a.begintime,a.endtime,b.endtime
--//--------------- --------------------- ---------------
NULL-2011-08-01 2011-08-02-2011-09-30 2011-11-01-NULL(1 行受影响)

Answer 9

SELECT CASE 
       WHEN a.ID IS NULL THEN b.ID
       ELSE a.ID end as ID,
       isnull(dateadd(day,1,a.ENDTIME),null) AS BEGINTIME,
       isnull(dateadd(day,-1,MIN(b.BEGINTIME)),null) AS ENDTIME
FROM TEST as a FULL OUTER JOIN TEST as b ON a.ID = b.ID and a.ENDTIME < b.BEGINTIME
GROUP BY CASE 
       WHEN a.ID IS NULL THEN b.ID
       ELSE a.ID end,a.ENDTIME
ORDER BY CASE 
       WHEN a.ID IS NULL THEN b.ID
       ELSE a.ID end,a.ENDTIME

Answer 10

create table tb(id int,BegindatePlan datetime,EnddatePlan datetime)
insert into tb select 2,'2005-12-28 23:46:23.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:50:16.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:52:33.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:56:45.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:58:43.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:00:49.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:03:47.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:05:10.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:07:51.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:09:43.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:11:20.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:14:28.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 11:49:03.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 18:28:34.000','2006-05-31 00:00:00.000'
insert into tb select 2,'2006-01-09 23:45:02.000','2006-12-31 00:00:00.000'
insert into tb select 2,'2006-01-16 18:34:29.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-01-16 18:46:24.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-02-12 16:10:34.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-02-12 16:12:23.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-02-28 17:42:47.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-04-12 17:00:05.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-05-08 08:50:29.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2009-10-01 00:00:00.000','2009-10-31 00:00:00.000'
go
select a.id,a.enddatePlan as begindatePlan,b.begindatePlan as endda0tePlan
from tb a inner join tb b on a.id=b.id and a.enddatePlan<b.begindatePlan and not exists(
select 1 from tb where (a.EnddatePlan>=BegindatePlan and a.EnddatePlan<EnddatePlan)
or (b.BegindatePlan>BegindatePlan and b.BegindatePlan<=EnddatePlan))
/*
id          begindatePlan           endda0tePlan
----------- ----------------------- -----------------------
2           2006-12-31 00:00:00.000 2009-10-01 00:00:00.000(1 行受影响)*/go
drop table tb

Answer 11

create table tb(id int,BegindatePlan datetime,EnddatePlan datetime)
insert into tb select 2,'2005-12-28 23:46:23.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:50:16.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:52:33.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:56:45.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:58:43.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:00:49.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:03:47.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:05:10.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:07:51.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:09:43.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:11:20.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:14:28.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 11:49:03.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 18:28:34.000','2006-05-31 00:00:00.000'
insert into tb select 2,'2006-01-09 23:45:02.000','2006-12-31 00:00:00.000'
insert into tb select 2,'2006-01-16 18:34:29.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-01-16 18:46:24.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-02-12 16:10:34.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-02-12 16:12:23.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-02-28 17:42:47.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-04-12 17:00:05.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-05-08 08:50:29.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2009-10-01 00:00:00.000','2009-10-31 00:00:00.000'
go
select ID,null,MIN(begindateplan) from tb group by id
union all
select a.id,a.enddatePlan as begindatePlan,b.begindatePlan as endda0tePlan
from tb a inner join tb b on a.id=b.id and a.enddatePlan<b.begindatePlan and not exists(
select 1 from tb where (a.EnddatePlan>=BegindatePlan and a.EnddatePlan<EnddatePlan)
or (b.BegindatePlan>BegindatePlan and b.BegindatePlan<=EnddatePlan))
union all
select ID,MAX(enddateplan),null from tb group by id
/*
ID                                  
----------- ----------------------- -----------------------
2           NULL                    2005-12-28 23:46:23.000
2           2006-12-31 00:00:00.000 2009-10-01 00:00:00.000
2           2009-10-31 00:00:00.000 NULL(3 行受影响)*/go
drop table tb

Answer 12

create table tb(id int,BegindatePlan datetime,EnddatePlan datetime)
insert into tb select 2,'2005-12-28 23:46:23.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:50:16.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:52:33.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:56:45.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:58:43.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:00:49.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:03:47.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:05:10.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:07:51.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:09:43.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:11:20.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:14:28.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 11:49:03.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 18:28:34.000','2006-05-31 00:00:00.000'
insert into tb select 2,'2006-01-09 23:45:02.000','2006-12-31 00:00:00.000'
insert into tb select 2,'2006-01-16 18:34:29.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-01-16 18:46:24.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-02-12 16:10:34.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-02-12 16:12:23.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-02-28 17:42:47.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-04-12 17:00:05.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-05-08 08:50:29.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2009-10-01 00:00:00.000','2009-10-31 00:00:00.000'
insert into tb select 2,'2011-05-25 00:00:00.000','2011-09-15 00:00:00.000'
go
select ID,null as begindatePlan,MIN(begindateplan)as endda0tePlan from tb group by id
union all
select a.id,a.enddatePlan,b.begindatePlan
from tb a inner join tb b on a.id=b.id and a.enddatePlan<b.begindatePlan and not exists(
select 1 from tb where (BegindatePlan>a.EnddatePlan and EnddatePlan<b.BegindatePlan)
or (BegindatePlan>a.EnddatePlan and BegindatePlan<b.BegindatePlan)
or (EnddatePlan>a.EnddatePlan and EnddatePlan<b.BegindatePlan)
or (BegindatePlan<a.EnddatePlan and EnddatePlan>b.BegindatePlan))
union all
select ID,MAX(enddateplan),null from tb group by id
/*
ID          begindatePlan           endda0tePlan
----------- ----------------------- -----------------------
2           NULL                    2005-12-28 23:46:23.000
2           2006-12-31 00:00:00.000 2009-10-01 00:00:00.000
2           2009-10-31 00:00:00.000 2011-05-25 00:00:00.000
2           2011-09-15 00:00:00.000 NULL(4 行受影响)*/go
drop table tb

Answer 13

create table tb(id int,BegindatePlan datetime,EnddatePlan datetime)
insert into tb select 2,'2005-12-28 23:46:23.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:50:16.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:52:33.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:56:45.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-28 23:58:43.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:00:49.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:03:47.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:05:10.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:07:51.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:09:43.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:11:20.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 00:14:28.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 11:49:03.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2005-12-29 18:28:34.000','2006-05-31 00:00:00.000'
insert into tb select 2,'2006-01-09 23:45:02.000','2006-12-31 00:00:00.000'
insert into tb select 2,'2006-01-16 18:34:29.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-01-16 18:46:24.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-02-12 16:10:34.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-02-12 16:12:23.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-02-28 17:42:47.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-04-12 17:00:05.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2006-05-08 08:50:29.000','2006-12-30 00:00:00.000'
insert into tb select 2,'2009-10-01 00:00:00.000','2009-10-31 00:00:00.000'
insert into tb select 2,'2011-05-25 00:00:00.000','2011-09-15 00:00:00.000'
go
select ID,null as begindatePlan,MIN(begindateplan)as enddatePlan from tb group by id
union all
select a.id,a.enddatePlan,b.begindatePlan
from tb a inner join tb b on a.id=b.id and a.enddatePlan<b.begindatePlan and not exists(
select 1 from tb where (BegindatePlan>a.EnddatePlan and EnddatePlan<b.BegindatePlan)
or (BegindatePlan>a.EnddatePlan and BegindatePlan<b.BegindatePlan)
or (EnddatePlan>a.EnddatePlan and EnddatePlan<b.BegindatePlan)
or (BegindatePlan<a.EnddatePlan and EnddatePlan>b.BegindatePlan))
union all
select ID,MAX(enddateplan),null from tb group by id
/*
ID          begindatePlan           enddatePlan
----------- ----------------------- -----------------------
2           NULL                    2005-12-28 23:46:23.000
2           2006-12-31 00:00:00.000 2009-10-01 00:00:00.000
2           2009-10-31 00:00:00.000 2011-05-25 00:00:00.000
2           2011-09-15 00:00:00.000 NULL(4 行受影响)
*/go
drop table tb

Answer 14

我用SQL SERVER 试过了，是好用的，只要你把时间前面的加1天，后面的减一天就行了