按照名称查出同一年中其中查出连续6个月有数据的名称-优快云博客

本文介绍了一种SQL查询方法，用于从包含名称、年份、月份和日期的数据表中找出特定名称在某一年内连续六个月有记录的情况。通过使用子查询和连接操作，可以有效地筛选出符合条件的数据范围。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据表
名称年份月份日期
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.youkuaiyun.com/t/20061122/10/5175928.html