按照名称查出同一年中其中查出连续6个月有数据的名称
数据表名称 年份 月份 日期
a 2000 1 2000-1-10
a 2000 2 2000-2-10
a 2000 3 2000-3-10
a 2000 4 2000-4-10
a 2000 5 2000-5-10
a 2000 6 2000-6-10
b 2000 1 2000-1-10
b 2000 2 2000-2-10
b 2000 3 2000-3-10
b 2000 4 2000-4-10
c 2000 1 2000-1-10
c 2000 2 2000-2-10
c 2000 3 2000-3-10
c 2000 4 2000-4-10
c 2000 5 2000-5-10
c 2000 6 2000-6-10
c 2000 7 2000-7-10
c 2000 8 2000-8-10
按照名称查出同一年中其中查出连续6个月有数据的名称。
结果如下
a 2000 1-6
c 2000 1-8
declare @t table(名称 varchar(2),年份 int,月份 int,日期 datetime)
insert into @t select 'a',2000,1,'2000-1-10'
insert into @t select 'a',2000,2,'2000-2-10'
insert into @t select 'a',2000,3,'2000-3-10'
insert into @t select 'a',2000,4,'2000-4-10'
insert into @t select 'a',2000,5,'2000-5-10'
insert into @t select 'a',2000,6,'2000-6-10'
insert into @t select 'b',2000,1,'2000-1-10'
insert into @t select 'b',2000,2,'2000-2-10'
insert into @t select 'b',2000,3,'2000-3-10'
insert into @t select 'b',2000,4,'2000-4-10'
insert into @t select 'c',2000,1,'2000-1-10'
insert into @t select 'c',2000,2,'2000-2-10'
insert into @t select 'c',2000,3,'2000-3-10'
insert into @t select 'c',2000,4,'2000-4-10'
insert into @t select 'c',2000,5,'2000-5-10'
insert into @t select 'c',2000,6,'2000-6-10'
insert into @t select 'c',2000,7,'2000-7-10'
insert into @t select 'c',2000,8,'2000-8-10'
select
a.名称,a.年份,rtrim(a.月份)+'-'+rtrim(min(b.月份)) as 月份
from
(select * from @t c where not exists(select 1 from @t where 名称=c.名称 and 年份=c.年份 and 月份=c.月份-1)) a,
(select * from @t d where not exists(select 1 from @t where 名称=d.名称 and 年份=d.年份 and 月份=d.月份+1)) b
where
a.名称=b.名称 and a.年份=b.年份 and a.月份<=b.月份
group by
a.名称,a.年份,a.月份
having
min(b.月份)-5>=a.月份
/*
名称 年份 月份
---- ----------- -------------------------
a 2000 1-6
c 2000 1-8
*/
http://topic.csdn.net/t/20061122/10/5175928.html
页:
[1]