企业单位 日期AAA 2008-01-29
AAA 2008-02-29
AAA 2008-03-29
AAA 2008-04-29
BBB 2008-05-29
BBB 2008-06-29
BBB 2008-07-29
BBB 2008-08-29
CCC 2008-09-29
CCC 2008-10-29
CCC 2008-11-29
CCC 2008-12-29
AAA 2009-01-29
DDD 2009-02-29
DDD 2009-03-29
DDD 2009-04-29
EEE 2009-05-29
EEE 2009-06-29
EEE 2009-07-29
EEE 2009-08-29
BBB 2009-09-29
BBB 2009-10-29
BBB 2009-11-29
BBB 2009-12-29 想要的结果:
单位 起始年月 结束年月 月数
---------------------------------
AAA 2008-01 2008-04 4
BBB 2008-05 2008-08 4
CCC 2008-09 2008-12 4
AAA 2009-01 2009-01 1
DDD 2009-02 2009-04 3
EEE 2009-05 2009-08 4
BBB 2009-09 2009-12 4请大家多多指教!
AAA 2008-02-29
AAA 2008-03-29
AAA 2008-04-29
BBB 2008-05-29
BBB 2008-06-29
BBB 2008-07-29
BBB 2008-08-29
CCC 2008-09-29
CCC 2008-10-29
CCC 2008-11-29
CCC 2008-12-29
AAA 2009-01-29
DDD 2009-02-29
DDD 2009-03-29
DDD 2009-04-29
EEE 2009-05-29
EEE 2009-06-29
EEE 2009-07-29
EEE 2009-08-29
BBB 2009-09-29
BBB 2009-10-29
BBB 2009-11-29
BBB 2009-12-29 想要的结果:
单位 起始年月 结束年月 月数
---------------------------------
AAA 2008-01 2008-04 4
BBB 2008-05 2008-08 4
CCC 2008-09 2008-12 4
AAA 2009-01 2009-01 1
DDD 2009-02 2009-04 3
EEE 2009-05 2009-08 4
BBB 2009-09 2009-12 4请大家多多指教!
BBB 2008-05 2008-08 4
CCC 2008-09 2008-12 4
AAA 2009-01 2009-01 1连续的才计算,还不能group
只能存储过程里循环,每次和上一条比较了
如果不相同,上一条就是结束,本条就是下一组的开始
月数可以用Months_between函数
to_char(max(日期),'yyyymm') 结束年月 ,
months_between(min(日期),max(日期)) 月数
from a
group by 单位
to_char(max(日期),'yyyymm') 结束年月 ,
months_between(min(日期),max(日期)) 月数
from a
group by 单位,to_char(日期,'yyyy')