业务场景:oracle查询数据时,需要把合同下所有订单的日期拼接显示在合同列表上。
关键函数:LISTAGG(字段名,分割字符) WITHIN GROUP (ORDER BY 排序的字段名 )
示例:
-- 查询合同下的订单日期汇总
select SUM(S.ACTUAL_MONEY) AS JSNUM, SUM(S.OUTSOURCE_MONEY) as outnum ,P.PJ_HT_ID,LISTAGG(TO_CHAR(S.ACTUAL_DATE,'yyyy-mm-dd'),',') WITHIN GROUP (ORDER BY S.ACTUAL_DATE ) AS DDD from table1 P inner JOIN table2 S ON P.ID=S.MX_ID
GROUP BY P.ID
效果图: