34,588
社区成员
发帖
与我相关
我的任务
分享
declare @tb table (id int,date datetime,date1 datetime,date2 datetime,date3 datetime)
insert into @tb select 1,'2009-11-01','2009-11-1','2009-11-02','2009-11-01'
union all select 2,'2009-11-01','2009-11-2','2009-11-01','2009-11-01'
union all select 3,'2009-11-01','2009-11-1','2009-11-01','2009-11-01'
union all select 4,'2009-11-01','2009-11-2','2009-11-01','2009-11-02'
union all select 5,'2009-10-26','2009-11-1','2009-10-26','2009-11-01'
union all select 6,'2009-10-28','2009-10-28','2009-10-31','2009-10-28'
select * from @tb a where
not exists(select 1 from @tb where a.date=date and a.date=date1 and a.date=date2 and a.date=date3)
/*
(6 行受影响)
id date date1 date2 date3
----------- ----------------------- ----------------------- ----------------------- -----------------------
5 2009-10-26 00:00:00.000 2009-11-01 00:00:00.000 2009-10-26 00:00:00.000 2009-11-01 00:00:00.000
6 2009-10-28 00:00:00.000 2009-10-28 00:00:00.000 2009-10-31 00:00:00.000 2009-10-28 00:00:00.000
(2 行受影响)
*/
--> 测试数据:@table
declare @table table([ID] int,[Date] varchar(10),[Date1] varchar(10),[Date2] varchar(10),[Date3] varchar(10))
insert @table
select 1,'2009-11-01','2009-11-01','2009-11-02','2009-11-01' union all
select 2,'2009-11-01','2009-11-02','2009-11-01','2009-11-01' union all
select 3,'2009-11-01','2009-11-01','2009-11-01','2009-11-01' union all
select 4,'2009-11-01','2009-11-01','2009-11-01','2009-11-02' union all
select 5,'2009-10-26','2009-11-01','2009-10-26','2009-11-01' union all
select 6,'2009-10-28','2009-10-28','2009-10-31','2009-10-28'
declare @search varchar(10)
select @search = '2009-11-02'
select * from @table
where ((cast([Date1] as datetime) >= case when datepart(weekday,@search)=2 then dateadd(day,-2,@search)
else dateadd(day,-1,@search) end and cast([Date1] as datetime) <= dateadd(day,-1,@search))
or (cast([Date2] as datetime) >= case when datepart(weekday,@search)=2 then dateadd(day,-2,@search)
else dateadd(day,-1,@search) end and cast([Date2] as datetime) <= dateadd(day,-1,@search))
or (cast([Date3] as datetime) >= case when datepart(weekday,@search)=2 then dateadd(day,-2,@search)
else dateadd(day,-1,@search) end and cast([Date3] as datetime) <= dateadd(day,-1,@search))
)
and cast([Date] as datetime) <> dateadd(day,-1,@search)
--结果
-----------------------------------
5 2009-10-26 2009-11-01 2009-10-26 2009-11-01
6 2009-10-28 2009-10-28 2009-10-31 2009-10-28