listagg 聚合函数及去重

最新推荐文章于 2025-03-22 15:27:46 发布

crawlingsoldier

最新推荐文章于 2025-03-22 15:27:46 发布

阅读量5.6k

点赞数 1

本文链接：https://blog.youkuaiyun.com/qq_33888187/article/details/81333148

版权

SELECT listagg(FOIDS,',') WITHIN GROUP(ORDER BY FOIDS) as allFos , listagg(RCIDS,',') WITHIN GROUP(ORDER BY RCIDS) as allRcs
       FROM
       (SELECT regexp_replace(listagg(FO.ID,',') WITHIN GROUP(ORDER BY FO.ID), '([^,]+)(,\1)+', '\1') AS FOIDS,
               regexp_replace(listagg(RC.ID,',') WITHIN GROUP(ORDER BY RC.ID), '([^,]+)(,\1)+', '\1') AS RCIDS

               FROM
               TB_ERP_FO_DELIVERY FO JOIN TB_ERP_M_RECV RC
           ON FO.DRAW_NO = RC.DRAW_NO
               AND FO.NUM = RC.NUM
               AND FO.TECH_CONDITION = RC.TECH_CONDITION
               AND FO.HEAT_BATCH_NO = RC.HEAT_BATCH_NO
               AND FO.P_STATUS = 0
               AND RC.P_STATUS = 0

                   GROUP BY FO.DRAW_NO
                   HAVING COUNT(*) > 1
                   )