Oracle LISTAGG 函数实现多行合并为一行

 oracle11g以后不要使用wmsys.wm_concat，改用LISTAGG 函数，用于多行合并为一行，执行效率更优

调整前：


        select 
        q.EUTRANCELLTDD_UK,
        q.CellReselPriority,
        q.QrxLevMinReCh,
        q.ThreshXHigh + r.qRxLevMin  as ThreshXHigh ,
        q.ThreshXLow + r.qRxLevMin   as ThreshXLow ,
        q.TReselEutran 
        from 
        (select
        EUTRANCELLTDD_UK,
        wmsys.wm_concat(distinct cellReselectionPriority)     as        CellReselPriority,
        wmsys.wm_concat(distinctqRxLevMin*2)                as        QrxLevMinReCh,
        max(threshXHigh)*2                                   as        ThreshXHigh,
        max(threshXLow)*2                                    as  ThreshXLow,
        wmsys.wm_concat(distincttReselectionEutra)          as        TReselEutran 
        from
        cm.O_L_E_EUtranFreqRelation
        where start_time = to_date('2018-06-17','yyyy-mm-dd')
        group by  EUTRANCELLTDD_UK)         q,
        cm.O_L_E_EutranCellTdd r
        where
        q.EUTRANCELLTDD_UK = r.EUTRANCELLTDD_UK and r.start_time =to_date('2018-06-17','yyyy-mm-dd')

语句：

select EUTRANCELLTDD_UK,wmsys.wm_concat(distinct qRxLevMin*2)  from cm.O_L_E_EUTRANFREQRELATION  

  2  where  start_time = to_date('2018-06-17','yyyy-mm-dd')

  3  group by EUTRANCELLTDD_UK

执行计划

 
调整后


        select
        a.EUTRANCELLTDD_UK,
        b.CellReselPriority,
        c.QrxLevMinReCh,
        d.ThreshXHigh + r.qRxLevMin  as ThreshXHigh ,
        d.ThreshXLow + r.qRxLevMin   as ThreshXLow ,
        a.TReselEutran  
         from 
         (selectEUTRANCELLTDD_UK, 
        LISTAGG( tReselectionEutra, ',') WITHIN GROUP(order by tReselectionEutra) asTReselEutran from (
        select  distinct EUTRANCELLTDD_UK,tReselectionEutra  fromcm.O_L_E_EUtranFreqRelation
        where start_time = to_date('2018-06-17','yyyy-mm-dd')
        ) group by EUTRANCELLTDD_UK ) a ,


        (select EUTRANCELLTDD_UK, 
        LISTAGG( cellReselectionPriority, ',') WITHIN GROUP(order bycellReselectionPriority) as CellReselPriority from (
        select  distinct EUTRANCELLTDD_UK,cellReselectionPriority  fromcm.O_L_E_EUtranFreqRelation
        where start_time = to_date('2018-06-17','yyyy-mm-dd')
        ) group by EUTRANCELLTDD_UK ) b  , 
        (select EUTRANCELLTDD_UK, 
        LISTAGG( qRxLevMin*2, ',') WITHIN GROUP(order by qRxLevMin) as QrxLevMinReChfrom (
        select  distinct EUTRANCELLTDD_UK,qRxLevMin  fromcm.O_L_E_EUtranFreqRelation
        where start_time = to_date('2018-06-17','yyyy-mm-dd')
        ) group by EUTRANCELLTDD_UK ) c    , 
        (select distinct EUTRANCELLTDD_UK,max(threshXHigh)*2  as ThreshXHigh,
        max(threshXLow)*2  as  ThreshXLow
         from cm.O_L_E_EUtranFreqRelation
        where start_time = to_date('2018-06-17','yyyy-mm-dd')
        group by EUTRANCELLTDD_UK
        ) d ,
        cm.O_L_E_EutranCellTdd r 
        where
        a.EUTRANCELLTDD_UK = r.EUTRANCELLTDD_UK and
         b.EUTRANCELLTDD_UK= r.EUTRANCELLTDD_UK and
         c.EUTRANCELLTDD_UK= r.EUTRANCELLTDD_UK and
         d.EUTRANCELLTDD_UK= r.EUTRANCELLTDD_UK and
         r.start_time=to_date('2018-06-17','yyyy-mm-dd')


 
验证：


        select
        q.EUTRANCELLTDD_UK,
        q.CellReselPriority,
        q.QrxLevMinReCh,
        q.ThreshXHigh + r.qRxLevMin  as ThreshXHigh ,
        q.ThreshXLow + r.qRxLevMin   as ThreshXLow ,
        q.TReselEutran
        from 
        (select
        EUTRANCELLTDD_UK,
        wmsys.wm_concat(distinct cellReselectionPriority)     as        CellReselPriority,
        wmsys.wm_concat(distinctqRxLevMin*2)                as        QrxLevMinReCh,
        max(threshXHigh)*2                                   as        ThreshXHigh,
        max(threshXLow)*2                                    as  ThreshXLow,
        wmsys.wm_concat(distincttReselectionEutra)          as        TReselEutran
        from
        cm.O_L_E_EUtranFreqRelation
       where start_time = to_date('2018-06-17','yyyy-mm-dd') and EUTRANCELLTDD_UK='oss1-L27253_D-1-L27253_D1'
        group by  EUTRANCELLTDD_UK) q,
        cm.O_L_E_EutranCellTdd r
        where
        q.EUTRANCELLTDD_UK = r.EUTRANCELLTDD_UK and r.start_time =to_date('2018-06-17','yyyy-mm-dd')
        and r.EUTRANCELLTDD_UK='oss1-L27253_D-1-L27253_D1'
 
调整后验证语句
        select
        a.EUTRANCELLTDD_UK,
        b.CellReselPriority,
        c.QrxLevMinReCh,
        d.ThreshXHigh + r.qRxLevMin  as ThreshXHigh ,
        d.ThreshXLow + r.qRxLevMin   as ThreshXLow,
        a.TReselEutran 
         from
        (select EUTRANCELLTDD_UK,
        LISTAGG( tReselectionEutra, ',') WITHIN GROUP(order by tReselectionEutra)as TReselEutran from (
        select  distinct EUTRANCELLTDD_UK,tReselectionEutra from cm.O_L_E_EUtranFreqRelation
        where start_time = to_date('2018-06-17','yyyy-mm-dd') and EUTRANCELLTDD_UK='oss1-L27253_D-1-L27253_D1'
        ) group by EUTRANCELLTDD_UK ) a,
        
        (select EUTRANCELLTDD_UK,
        LISTAGG( cellReselectionPriority, ',') WITHIN GROUP(order by cellReselectionPriority)as CellReselPriority from (
        select  distinct EUTRANCELLTDD_UK,cellReselectionPriority from cm.O_L_E_EUtranFreqRelation
        where start_time = to_date('2018-06-17','yyyy-mm-dd') and EUTRANCELLTDD_UK='oss1-L27253_D-1-L27253_D1'
        ) group by EUTRANCELLTDD_UK ) b,
        
        (select EUTRANCELLTDD_UK,
        LISTAGG( qRxLevMin*2, ',') WITHIN GROUP(order by qRxLevMin)as QrxLevMinReCh from (
        select  distinct EUTRANCELLTDD_UK,qRxLevMin from cm.O_L_E_EUtranFreqRelation
        where start_time = to_date('2018-06-17','yyyy-mm-dd') and EUTRANCELLTDD_UK='oss1-L27253_D-1-L27253_D1'
        ) group by EUTRANCELLTDD_UK ) c,
        
        (select distinct EUTRANCELLTDD_UK,max(threshXHigh)*2  as  ThreshXHigh,
        max(threshXLow)*2  as  ThreshXLow
         from cm.O_L_E_EUtranFreqRelation
        where start_time = to_date('2018-06-17','yyyy-mm-dd') and EUTRANCELLTDD_UK='oss1-L27253_D-1-L27253_D1'
        group by EUTRANCELLTDD_UK
        ) d , 
        cm.O_L_E_EutranCellTdd r
        where
        a.EUTRANCELLTDD_UK = r.EUTRANCELLTDD_UK and
         b.EUTRANCELLTDD_UK= r.EUTRANCELLTDD_UK and
         c.EUTRANCELLTDD_UK= r.EUTRANCELLTDD_UK and
         d.EUTRANCELLTDD_UK= r.EUTRANCELLTDD_UK and
         r.start_time=to_date('2018-06-17','yyyy-mm-dd') and r.EUTRANCELLTDD_UK='oss1-L27253_D-1-L27253_D1'