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

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

结果

我又想把每行的汇总数字进行汇总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')

结果如下

我想把下面的三行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

执行结果为

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