oracle中查询连续5个月都有值的记录

[gzluoy]

表结构如下
姓名 日期 ...
a 200101
a 200102
a 200103
a 200104
a 200105
a 200401
a 200403
a 200404
a 200405
c 200901
c 200902
b 200301
b 200304
b 200801
b 200802
b 200803
b 200804
b 200805
现在需要找出连续5个月都有记录的人员和开始日期及结束日期，例如a从200101-200105和b从200801-200805
....
....

[shiyiwan]

使用lag(date,1)和months_between()

[小灰狼W]

如果要求的是5个月而不是5个月以上，那就简单了
看你表，日期是以字符串储存的吗，如果是的话

select 姓名,日期||'-'||(日期+4) 开始日期及结束日期 from(
  select 姓名,日期,lead(日期,4)over(partition by 姓名 order by 日期)ld from tt )
where 日期=ld-4

是date类型的话，就进行下to_char(日期,'YYYYMM')转换

[小灰狼W]

如果你的意思是从原表中筛选连续5个月的记录的首尾两行
用

select 姓名,日期 from(
  select 姓名,日期,
         lag(日期,4)over(partition by 姓名 order by 日期)lg,
         lead(日期,4)over(partition by 姓名 order by 日期)ld  from tt 
         )
where 日期=lg+4 or 日期=ld-4
order by 姓名,日期

[gzluoy]

不好意思，是5个月以上

[小灰狼W]

[Quote=引用 4 楼 gzluoy 的回复:]
不好意思，是5个月以上
[/Quote]

select a.姓名,min(a.日期) 开始日期,max(b.日期)结束日期 from 
  (select tt.*,row_number()over(partition by 姓名 order by 日期)rn from tt) a,
  (select tt.*,row_number()over(partition by 姓名 order by 日期)rn from tt) b
where a.姓名=b.姓名 and
  a.日期<=b.日期-4
  and b.日期-a.日期=b.rn-a.rn
group by a.姓名  
--若每个姓名对应的连续5个月以上的日期只有一段，可以用这个代码

[gzluoy]

[Quote=引用 5 楼 wildwave 的回复:]
引用 4 楼 gzluoy 的回复:
不好意思，是5个月以上

SQL codeselect a.姓名,min(a.日期) 开始日期,max(b.日期)结束日期from
(select tt.*,row_number()over(partitionby 姓名orderby 日期)rnfrom tt) a,
(select tt.*,row_number()over(partitionby 姓名orderby 日期)rnfrom tt) bwhere a.姓名=b.姓名and
a.日期<=b.日期-4and b.日期-a.日期=b.rn-a.rngroupby a.姓名--若每个姓名对应的连续5个月以上的日期只有一段，可以用这个代码
[/Quote]每个人5个月以上的时间段可能不止一个

[shiyiwan]

SELECT 姓名, Start_HM, End_HM FROM(
         SELECT b.姓名, MIN (b.日期) Start_HM, MAX (b.日期) End_HM
         FROM (SELECT a.*, ADD_MONTHS(a.日期, ROWNUM * (-1)) cc
               FROM (SELECT *
                     FROM t
                     ORDER BY 姓名, 日期
                    ) a
              ) b
         GROUP BY b.姓名, b.cc) 
WHERE MONTHS_BETWEEN(End_HM,Start_HM) >= 5;

若日期 是 date类型直接使用，若是varchar2类型，使用to_date(日期,'yyyy-mm')处理下

[小灰狼W]

[Quote=引用 7 楼 shiyiwan 的回复:]
SQL codeSELECT 姓名, Start_HM, End_HMFROM(SELECT b.姓名,MIN (b.日期) Start_HM,MAX (b.日期) End_HMFROM (SELECT a.*, ADD_MONTHS(a.日期, ROWNUM* (-1)) ccFROM (SELECT*FROM tORDERBY 姓名, 日期
) a
) bGROUPBY b.姓名, b.cc)WHERE MONTHS_BETWEEN(End_HM,Start_HM)>=5;


若日期 是 date类型直接使用，若是varchar2类型，使用to_date(日期,'yyyy-mm')处理下
[/Quote]

写得漂亮
不过最后那个5应该改成4
楼主再自己order 一下就OK了

[gzluoy]

[Quote=引用 7 楼 shiyiwan 的回复:]
SQL codeSELECT 姓名, Start_HM, End_HMFROM(SELECT b.姓名,MIN (b.日期) Start_HM,MAX (b.日期) End_HMFROM (SELECT a.*, ADD_MONTHS(a.日期, ROWNUM* (-1)) ccFROM (SELECT*FROM tORDERBY 姓名, 日期
) a
) bGROUPBY b.姓名, b.cc)WHERE MONTHS_BETWEEN(End_HM,Start_HM)>=5;


若日期 是 date类型直接使用，若是varchar2类型，使用to_date(日期,'yyyy-mm')处理下
[/Quote]谢谢，现在试试

[gzluoy]

[Quote=引用 7 楼 shiyiwan 的回复:]
SQL codeSELECT 姓名, Start_HM, End_HMFROM(SELECT b.姓名,MIN (b.日期) Start_HM,MAX (b.日期) End_HMFROM (SELECT a.*, ADD_MONTHS(a.日期, ROWNUM* (-1)) ccFROM (SELECT*FROM tORDERBY 姓名, 日期
) a
) bGROUPBY b.姓名, b.cc)WHERE MONTHS_BETWEEN(End_HM,Start_HM)>=5;


若日期 是 date类型直接使用，若是varchar2类型，使用to_date(日期,'yyyy-mm')处理下
[/Quote]不知道为何提示b.日期：无效的标识符

[gzluoy]

SQL codeSELECT 姓名, Start_HM, End_HMFROM(SELECT b.姓名,MIN (b.日期) Start_HM,MAX (b.日期) End_HMFROM (SELECT a.*,a.日期, ADD_MONTHS(a.日期, ROWNUM* (-1)) ccFROM (SELECT*FROM tORDERBY 姓名, 日期
) a
) bGROUPBY b.姓名, b.cc)WHERE MONTHS_BETWEEN(End_HM,Start_HM)>=5;
知道了，原来少写了a.日期

[小灰狼W]

没有问题
你检查下字段名和表别名

[gzluoy]

[Quote=引用 12 楼 wildwave 的回复:]
没有问题
你检查下字段名和表别名
[/Quote]出现错误：年度值必须介于-4713 +9999，继续请教

[gzluoy]

[Quote=引用 8 楼 wildwave 的回复:]
引用 7 楼 shiyiwan 的回复:
SQL codeSELECT 姓名, Start_HM, End_HMFROM(SELECT b.姓名,MIN (b.日期) Start_HM,MAX (b.日期) End_HMFROM (SELECT a.*, ADD_MONTHS(a.日期, ROWNUM* (-1)) ccFROM (SELECT*FROM tORDERBY 姓名, 日期
                    ) a
              ) bGROUPBY b.姓名, b.cc)WHERE MONTHS_BETWEEN(End_HM,Start_HM)>=5;


若日期 是 date类型直接使用，若是varchar2类型，使用to_date(日期,'yyyy-mm')处理下


写得漂亮
不过最后那个5应该改成4
楼主再自己order 一下就OK了
[/Quote]出现错误：年度值必须介于-4713 +9999，继续请教