按照名称查出同一年中其中查出连续6个月有数据的名称

最新推荐文章于 2023-03-30 22:40:45 发布

htl258_Tony

最新推荐文章于 2023-03-30 22:40:45 发布

阅读量789

点赞数

分类专栏：精华答题收集文章标签： insert c table

精华答题收集专栏收录该内容

65 篇文章 0 订阅

订阅专栏

数据表
名称年份月份日期
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