表结构:
id sdd edd
1 2009-01-01 2009-01-31
2 2009-01-03 2009-01-03
3 2009-01-03 2009-01-08
4 2009-01-01 2009-01-03
5 2009-01-06 2009-01-08
6 2009-01-01 2009-01-01我现在给出一个时间段比如:2009-01-02到2009-01-06
我要求查出sdd到edd之间所有包含给定日期段之间的日期.只要包含了就取出来.
那么上面的就要得到1,2,3,4条记录,只有5,6不符合.
我已经晕了.可能还有一些数据未举出来,
id sdd edd
1 2009-01-01 2009-01-31
2 2009-01-03 2009-01-03
3 2009-01-03 2009-01-08
4 2009-01-01 2009-01-03
5 2009-01-06 2009-01-08
6 2009-01-01 2009-01-01我现在给出一个时间段比如:2009-01-02到2009-01-06
我要求查出sdd到edd之间所有包含给定日期段之间的日期.只要包含了就取出来.
那么上面的就要得到1,2,3,4条记录,只有5,6不符合.
我已经晕了.可能还有一些数据未举出来,
union all
select * from 表 where edd between '2009-01-02' and '2009-01-06'
From ((Select * From Tb where sdd Between '2009-01-02' and '2009-01-06') Union all
(Select * From Tb where edd Between '2009-01-02' and '2009-01-06')) F
insert @a select 1,'2009-01-01','2009-01-31'
union all select 2,'2009-01-03','2009-01-03'
union all select 3,'2009-01-03','2009-01-08'
union all select 4,'2009-01-01','2009-01-03'
union all select 5,'2009-01-06','2009-01-08'
union all select 6,'2009-01-01','2009-01-01'
DECLARE @s VARCHAR(10),@e VARCHAR(10)
SELECT @s='2009-01-02',@e='2009-01-06'
SELECT * FROM @a WHERE not (@e<=sdd OR @s>=edd)
--result
/*id sdd edd
----------- ---------- ----------
1 2009-01-01 2009-01-31
2 2009-01-03 2009-01-03
3 2009-01-03 2009-01-08
4 2009-01-01 2009-01-03(所影响的行数为 4 行)
*/
DECLARE @a TABLE(id INT,sdd varchar(10),edd VARCHAR(10))
insert @a select 1,'2009-01-01','2009-01-31'
union all select 2,'2009-01-03','2009-01-03'
union all select 3,'2009-01-03','2009-01-08'
union all select 4,'2009-01-01','2009-01-03'
union all select 5,'2009-01-06','2009-01-08'
union all select 6,'2009-01-01','2009-01-01'
DECLARE @s VARCHAR(10),@e VARCHAR(10)
SELECT @s='2009-01-02',@e='2009-01-06'
SELECT * FROM @a WHERE @e>sdd and @s<edd
/*
id sdd edd
----------- ---------- ----------
1 2009-01-01 2009-01-31
2 2009-01-03 2009-01-03
3 2009-01-03 2009-01-08
4 2009-01-01 2009-01-03(所影响的行数为 4 行)
*/
DECLARE @a TABLE(id INT,sdd varchar(10),edd VARCHAR(10))
insert @a select 1,'2009-01-01','2009-01-31'
union all select 2,'2009-01-03','2009-01-03'
union all select 3,'2009-01-03','2009-01-08'
union all select 4,'2009-01-01','2009-01-03'
union all select 5,'2009-01-06','2009-01-08'
union all select 6,'2009-01-01','2009-01-01'
DECLARE @s VARCHAR(10),@e VARCHAR(10)
SELECT @s='2009-01-02',@e='2009-01-06'
SELECT * FROM @a WHERE @e>=sdd and @s<edd
/*
id sdd edd
----------- ---------- ----------
1 2009-01-01 2009-01-31
2 2009-01-03 2009-01-03
3 2009-01-03 2009-01-08
4 2009-01-01 2009-01-03
5 2009-01-06 2009-01-08(所影响的行数为 5 行)*/
DECLARE @a TABLE(id INT,sdd varchar(10),edd VARCHAR(10))
insert @a select 1,'2009-01-01','2009-01-31'
union all select 2,'2009-01-03','2009-01-03'
union all select 3,'2009-01-03','2009-01-08'
union all select 4,'2009-01-01','2009-01-03'
union all select 5,'2009-01-06','2009-01-08'
union all select 6,'2009-01-01','2009-01-01'
DECLARE @s VARCHAR(10),@e VARCHAR(10)
SELECT @s='2009-01-02',@e='2009-01-06'
SELECT * FROM @a WHERE @e>=sdd and @s<=edd
/*
id sdd edd
----------- ---------- ----------
1 2009-01-01 2009-01-31
2 2009-01-03 2009-01-03
3 2009-01-03 2009-01-08
4 2009-01-01 2009-01-03
5 2009-01-06 2009-01-08(所影响的行数为 5 行)
*/
--> (让你望见影子的墙)生成测试数据,时间:2009-02-18
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[sdd] Datetime,[edd] Datetime)
Insert tb
select 1,'2009-01-01','2009-01-31' union all
select 2,'2009-01-03','2009-01-03' union all
select 3,'2009-01-03','2009-01-08' union all
select 4,'2009-01-01','2009-01-03' union all
select 5,'2009-01-06','2009-01-08' union all
select 6,'2009-01-01','2009-01-01'
Go
Select * from tbdeclare @start datetime,@end datetime
set @start='2009-01-02'
set @end='2009-01-06'select id,sdd,edd
from tb
where (sdd<=@start and edd>=@start) or(sdd>=@start and sdd<=@end)
1 2009-01-01 00:00:00.000 2009-01-31 00:00:00.000
2 2009-01-03 00:00:00.000 2009-01-03 00:00:00.000
3 2009-01-03 00:00:00.000 2009-01-08 00:00:00.000
4 2009-01-01 00:00:00.000 2009-01-03 00:00:00.000
5 2009-01-06 00:00:00.000 2009-01-08 00:00:00.000
select * from table where (sdd >= '2009-01-02' and sdd <='2009-01-06') or (edd >= '2009-01-02' and edd <='2009-01-06') or (add <= '2009-01-02' and edd >='2009-01-06')再多加一中情况了,这样可以不?不可以了继续加,好象也用不着那么麻烦吧
SELECT * FROM 表
WHERE ('2009-01-02' BETWEEN SDD AND EDD)
OR ('2009-01-06' BETWEEN SDD AND EDD )
OR (SDD BETWEEN '2009-01-02' AND '2009-01-06')
OR (EDD BETWEEN '2009-01-02' AND '2009-01-06' )