这个求出所有的请假的天;WITH CTE AS ( SELECT xj_begintime, case when CONVERT(varchar(12) , xj_begintime, 112 )=CONVERT(varchar(12) , xj_endtime, 112 ) then xj_endtime else CAST ( CONVERT(varchar(12) , dateadd(dd,1,xj_begintime), 111 )+' 00:00:00' as datetime) end as stat_day, xj_endtime FROM Table_XJ union all select a.stat_day, case when CONVERT(varchar(12) , a.stat_day , 112 )=CONVERT(varchar(12) , b.xj_endtime, 112 ) then b.xj_endtime else CAST ( CONVERT(varchar(12) , DATEADD (dd,1,a.stat_day ), 111 )+' 00:00:00' as datetime) end as stat_day, a.xj_endtime from cte a join Table_XJ b on a.xj_endtime =b.xj_endtime where a.stat_day <b.xj_endtime
) select xj_begintime as xj_time from CTE union select xj_endtime from Table_XJ as xj_time
完整的代码如下:WITH CTE AS ( SELECT xj_begintime, case when CONVERT(varchar(12) , xj_begintime, 112 )=CONVERT(varchar(12) , xj_endtime, 112 ) then xj_endtime else CAST ( CONVERT(varchar(12) , dateadd(dd,1,xj_begintime), 111 )+' 00:00:00' as datetime) end as stat_day, xj_endtime FROM Table_XJ union all select a.stat_day, case when CONVERT(varchar(12) , a.stat_day , 112 )=CONVERT(varchar(12) , b.xj_endtime, 112 ) then b.xj_endtime else CAST ( CONVERT(varchar(12) , DATEADD (dd,1,a.stat_day ), 111 )+' 00:00:00' as datetime) end as stat_day, a.xj_endtime from cte a join Table_XJ b on a.xj_endtime =b.xj_endtime where a.stat_day <b.xj_endtime
) select * from Table_DK left join ( select xj_begintime as xj_time from CTE union select xj_endtime from Table_XJ as xj_time ) t on Table_DK.dk_begintime=t.xj_time where xj_time is null
insert into #ta select '2014-03-13','2014-03-13' union all select '2014-03-14','2014-03-14' union all select '2014-03-15','2014-03-15' union all select '2014-03-16','2014-03-16' union all select '2014-03-25','2014-03-25' union all select '2014-03-26','2014-03-26' union all select '2014-03-27','2014-03-27' union all select '2014-03-28','2014-03-28'create table #tb([HolidayStart] smalldatetime,[HolidayEnd] smalldatetime)
insert into #tb select '2014-03-15','2014-03-17' union all select '2014-03-24','2014-03-26' ;merge #ta as target using(select HolidayStart,HolidayEnd from #tb) as source(HolidayStart,HolidayEnd ) ON target.[Start]>=source.HolidayStart and target.[End]<=source.HolidayEnd when matched then delete;select * from #ta ------------------------*/(8 行受影响)(2 行受影响)(4 行受影响)(4 行受影响)结果: 2014-03-13 00:00:00 2014-03-13 00:00:00 2014-03-14 00:00:00 2014-03-14 00:00:00 2014-03-27 00:00:00 2014-03-27 00:00:00 2014-03-28 00:00:00 2014-03-28 00:00:00
INSERT INTO #ta SELECT '2014-03-13','2014-03-13' UNION ALL SELECT '2014-03-14','2014-03-14' UNION ALL SELECT '2014-03-15','2014-03-15' UNION ALL SELECT '2014-03-16','2014-03-16' UNION ALL SELECT '2014-03-25','2014-03-25' UNION ALL SELECT '2014-03-26','2014-03-26' UNION ALL SELECT '2014-03-27','2014-03-27' UNION ALL CREATE TABLE #ta([Start] smalldatetime,[End] smalldatetime)CREATE TABLE #tb([HolidayStart] smalldatetime,[HolidayEnd] smalldatetime)
INSERT INTO #tb SELECT '2014-03-15','2014-03-17' UNION ALL SELECT '2014-03-24','2014-03-26' ;
MERGE #ta AS target USING(SELECT HolidayStart,HolidayEnd FROM #tb) AS source(HolidayStart,HolidayEnd ) ON target.[Start]>=source.HolidayStart and target.[End]<=source.HolidayEnd WHEN matched then delete;SELECT * FROM #ta MERGE #ta AS target USING(SELECT HolidayStart,HolidayEnd FROM #tb) AS source(HolidayStart,HolidayEnd ) ON target.[Start]>=source.HolidayStart and target.[End]<=source.HolidayEnd WHEN matched then delete;SELECT * FROM #ta
SELECT * FROM date_in WHERE (date_in.time_start NOT BETWEEN (SELECT MIN(date_out.time_start) FROM date_out)AND(SELECT MAX(date_out.time_start) FROM date_out)) AND (date_in.time_end NOT BETWEEN (SELECT MIN(date_out.time_end) FROM date_out)AND(SELECT MAX(date_out.time_end) FROM date_out))刚学了几天数据库,纯粹练手 嘿嘿
insert into ta1 select '2014-03-13','2014-03-13' union all select '2014-03-14','2014-03-14' union all select '2014-03-15','2014-03-15' union all select '2014-03-16','2014-03-16' union all select '2014-03-25','2014-03-25' union all select '2014-03-26','2014-03-26' union all select '2014-03-27','2014-03-27' union all select '2014-03-28','2014-03-28'create table tb2([HolidayStart] smalldatetime,[HolidayEnd] smalldatetime)
insert into tb2 select '2014-03-15','2014-03-17' union all select '2014-03-24','2014-03-26' ;with ta11 as( select a.*,b.* from ta1 a ,tb2 b ) , ta22 as( select b.Start , b.[End] from ta11 b where b.Start >=b.HolidayStart and b.[End]<=b.HolidayEnd ) select * from ta1 where not exists (select * from ta22 where ta1.Start =ta22.Start and ta1.[End]=ta22.[End]) ][/code]Start End ----------------------- ----------------------- 2014-03-13 00:00:00 2014-03-13 00:00:00 2014-03-14 00:00:00 2014-03-14 00:00:00 2014-03-27 00:00:00 2014-03-27 00:00:00 2014-03-28 00:00:00 2014-03-28 00:00:00
(
SELECT xj_begintime,
case when CONVERT(varchar(12) , xj_begintime, 112 )=CONVERT(varchar(12) , xj_endtime, 112 ) then xj_endtime
else CAST ( CONVERT(varchar(12) , dateadd(dd,1,xj_begintime), 111 )+' 00:00:00' as datetime) end as stat_day,
xj_endtime
FROM Table_XJ
union all
select a.stat_day,
case when CONVERT(varchar(12) , a.stat_day , 112 )=CONVERT(varchar(12) , b.xj_endtime, 112 ) then b.xj_endtime
else CAST ( CONVERT(varchar(12) , DATEADD (dd,1,a.stat_day ), 111 )+' 00:00:00' as datetime) end as stat_day,
a.xj_endtime
from cte a join Table_XJ b on a.xj_endtime =b.xj_endtime
where a.stat_day <b.xj_endtime
)
select xj_begintime as xj_time
from CTE
union
select xj_endtime from Table_XJ as xj_time
(
SELECT xj_begintime,
case when CONVERT(varchar(12) , xj_begintime, 112 )=CONVERT(varchar(12) , xj_endtime, 112 ) then xj_endtime
else CAST ( CONVERT(varchar(12) , dateadd(dd,1,xj_begintime), 111 )+' 00:00:00' as datetime) end as stat_day,
xj_endtime
FROM Table_XJ
union all
select a.stat_day,
case when CONVERT(varchar(12) , a.stat_day , 112 )=CONVERT(varchar(12) , b.xj_endtime, 112 ) then b.xj_endtime
else CAST ( CONVERT(varchar(12) , DATEADD (dd,1,a.stat_day ), 111 )+' 00:00:00' as datetime) end as stat_day,
a.xj_endtime
from cte a join Table_XJ b on a.xj_endtime =b.xj_endtime
where a.stat_day <b.xj_endtime
) select * from Table_DK
left join
(
select xj_begintime as xj_time
from CTE
union
select xj_endtime from Table_XJ as xj_time
) t on Table_DK.dk_begintime=t.xj_time where xj_time is null
create table #ta([Start] smalldatetime,[End] smalldatetime)
insert into #ta
select '2014-03-13','2014-03-13' union all
select '2014-03-14','2014-03-14' union all
select '2014-03-15','2014-03-15' union all
select '2014-03-16','2014-03-16' union all
select '2014-03-25','2014-03-25' union all
select '2014-03-26','2014-03-26' union all
select '2014-03-27','2014-03-27' union all
select '2014-03-28','2014-03-28'create table #tb([HolidayStart] smalldatetime,[HolidayEnd] smalldatetime)
insert into #tb
select '2014-03-15','2014-03-17' union all
select '2014-03-24','2014-03-26'
;merge #ta as target
using(select HolidayStart,HolidayEnd from #tb) as source(HolidayStart,HolidayEnd )
ON target.[Start]>=source.HolidayStart and target.[End]<=source.HolidayEnd
when matched then delete;select * from #ta
------------------------*/(8 行受影响)(2 行受影响)(4 行受影响)(4 行受影响)结果:
2014-03-13 00:00:00 2014-03-13 00:00:00
2014-03-14 00:00:00 2014-03-14 00:00:00
2014-03-27 00:00:00 2014-03-27 00:00:00
2014-03-28 00:00:00 2014-03-28 00:00:00
CREATE TABLE #ta([Start] smalldatetime,[End] smalldatetime)
INSERT INTO #ta
SELECT '2014-03-13','2014-03-13' UNION ALL
SELECT '2014-03-14','2014-03-14' UNION ALL
SELECT '2014-03-15','2014-03-15' UNION ALL
SELECT '2014-03-16','2014-03-16' UNION ALL
SELECT '2014-03-25','2014-03-25' UNION ALL
SELECT '2014-03-26','2014-03-26' UNION ALL
SELECT '2014-03-27','2014-03-27' UNION ALL
CREATE TABLE #ta([Start] smalldatetime,[End] smalldatetime)CREATE TABLE #tb([HolidayStart] smalldatetime,[HolidayEnd] smalldatetime)
INSERT INTO #tb
SELECT '2014-03-15','2014-03-17' UNION ALL
SELECT '2014-03-24','2014-03-26'
;
MERGE #ta AS target
USING(SELECT HolidayStart,HolidayEnd FROM #tb) AS source(HolidayStart,HolidayEnd )
ON target.[Start]>=source.HolidayStart and target.[End]<=source.HolidayEnd
WHEN matched then delete;SELECT * FROM #ta
MERGE #ta AS target
USING(SELECT HolidayStart,HolidayEnd FROM #tb) AS source(HolidayStart,HolidayEnd )
ON target.[Start]>=source.HolidayStart and target.[End]<=source.HolidayEnd
WHEN matched then delete;SELECT * FROM #ta
(date_in.time_start NOT BETWEEN (SELECT MIN(date_out.time_start) FROM date_out)AND(SELECT MAX(date_out.time_start) FROM date_out))
AND
(date_in.time_end NOT BETWEEN (SELECT MIN(date_out.time_end) FROM date_out)AND(SELECT MAX(date_out.time_end) FROM date_out))刚学了几天数据库,纯粹练手 嘿嘿
insert into ta1
select '2014-03-13','2014-03-13' union all
select '2014-03-14','2014-03-14' union all
select '2014-03-15','2014-03-15' union all
select '2014-03-16','2014-03-16' union all
select '2014-03-25','2014-03-25' union all
select '2014-03-26','2014-03-26' union all
select '2014-03-27','2014-03-27' union all
select '2014-03-28','2014-03-28'create table tb2([HolidayStart] smalldatetime,[HolidayEnd] smalldatetime)
insert into tb2
select '2014-03-15','2014-03-17' union all
select '2014-03-24','2014-03-26'
;with ta11
as(
select a.*,b.*
from ta1 a ,tb2 b
) , ta22
as(
select b.Start , b.[End]
from ta11 b
where b.Start >=b.HolidayStart and b.[End]<=b.HolidayEnd
)
select *
from ta1
where not exists (select * from ta22 where ta1.Start =ta22.Start and ta1.[End]=ta22.[End]) ][/code]Start End
----------------------- -----------------------
2014-03-13 00:00:00 2014-03-13 00:00:00
2014-03-14 00:00:00 2014-03-14 00:00:00
2014-03-27 00:00:00 2014-03-27 00:00:00
2014-03-28 00:00:00 2014-03-28 00:00:00