字段1 intime 表示进厂时间,字段2 outtime 表示出厂时间,字段3 C表示车名
在intime 与 outtime 表示在厂里
c intime outtime
车A 2009-10-2 2009-10-9
车B 2009-10-4 2009-10-10
车C 2009-10-2 2009-10-5
车D 2009-10-5 2009-10-8我想查2009-10-3 到 2009-10-8时间段 在厂里的车的名字??????
在intime 与 outtime 表示在厂里
c intime outtime
车A 2009-10-2 2009-10-9
车B 2009-10-4 2009-10-10
车C 2009-10-2 2009-10-5
车D 2009-10-5 2009-10-8我想查2009-10-3 到 2009-10-8时间段 在厂里的车的名字??????
select * from table where intime<='2009-10-3 ' and outtime >='2009-10-8'
Select * From t where intime<='2009-10-8' and outtime>='2009-10-3'
select * from table where intime<='2009-10-3 ' and outtime >='2009-10-8'
應該是Or的關係
Select * From t where intime<='2009-10-8' or outtime>='2009-10-3'
where intime between '2009-10-3' and '2009-10-8'
or outtime between '2009-10-3' and '2009-10-8'
select C from tb
where intime<='2009-10-3'
and outtime >='2009-10-8'
insert tb select '车A','2009-10-2','2009-10-9'
insert tb select '车B','2009-10-4','2009-10-10'
insert tb select '车C','2009-10-2','2009-10-5'
insert tb select '车D','2009-10-5','2009-10-8'select * from tb where intime between '20091003' and '20091008' or [outtime] between '20091003' and '20091008'
/*
c intime outtime
---------- ----------------------- -----------------------
车B 2009-10-04 00:00:00.000 2009-10-10 00:00:00.000
车C 2009-10-02 00:00:00.000 2009-10-05 00:00:00.000
车D 2009-10-05 00:00:00.000 2009-10-08 00:00:00.000(3 行受影响)
*/drop table tb
(
c varchar(20),
intime datetime,
outtime datetime
)
insert into #c select '车A','2009-10-2',' 2009-10-9'
union all select '车B','2009-10-4',' 2009-10-10'
union all select '车C','2009-10-2',' 2009-10-5'
union all select '车D','2009-10-5',' 2009-10-8'
--我想查2009-10-3 到 2009-10-8时间段 在厂里的车的名字??????select * from #c where intime>='2009-10-3 ' and outtime <='2009-10-8'c intime outtime
-------------------- ----------------------- -----------------------
车D 2009-10-05 00:00:00.000 2009-10-08 00:00:00.000(1 行受影响)
(
select * from #c where intime>='2009-10-3 ' and outtime <='2009-10-8' and C.c=c
)
insert tb select '车A','2009-10-2','2009-10-9'
insert tb select '车B','2009-10-4','2009-10-10'
insert tb select '车C','2009-10-2','2009-10-5'
insert tb select '车D','2009-10-5','2009-10-8'select * from tb where '20091003' between intime and [outtime] or '20091008' between intime and [outtime]
/*
c intime outtime
---------- ----------------------- -----------------------
车A 2009-10-02 00:00:00.000 2009-10-09 00:00:00.000
车B 2009-10-04 00:00:00.000 2009-10-10 00:00:00.000
车C 2009-10-02 00:00:00.000 2009-10-05 00:00:00.000
车D 2009-10-05 00:00:00.000 2009-10-08 00:00:00.000(4 行受影响)*/drop table tbmodify
(
c varchar(20),
intime datetime,
outtime datetime
)
insert into #c select '车A','2009-10-2',' 2009-10-9'
union all select '车B','2009-10-4',' 2009-10-10'
union all select '车C','2009-10-2',' 2009-10-5'
union all select '车D','2009-10-5',' 2009-10-8'
--我想查2009-10-3 到 2009-10-8时间段 在厂里的车的名字??????select * from #C C where exists
(
select * from #c where intime>='2009-10-3 ' or outtime <='2009-10-8' and C.c=c
)c intime outtime
-------------------- ----------------------- -----------------------
车A 2009-10-02 00:00:00.000 2009-10-09 00:00:00.000
车B 2009-10-04 00:00:00.000 2009-10-10 00:00:00.000
车C 2009-10-02 00:00:00.000 2009-10-05 00:00:00.000
车D 2009-10-05 00:00:00.000 2009-10-08 00:00:00.000(4 行受影响)
select * from table where intime>='2009-10-3 ' and outtime >='2009-10-8'
select C from tb where intime <'2009-10-8'
and outtime >'2009-10-3'
select C from tb where intime <'2009-10-8'
and outtime >'2009-10-3'
and (outtime is null or outtime >'2009-10-3' )
--同理,当outtime为NULL时,intime<'2009-10-5'的记录亦不能认定.
--因此,当有一为NULL时,另一时间必须在'2009-10-5'~'2009-10-9'之间才能被认定.
create table tb([c] varchar(10),[intime] datetime,[outtime] datetime)
insert tb select '车A','2009-10-2','2009-10-4'
insert tb select '车B','2009-10-4','2009-10-10'
insert tb select '车C','2009-10-2','2009-10-3'
insert tb select '车D','2009-10-5','2009-10-8'
insert tb select '车E','2009-10-6','2009-10-7'
insert tb select '车F','2009-10-10','2009-10-12'
insert tb select '车G','2009-10-2',NULL
insert tb select '车H','2009-10-10',NULL
insert tb select '车I',NULL,'2009-10-2'
insert tb select '车J',NULL,'2009-10-12'
insert tb select '车K','2009-10-5',NULL
insert tb select '车L',NULL,'2009-10-8'
select * from tb where (intime <='2009-10-9' and outtime >= '2009-10-5') or isnull(intime,outtime) between '2009-10-5' and '2009-10-9'
go
drop table tb
/*
c intime outtime
---------- ----------------------- -----------------------
车B 2009-10-04 00:00:00.000 2009-10-10 00:00:00.000
车D 2009-10-05 00:00:00.000 2009-10-08 00:00:00.000
车E 2009-10-06 00:00:00.000 2009-10-07 00:00:00.000
车K 2009-10-05 00:00:00.000 NULL
车L NULL 2009-10-08 00:00:00.000
*/