Oracle SQL按Confirmation#降序合并Work_Request_Note的Notes字段

要实现同一WORK_REQUEST_NUMBER（对应Confirmation#）下的Notes按work_request_note.create_date降序拼接，只需在LISTAGG函数的WITHIN GROUP子句中指定排序规则为create_date DESC即可。

修改前的典型SQL示例（升序拼接） #

SELECT 
    wr.WORK_REQUEST_NUMBER AS "Confirmation#",
    LISTAGG(wrn.NOTE, ' ') WITHIN GROUP (ORDER BY wrn.create_date) AS combined_notes
FROM Work_Request wr
JOIN Work_Request_Note wrn ON wr.WORK_REQUEST_NUMBER = wrn.WORK_REQUEST_NUMBER
GROUP BY wr.WORK_REQUEST_NUMBER;

修改后的SQL（降序拼接） #

SELECT 
    wr.WORK_REQUEST_NUMBER AS "Confirmation#",
    LISTAGG(wrn.NOTE, ' ') WITHIN GROUP (ORDER BY wrn.create_date DESC) AS combined_notes
FROM Work_Request wr
JOIN Work_Request_Note wrn ON wr.WORK_REQUEST_NUMBER = wrn.WORK_REQUEST_NUMBER
GROUP BY wr.WORK_REQUEST_NUMBER;

效果对比 #

• 修改前结果（升序）：

  "10/04/2023 13:27:55 Work Request was assigned to Mary Bang MBCorpCB1 10/04/2023 13:27:57 Work Request was completed by Mary Bang MBCorpCB1 10/11/2023 08:58:59 Work Request was reassigned to Mona Lash f2326COcorp 10/11/2023 08:59:01 Work Request was reset to new by Mona Lash f2326COcorp "

• 修改后结果（降序，符合期望）：

  "10/11/2023 08:59:01 Work Request was reset to new by Mona Lash f2326COcorp 10/11/2023 08:58:59 Work Request was reassigned to Mona Lash f2326COcorp 10/04/2023 13:27:57 Work Request was completed by Mary Bang MBCorpCB1 10/04/2023 13:27:55 Work Request was assigned to Mary Bang MBCorpCB1"

核心改动就是在LISTAGG的排序参数里把create_date调整为create_date DESC，让最新的Note排在拼接结果的最前面。

内容的提问来源于stack exchange，提问作者Vimal