问题如下:
id cu_companyname s_time e_time
1 abc 2000-12-5 2001-4-5
2 abc 2004-2-15 2005-12-9
3 abc 2009-5-15 2010-3-20
用SQ语句查询后得到的结果:
(我选择的时间段为:2005-1-1至2010-1-1) id cu_companyname s_time e_time
3 abc 2009-5-15 2010-3-20 SQ语句怎么写呢????
id cu_companyname s_time e_time
1 abc 2000-12-5 2001-4-5
2 abc 2004-2-15 2005-12-9
3 abc 2009-5-15 2010-3-20
用SQ语句查询后得到的结果:
(我选择的时间段为:2005-1-1至2010-1-1) id cu_companyname s_time e_time
3 abc 2009-5-15 2010-3-20 SQ语句怎么写呢????
select * from tablename where s_time between '2005-1-1' and '2010-1-1'
from ta
where s_time between '2005-1-1' and '2010-1-1'
or e_time between '2005-1-1' and '2010-1-1'
from ta
where s_time between '2005-01-01' and '2010-01-01'
or e_time between '2005-01-01' and '2010-01-01'是否还有其它情况,是要两者都包含在内的话要用AND,如果不是就上面
不好意思,我给的数据太少了,你没看出问题来,如果我的数据太多,如果是这样呢:
id cu_companyname s_time e_time
1 abc 2000-12-5 2001-4-5
2 abc 2004-2-15 2005-12-9
3 abc 2009-5-15 2010-3-20
4 abc 2005-1-1 2010-3-20
5 abc 2005-9-1 2009-3-20
6 abc 2007-1-1 209-8-20用SQ语句查询后得到的结果:
(我选择的时间段为:s_time:2005-1-1至e_time2010-1-1) id cu_companyname s_time e_time
3 abc 2009-5-15 2010-3-20
不好意思,我给的数据太少了,你没看出问题来,如果我的数据太多,如果是这样呢:
id cu_companyname s_time e_time
1 abc 2000-12-5 2001-4-5
2 abc 2004-2-15 2005-12-9
3 abc 2009-5-15 2010-3-20
4 abc 2005-1-1 2010-3-20
5 abc 2005-9-1 2009-3-20
6 abc 2007-1-1 209-8-20 用SQ语句查询后得到的结果:
(我选择的时间段为:s_time:2005-1-1至e_time2010-1-1) id cu_companyname s_time e_time
3 abc 2009-5-15 2010-3-20
insert into tb values(1 , 'abc' , '2000-12-5', '2001-4-5')
insert into tb values(2 , 'abc' , '2004-2-15', '2005-12-9')
insert into tb values(3 , 'abc' , '2009-5-15', '2010-3-20')
insert into tb values(4 , 'abc' , '2005-1-1' , '2010-3-20')
insert into tb values(5 , 'abc' , '2005-9-1' , '2009-3-20')
insert into tb values(6 , 'abc' , '2007-1-1' , '2009-8-20')
go--需要使用一个临时表来获取你开始到结束的所有日期
SELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO tmp FROM syscolumns a, syscolumns b --定义起始结束日期
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2005-1-1'
set @dt2 = '2010-1-1'--按照s_time来算。
select distinct m.* from tb m ,
(select dateadd(dd,tmp.id,@dt1) dt from tmp where dateadd(dd,tmp.id,@dt1) <= @dt2) n
where n.dt between m.s_time and m.e_time
and m.s_time = (select max(s_time) from tb where cu_companyname = m.cu_companyname)
/*
id cu_companyname s_time e_time
----------- -------------- ------------------------------------------------------ ------------------------------------------------------
3 abc 2009-05-15 00:00:00.000 2010-03-20 00:00:00.000(所影响的行数为 1 行)
*/--按照e_time来算。
select distinct m.* from tb m ,
(select dateadd(dd,tmp.id,@dt1) dt from tmp where dateadd(dd,tmp.id,@dt1) <= @dt2) n
where n.dt between m.s_time and m.e_time
and m.e_time = (select max(e_time) from tb where cu_companyname = m.cu_companyname)
/*
id cu_companyname s_time e_time
----------- -------------- ------------------------------------------------------ ------------------------------------------------------
3 abc 2009-05-15 00:00:00.000 2010-03-20 00:00:00.000
4 abc 2005-01-01 00:00:00.000 2010-03-20 00:00:00.000(所影响的行数为 2 行)
*/drop table tb , tmp
select * from ta
where s_time between '2005-1-1' and '2010-1-1'
and e_time between '2005-1-1' and '2010-1-1'
到底是对开始时间s_time 过滤,还是对结束时间e_time也过滤呢,
安你给的数据,上面的sql确实可以实现了如果后者就用 select *
from ta
where s_time between '2005-1-1' and '2010-1-1'
or e_time between '2005-1-1' and '2010-1-1'