DB2缩进式BOM查询中SEQUENCENUMBER列排序异常求助

当前代码中SequenceNumber按字符串排序出现1、10、100...的错误顺序，原因是字符串排序会逐字符比较ASCII值，而非按数字大小层级排序。以下两种方案可在SQL层面直接解决：

方案一：固定长度补零生成可正确排序的字符串序号 #

通过将每个层级的序号补零到固定长度（如3位），确保字符串排序时数字大小顺序与层级逻辑一致。修改后的代码如下：

WITH RECURSIVE MyRecursiveBOM (
   ParentNBR,
   ParentITR,
   ChildNBR,
   ChildITR,
   BOMDepth,
   BOMPath,
   SequenceNumber
)
AS (
   -- 锚点：获取根节点的直接子节点，生成补零后的顶层序号
   SELECT
      bom.PINBR       AS ParentNBR,
      bom.PITR        AS ParentITR,
      bom.CINBR       AS ChildNBR,
      bom.CITR        AS ChildITR,
      1               AS BOMDepth,
      CAST(bom.PINBR AS VARCHAR(50)) AS BOMPath,
      -- 顶层序号补零到3位
      CAST(LPAD(ROW_NUMBER() OVER (ORDER BY bom.CINBR), 3, '0') AS VARCHAR(10)) AS SequenceNumber
   FROM AMFLIBD.PSTDTL bom
   WHERE bom.PINBR = '980-218130-106'
     AND bom.PITR  = 'B'

   UNION ALL

   -- 递归成员：生成子节点的补零序号
   SELECT
      c.PINBR                      AS ParentNBR,
      c.PITR                       AS ParentITR,
      c.CINBR                      AS ChildNBR,
      c.CITR                       AS ChildITR,
      p.BOMDepth + 1              AS BOMDepth,
      p.BOMPath || '.' || CAST(c.CINBR AS VARCHAR(50))  AS BOMPath,
      -- 子序号补零到3位后拼接
      p.SequenceNumber
         || '.'
         || CAST(LPAD(
               (
                 SELECT COUNT(*)
                 FROM AMFLIBD.PSTDTL x
                 WHERE x.PINBR = p.ChildNBR
                   AND x.PITR  = p.ChildITR
                   AND x.CINBR <= c.CINBR
                   AND x.CITR  = c.CITR
               ), 3, '0'
            ) AS VARCHAR(10))
         AS SequenceNumber
   FROM AMFLIBD.PSTDTL c
        JOIN MyRecursiveBOM p
          ON c.PINBR = p.ChildNBR
         AND c.PITR  = p.ChildITR
)

SELECT
  ParentNBR,
  ParentITR,
  ChildNBR,
  ChildITR,
  BOMDepth,
  BOMPath,
  SequenceNumber
FROM MyRecursiveBOM
ORDER BY SequenceNumber

说明：生成的SequenceNumber会变成001、002、010、003.001格式，字符串排序时会严格遵循数字层级顺序，同时保留了层级结构的可读性。可根据实际序号最大值调整补零长度（如序号超过999则改为4位）。

方案二：使用数组存储层级序号（更灵活） #

在递归CTE中新增一个数组列存储各层级的数字序号，利用DB2对数组的排序特性（按元素逐个比较数字大小）实现精准排序，同时保留原SequenceNumber的字符串格式。修改后的代码如下：

WITH RECURSIVE MyRecursiveBOM (
   ParentNBR,
   ParentITR,
   ChildNBR,
   ChildITR,
   BOMDepth,
   BOMPath,
   SequenceNumber,
   SequenceArray
)
AS (
   -- 锚点：初始化数组存储顶层序号
   SELECT
      bom.PINBR       AS ParentNBR,
      bom.PITR        AS ParentITR,
      bom.CINBR       AS ChildNBR,
      bom.CITR        AS ChildITR,
      1               AS BOMDepth,
      CAST(bom.PINBR AS VARCHAR(50)) AS BOMPath,
      CAST(ROW_NUMBER() OVER (ORDER BY bom.CINBR) AS VARCHAR(10)) AS SequenceNumber,
      ARRAY[ROW_NUMBER() OVER (ORDER BY bom.CINBR)] AS SequenceArray
   FROM AMFLIBD.PSTDTL bom
   WHERE bom.PINBR = '980-218130-106'
     AND bom.PITR  = 'B'

   UNION ALL

   -- 递归成员：将子序号追加到数组中
   SELECT
      c.PINBR                      AS ParentNBR,
      c.PITR                       AS ParentITR,
      c.CINBR                      AS ChildNBR,
      c.CITR                       AS ChildITR,
      p.BOMDepth + 1              AS BOMDepth,
      p.BOMPath || '.' || CAST(c.CINBR AS VARCHAR(50))  AS BOMPath,
      p.SequenceNumber
         || '.'
         || CAST(
               (
                 SELECT COUNT(*)
                 FROM AMFLIBD.PSTDTL x
                 WHERE x.PINBR = p.ChildNBR
                   AND x.PITR  = p.ChildITR
                   AND x.CINBR <= c.CINBR
                   AND x.CITR  = c.CITR
               ) AS VARCHAR(10)
            ) AS SequenceNumber,
      p.SequenceArray || ARRAY[
               (
                 SELECT COUNT(*)
                 FROM AMFLIBD.PSTDTL x
                 WHERE x.PINBR = p.ChildNBR
                   AND x.PITR  = p.ChildITR
                   AND x.CINBR <= c.CINBR
                   AND x.CITR  = c.CITR
               )
            ] AS SequenceArray
   FROM AMFLIBD.PSTDTL c
        JOIN MyRecursiveBOM p
          ON c.PINBR = p.ChildNBR
         AND c.PITR  = p.ChildITR
)

SELECT
  ParentNBR,
  ParentITR,
  ChildNBR,
  ChildITR,
  BOMDepth,
  BOMPath,
  SequenceNumber
FROM MyRecursiveBOM
ORDER BY SequenceArray

说明：数组SequenceArray会存储如[1]、[2]、[10]、[3,1]这样的层级数字，排序时DB2会自动按层级逐个比较数字大小，完全符合1、2、3、3.1、3.2、4...的需求。此方案无需调整补零长度，适配任意层级和序号范围。

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