oracle的 listagg() WITHIN GROUP () 根据字段多行字段合并

女爷i 2023-07-09 02:10 10阅读 0赞

[https://blog.csdn.net/qq\_32448349/article/details/104531537][https_blog.csdn.net_qq_32448349_article_details_104531537]

基础用法：LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)，就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来

LISTAGG()其实可以把它当作SUM()函数来使用或者理解

针对上一个的查询SQL

SELECT * FROM 
    (
    SELECT TO_CHAR(CREATE_TIME,'yyyy-MM') CREATE_TIME,
    PROJECT_TYPE,COUNT(*) SUM,
    NULL COUNT_NUM from  
    PROJECT_DECLARATION
    GROUP BY TO_CHAR(CREATE_TIME,'yyyy-MM'),PROJECT_TYPE
    )
    PIVOT (SUM(SUM) NUM FOR PROJECT_TYPE IN ('projectType1' TYPE1,  'projectType2' TYPE2,'projectType3' TYPE3))
    ORDER BY CREATE_TIME

结果

![2020022710520077.png][]

我又想把每行的汇总数字进行汇总count，于是我想到了union all，在上面的基础上添加一个关联查询 对月分组统计，SQL如下

SELECT * FROM (SELECT * FROM 
    (
    SELECT TO_CHAR(CREATE_TIME,'yyyy-MM') CREATE_TIME,
    PROJECT_TYPE,COUNT(*) SUM,
    NULL COUNT_NUM from  
    PROJECT_DECLARATION
    GROUP BY TO_CHAR(CREATE_TIME,'yyyy-MM'),PROJECT_TYPE
    )
    PIVOT (SUM(SUM) NUM FOR PROJECT_TYPE IN ('projectType1' TYPE1,  'projectType2' TYPE2,'projectType3' TYPE3))
    ORDER BY CREATE_TIME)
    --增加的
    UNION ALL
    select TO_CHAR(CREATE_TIME,'yyyy-MM') CREATE_TIME,
    COUNT(*) COUNT_NUM,
    NULL TYPE1_SUM,
    NULL TYPE2_SUM,
    NULL TYPE3_SUM 
    FROM  PROJECT_DECLARATION
    GROUP BY  TO_CHAR(CREATE_TIME,'yyyy-MM')

结果如下

![20200227105548268.png][]

我想把下面的三行COUNT\_NUM 根据CREATE\_TIME 合并列 就用到了我们今天的函数

listagg() WITHIN GROUP () SQL如下

--增加的
    SELECT CREATE_TIME,  
         LISTAGG( to_char(COUNT_NUM)) WITHIN GROUP(ORDER BY COUNT_NUM) AS COUNT_NUM,
    		 LISTAGG( to_char(TYPE1_NUM)) WITHIN GROUP(ORDER BY TYPE1_NUM) AS TYPE1_NUM,  
    		 LISTAGG( to_char(TYPE2_NUM)) WITHIN GROUP(ORDER BY TYPE2_NUM) AS TYPE2_NUM , 
    		 LISTAGG( to_char(TYPE3_NUM)) WITHIN GROUP(ORDER BY TYPE3_NUM) AS TYPE3_NUM  
    FROM 
    (
    --增加的
    
    SELECT * FROM (SELECT * FROM 
    (
    SELECT TO_CHAR(CREATE_TIME,'yyyy-MM') CREATE_TIME,
    PROJECT_TYPE,COUNT(*) SUM,
    NULL COUNT_NUM from  
    PROJECT_DECLARATION
    GROUP BY TO_CHAR(CREATE_TIME,'yyyy-MM'),PROJECT_TYPE
    )
    PIVOT (SUM(SUM) NUM FOR PROJECT_TYPE IN ('projectType1' TYPE1,  'projectType2' TYPE2,'projectType3' TYPE3))
    ORDER BY CREATE_TIME)
    
    UNION ALL
    select TO_CHAR(CREATE_TIME,'yyyy-MM') CREATE_TIME,
    COUNT(*) COUNT_NUM,
    NULL TYPE1_SUM,
    NULL TYPE2_SUM,
    NULL TYPE3_SUM 
    FROM  PROJECT_DECLARATION
    GROUP BY  TO_CHAR(CREATE_TIME,'yyyy-MM')
    
    --增加的
    ) 
    GROUP BY CREATE_TIME ORDER BY CREATE_TIME DESC

执行结果为

![20200227110134525.png][]

正是我们想要的，完美！！！！！

[https_blog.csdn.net_qq_32448349_article_details_104531537]: https://blog.csdn.net/qq_32448349/article/details/104531537
[2020022710520077.png]: https://img-blog.csdnimg.cn/2020022710520077.png
[20200227105548268.png]: https://img-blog.csdnimg.cn/20200227105548268.png
[20200227110134525.png]: https://img-blog.csdnimg.cn/20200227110134525.png