Oracle分组查询结果SQL语句

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

--刘轶鹤