一个表,有ID,months(月份) 有些months有值,有些没有, 怎样可以查出months连续有3 月有值的记录呢?
表结构如下 id month ... 100 200201
100 200205
100 200206
100 200207
100 200210
... 101 200201
101 200202
101 200205
101 200206
.... 102 200201
... 如上表中, 100 200205
100 200206
100 200207
就是我要的数据 因为有连续三个月的记录 怎样写呢?
select *
from (select count(*)
over (PARTITION
by id,DIFFDATE)
diffcount,id,month from
( SELECT id,month
,month-ROW_NUMBER() OVER(ORDER BY id,month)
DIFFDATE FROM t
) )where diffcount = 3
附:一个表,有ID,months(月份) 有些months有值,有些没有, 怎样可以查出months连续有3 月有值的记录呢?
create table tabname (id int ,month int);
insert into tabname values(1,200901);
insert into tabname values(1,200902);
insert into tabname values(1,200903);
insert into tabname values(2,200901);
insert into tabname values(2,200902);
insert into tabname values(2,200904);
insert into tabname values(2,200905);
--不能跨年
select distinct
id from (select count(*) over (PARTITION by id,DIFFDATE) diffcount,id,month from ( SELECT id,month ,month-ROW_NUMBER() OVER(ORDER BY id,month) DIFFDATE FROM tabname ) )where diffcount = 3 ;
create table tabname1 (id int ,month DATE);
insert into tabname1
values(1,to_date('2009-01-01','yyyy-mm-dd'));
insert into tabname1
values(1,to_date('2009-02-01','yyyy-mm-dd'));
insert into tabname1
values(1,to_date('2009-03-01','yyyy-mm-dd'));
insert into tabname1
values(2,to_date('2009-11-01','yyyy-mm-dd'));
insert into tabname1
values(2,to_date('2009-12-01','yyyy-mm-dd'));
insert into tabname1
values(2,to_date('2010-01-01','yyyy-mm-dd'));
insert into tabname1
values(3,to_date('2009-05-01','yyyy-mm-dd'));
insert into tabname1
values(3,to_date('2009-06-01','yyyy-mm-dd'));
insert into tabname1
values(3,to_date('2009-08-01','yyyy-mm-dd'));
insert into tabname1
values(3,to_date('2009-09-01','yyyy-mm-dd'));
select * from tabname1 order by id, month;
--支持跨年,而且可以直接利用日期字段,不用外加月份序列. select distinct id from (select count(*) over (PARTITION by DIFFDATE) diffcount,id,month from ( SELECT id,month ,add_months(month ,-ROW_NUMBER() OVER(ORDER BY id,month) ) DIFFDATE FROM tabname1 ) )where diffcount = 3