有个tableA,里面有个DateTime类型字段:FieldA,现在要从这个表里面select出来一些记录,要求DateTime类型字段FieldA的值不在另外一个临时表@TblB的所有日期范围内,tableB是这样定义的: DECLARE @TblB TABLE
(
dtStart DateTime,
dtEnd DateTime
)这个表里面的记录是这样的: dtStart dtEnd
---------------------------------------
2011-11-22 14:49:06 2011-11-22 14:51:23
2011-11-23 15:02:48 2011-11-23 15:05:20
2011-11-24 09:34:12 2011-11-24 09:41:35
......
.....这个where语句要求FieldA的值不在这个临时表@TblB所有日期范围内(所有记录 not between dtStart and dtEnd)。这个sql语句怎么写?搞定就给分结贴!
(
dtStart DateTime,
dtEnd DateTime
)这个表里面的记录是这样的: dtStart dtEnd
---------------------------------------
2011-11-22 14:49:06 2011-11-22 14:51:23
2011-11-23 15:02:48 2011-11-23 15:05:20
2011-11-24 09:34:12 2011-11-24 09:41:35
......
.....这个where语句要求FieldA的值不在这个临时表@TblB所有日期范围内(所有记录 not between dtStart and dtEnd)。这个sql语句怎么写?搞定就给分结贴!
FieldA < (select min(dtStart) from @TblB) or
FieldA > (select max(dtEnd) from @TblB)
Select * From tableA Where Not Exists(Select 1 From @TblB Where tableA.FieldA between dtStart and dtEnd
select a.FieldA from tableA a join @TblB b
on (a.FieldA not between b.dtStart and b.dtEnd)
Select * From tableA Where Not Exists(Select 1 From @TblB Where tableA.FieldA between dtStart and dtEnd)
--刚才的少了个括号
Where Not Exists(Select 1 From @TblB Where tableA.FieldA between dtStart and dtEnd
select a.* from tablea a ,
(select m.dtEnd dtStart , (select top 1 dtStart from @TblB n where n.dtStart > m.dtStart order by dtStart) dtEnd from @TblB m) b
where (a.FieldA > b.dtStart and a.FieldA < b.dtEnd and b.dtEnd is not null) or
FieldA < (select min(dtStart) from @TblB) or
FieldA > (select max(dtEnd) from @TblB)
where not exists(Select 1 From @TblB Where tableA.FieldA between dtStart and dtEnd)
*
from
tableA t
where
not exists(Select 1 From @TblB Where t.FieldA between dtStart and dtEnd)
select * from tableA
where FieldA between
(select min(dtStart) from @TBlB) and (select max(dtEnd) from @TBlb)
--把@TBlb中的最小开始日期和最大结束日期取出来