一个表，有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