日期:2014-05-18 浏览次数:20474 次
declare @t table(id int,date datetime,times int) insert @t select 10,'2009-01-01',3 insert @t select 11,'2009-01-02',1 insert @t select 12,'2009-01-03',3 insert @t select 13,'2009-01-04',3 insert @t select 14,'2009-01-05',2 insert @t select 15,'2009-01-06',0 insert @t select 16,'2009-01-07',0 insert @t select 17,'2009-01-08',1 insert @t select 18,'2009-01-09',0 insert @t select 19,'2009-01-10',0 insert @t select 20,'2009-01-11',2 insert @t select 21,'2009-01-12',0 insert @t select 22,'2009-01-13',0 insert @t select 23,'2009-01-14',1 insert @t select 24,'2009-01-15',3 insert @t select 25,'2009-01-16',3 insert @t select 26,'2009-01-18',0 insert @t select 27,'2009-01-19',0 declare @begdate datetime,@enddate datetime select @begdate='2009-01-02', @enddate='2009-01-04' select * from ( select * from @t a where not exists(select 1 from @t where id=a.id and id not in(select id from @t where date between @begdate and @enddate)) ) t where exists(select 1 from @t where id=t.id and date between @begdate and @enddate) id date times ----------- ----------------------- ----------- 11 2009-01-02 00:00:00.000 1 12 2009-01-03 00:00:00.000 3 13 2009-01-04 00:00:00.000 3 (3 行受影响)
------解决方案--------------------
if object_id ('A') is not null drop table A if OBJECT_ID('pro_c') is not null drop procedure pro_c go create table A (id int,[date] datetime ,times int) insert into A select 10,'2009-01-01',3 union all select 11,'2009-01-02',1 union all select 12,'2009-01-03',3 union all select 13,'2009-01-04',3 union all select 14,'2009-01-05',2 union all select 15,'2009-01-06',0 union all select 16,'2009-01-07',0 union all select 17,'2009-01-08',0 union all select 18,'2009-01-09',0 union all select 19,'2009-01-10',0 union all select 20,'2009-01-05',6 union all select 21,'2009-01-06',0 union all select 22,'2009-01-07',0 union all select 23,'2009-01-08',1 union all select 24,'2009-01-09',3 union all select 25,'2009-01-10',3 union all select 26,'2009-01-09',0 union all select 27,'2009-01-09',0 go create procedure pro_c (@da1 datetime,@da2 datetime) as set nocount on select * from A where times in (select times from A WHERE [DATE] in ('2009-01-02','2009-01-03','2009-01-04')) and [date] between @da1 and @da2 set nocount off go exec pro_c '2009-01-08','2009-01-10' (18 行受影响) id date times ----------- ----------------------- ----------- 23 2009-01-08 00:00:00.000 1 24 2009-01-09 00:00:00.000 3 25 2009-01-10 00:00:00.000 3