求助SQL语句查询，进来看看！

[ccooer]

两张不同的考勤表：
一张是考勤签到表，字段有有上班打卡时间，和下班打卡时间，
2014-03-13 2014-03-13
2014-03-14 2014-03-14
2014-03-15 2014-03-15
2014-03-16 2014-03-16
2014-03-25 2014-03-25
2014-03-26 2014-03-26
2014-03-27 2014-03-27
2014-03-28 2014-03-28
一张是休假表，字段是开始休假时间，结束休假时间，
2014-03-15 2014-03-17
2014-03-24 2014-03-26
根据上面的记录现在要把考勤表中如果存在休假表中区间的记录过滤掉~
结果想要：
2014-03-13 2014-03-13
2014-03-14 2014-03-14
2014-03-27 2014-03-27
2014-03-28 2014-03-28

非常感谢！！！

[jorkin_me]

(上班打卡时间 not between 开始休假时间 and 结束休假时间) and (下班打卡时间 not between 开始休假时间 and 结束休假时间)

[KeepSayingNo]

这个求出所有的请假的天

;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

[KeepSayingNo]

完整的代码如下：

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

[iamcool345]

/*------------------------ 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

[iamcool345]

MERGE修改了源表#ta，所以有必要的话先备份，或者MERGE的结果查到另一个表。

[iamcool345]

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

 

[zhu8228699]

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))

刚学了几天数据库，纯粹练手 嘿嘿

[tiktop]

[code=sqlcreate table ta1([Start] smalldatetime,[End] smalldatetime) 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