CREATE TABLE t_test
AS
SELECT '名称1' as t_name,to_date('2011-11-18','YYYY-MM-DD') as t_bdate,to_date('2012-10-01','YYYY-MM-DD') as t_edate,'Y' as t_flag from dual
union all
SELECT '名称2' as t_name,to_date('2012-10-02','YYYY-MM-DD') as t_bdate,to_date('2013-10-01','YYYY-MM-DD') as t_edate, NULL as t_flag from dual
union all
SELECT '名称3' as t_name,to_date('2013-10-02','YYYY-MM-DD') as t_bdate,to_date('2014-10-01','YYYY-MM-DD') as t_edate,'Y' as t_flag from dual
union all
SELECT '名称4' as t_name,to_date('2014-10-02','YYYY-MM-DD') as t_bdate,to_date('2015-10-31','YYYY-MM-DD') as t_edate,'Y' as t_flag from dual
;
SELECT
FROM
(
select LNUM
from
(
select decode(T_FLAG,'Y',row_number() over(partition by T_FLAG ORDER BY ROWNUM ),row_number() over(partition by T_FLAG ORDER BY ROWNUM )+1) as lnum
from t_test
order by ROWNUM
)
)
GROUP BY LNUM