数据表
名称 年份 月份 日期
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
名称 年份 月份 日期
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
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
*/
名称 年份 月份 日期
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
a 2001 1 2001-1-10
a 2001 2 2001-2-10
a 2001 3 2001-3-10
a 2001 4 2001-4-10
a 2001 5 2001-5-10
a 2001 6 2001-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个月有数据的名称。
1、结果如下
a 2000 1-6
c 2000 1-8
结果2,如下
a 2000-2001
c 2000
from @t t
where (
select count(distinct(a.月份))
from @t a
where a.年份=t.年份 and a.名称=t.名称 and a.月份 between t.月份 and t.月份+5
) >5
group by 名称,年份
declare @t1 table(id int identity(1,1), 名称 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'
insert @t1 select * from @tselect t.名称,t.年份,cast(min(月份) as varchar(2))+'-'+cast(max(月份) as varchar(2)) from @t t join (select 名称,年份 from(select 名称,年份,s=id-月份 from @t1)a group by 名称,年份 having count(s)>5)b
on t.名称=b.名称 and t.年份=b.年份
group by t.名称,t.年份
(所影响的行数为 18 行)名称 年份
---- ----------- -----
a 2000 1-6
c 2000 1-8(所影响的行数为 2 行)