oracle查询语句如何分组,Oracle分组查询结果SQL语句

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

LNUM

, TO_CHAR(MIN(DECODE(RANK, 1, T_BDATE,T_BDATE)),'YYYY-MM-DD')  AS T_BDATE

, TO_CHAR(MAX(DECODE( RANK, 2, T_EDATE,T_EDATE)),'YYYY-MM-DD')  AS T_EDATE

FROM

(

select LNUM

,T_NAME

,T_BDATE

,T_EDATE

,rank() over(partition by LNUM ORDER BY T_BDATE,T_EDATE) AS RANK

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

,T_NAME

,T_BDATE

,T_EDATE

from t_test

order by ROWNUM

)

)

GROUP BY LNUM