LISTAGG、XMLAGG

LISTAGG(TO_CHAR(orp.id),';') WITHIN GROUP (ORDER BY orp.id) ORP_IDS,

XMLAGG(XMLPARSE(content to_char(orp.id) || ';' wellformed) ORDER BY orp.id).getclobval() ORP_IDS,

注：to_char保证分组之后列转行的字符类型，不然会有空格，当列转行字符串超过4000时，listagg会报错，可以使用xmlagg来替换，listagg结果：132205564;132205580、xmlagg结果：132205564;132205580;xmlagg字符串最后会多一个分隔符。

示例：

SELECT o.ACCOUNT_ID, XMLAGG(XMLPARSE(content to_char(orp.id) || ';' wellformed) ORDER BY orp.id).getclobval() ORP_IDS, count(orp.NUM_ID) orp_number,nvl(sum(fm.ACTUAL_FREIGHT),0) TOTAL_FREIGHT FROM ORDERS o LEFT JOIN ORDERS_RELATIONSHIP orp ON orp.ORDER_ID = o.ID LEFT JOIN FREIGHT_MANAGEMENT fm ON fm.ORDERS_RELATIONSHIP_NUM = orp.NUM_ID LEFT JOIN DISTRIBUTE d ON d.MOD_ID = orp.ID AND d.TYPE_FAY = '14' AND d.OPER_LOG LIKE '%货已发出' WHERE o.ORDER_STATE IN ('Order_Status_007','Order_Status_008','Order_Status_009') AND EXISTS (SELECT 1 FROM account a where a.id = o.ACCOUNT_ID and a.key_ACCOUNT_ID is not null) AND d.UPDATE_DATE >= to_date('2025-11-01 00:00:00','yyyy-MM-dd HH24:mi:ss') AND d.UPDATE_DATE < to_date('2025-12-01 00:00:00','yyyy-MM-dd HH24:mi:ss') GROUP BY o.ACCOUNT_ID