SQL中使用LISTAGG对REFDES列进行升序排序的问题求助

刚接触SQL，查了好多资料都没解决这个问题。我写了一个查询，想要把REFDES列按升序排列，但实际结果里该列并没有按升序显示，相关代码如下：

WITH DistinctRefDes AS (
    SELECT bom.PINBR,
           bom.PITR,
           bom.CINBR,
           bom.CITR,
           REPLACE(TRIM(bom.REFDES), ',', '') AS REFDES
    FROM FILTD.PREFP110 bom
    WHERE TRIM(bom.REFDES) <> ''
    GROUP BY bom.PINBR,
             bom.PITR,
             bom.CINBR,
             bom.CITR,
             REPLACE(TRIM(bom.REFDES), ',', '')
),
OrderedRefDes AS (
    SELECT PINBR,
           PITR,
           CINBR,
           CITR,
           REFDES
    FROM DistinctRefDes
    ORDER BY REFDES
),
DistinctRefDes2 AS (
    SELECT PINBR,
           PITR,
           CINBR,
           CITR,
           LISTAGG(REFDES, ' ') WITHIN GROUP (ORDER BY REFDES) AS REFDES
    FROM OrderedRefDes
    GROUP BY PINBR,
             PITR,
             CINBR,
             CITR
)
SELECT bom.PINBR                            AS "PNumber",
       i2.ITDSC                             AS "PName",
       bom.PITR                             AS "PRev",
       bom.CINBR                            AS "CNumber",
       itmrva.ITDSC                         AS "CDesc",
       bom.CITR                             AS "CRev",
       bom.QTYPR                            AS "Qty",
       COALESCE(DistinctRefDes2.REFDES, '') AS REFDES,
       itembl.MOHTQ
FROM AMFLIBD.PSTDTL bom
LEFT JOIN DistinctRefDes2
   ON bom.PINBR = DistinctRefDes2.PINBR
   AND bom.PITR = DistinctRefDes2.PITR
   AND bom.CINBR = DistinctRefDes2.CINBR
   AND bom.CITR = DistinctRefDes2.CITR
LEFT JOIN AMFLIBD.ITMRVA itmrva
   ON bom.CINBR = itmrva.ITNBR AND bom.CITR = itmrva.ITRV
LEFT JOIN AMFLIBD.ITMRVA i2
   ON bom.PINBR = i2.ITNBR AND bom.PITR = i2.ITRV
LEFT JOIN AMFLIBD.ITEMBL itembl
   ON bom.CINBR = itembl.ITNBR AND itembl.HOUSE = 'MVS'
WHERE bom.PINBR = '980-218130-106'
AND bom.PITR = 'B'
ORDER BY bom.CINBR;

问题原因及解决方法 #

核心问题 #

你写的OrderedRefDes CTE里的ORDER BY REFDES是无效的——CTE内部的排序不会保留到后续的GROUP BY操作中，分组操作会直接打乱之前的排序结果。真正控制LISTAGG拼接顺序的是DistinctRefDes2里的WITHIN GROUP (ORDER BY REFDES)子句。

另外如果你的REFDES是字母+数字混合格式（比如A1、A10、A2），直接按字符串排序会得到A1, A10, A2的结果，这不符合我们直觉的自然排序，这也是常见的排序失效原因。

修改后的代码 #

WITH DistinctRefDes AS (
    SELECT bom.PINBR,
           bom.PITR,
           bom.CINBR,
           bom.CITR,
           REPLACE(TRIM(bom.REFDES), ',', '') AS REFDES
    FROM FILTD.PREFP110 bom
    WHERE TRIM(bom.REFDES) <> ''
    GROUP BY bom.PINBR,
             bom.PITR,
             bom.CINBR,
             bom.CITR,
             REPLACE(TRIM(bom.REFDES), ',', '')
),
DistinctRefDes2 AS (
    SELECT PINBR,
           PITR,
           CINBR,
           CITR,
           -- 针对混合格式做自然排序，纯字符串格式可直接用ORDER BY REFDES
           LISTAGG(REFDES, ' ') WITHIN GROUP (ORDER BY 
               REGEXP_SUBSTR(REFDES, '[A-Za-z]+'), -- 提取字母部分排序
               TO_NUMBER(REGEXP_SUBSTR(REFDES, '[0-9]+')) -- 提取数字转数值排序
           ) AS REFDES
    FROM DistinctRefDes
    GROUP BY PINBR,
             PITR,
             CINBR,
             CITR
)
SELECT bom.PINBR                            AS "PNumber",
       i2.ITDSC                             AS "PName",
       bom.PITR                             AS "PRev",
       bom.CINBR                            AS "CNumber",
       itmrva.ITDSC                         AS "CDesc",
       bom.CITR                             AS "CRev",
       bom.QTYPR                            AS "Qty",
       COALESCE(DistinctRefDes2.REFDES, '') AS REFDES,
       itembl.MOHTQ
FROM AMFLIBD.PSTDTL bom
LEFT JOIN DistinctRefDes2
   ON bom.PINBR = DistinctRefDes2.PINBR
   AND bom.PITR = DistinctRefDes2.PITR
   AND bom.CINBR = DistinctRefDes2.CINBR
   AND bom.CITR = DistinctRefDes2.CITR
LEFT JOIN AMFLIBD.ITMRVA itmrva
   ON bom.CINBR = itmrva.ITNBR AND bom.CITR = itmrva.ITRV
LEFT JOIN AMFLIBD.ITMRVA i2
   ON bom.PINBR = i2.ITNBR AND bom.PITR = i2.ITRV
LEFT JOIN AMFLIBD.ITEMBL itembl
   ON bom.CINBR = itembl.ITNBR AND itembl.HOUSE = 'MVS'
WHERE bom.PINBR = '980-218130-106'
AND bom.PITR = 'B'
ORDER BY bom.CINBR;

关键修改点 #

1. 删除了无用的OrderedRefDes CTE，避免无效排序操作。
2. 优化了LISTAGG的排序逻辑：
   • 如果REFDES是纯字符串（如全字母、固定格式编码），直接用ORDER BY REFDES即可。
   • 如果是字母+数字混合格式，通过正则拆分字母和数字部分，先按字母排序，再按数字的数值排序，实现自然升序。

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