基于父子层级排序菜单数据：如何仅用int_order替代naturalsort

你当前用递归CTE实现了父子菜单的层级查询，但依赖naturalsort函数维持排序。由于菜单最大层级为4级，完全可以通过格式化int_order字段生成固定长度的排序路径，彻底替代自然排序函数，直接用字符串排序实现需求。

核心思路 #

把每个层级的int_order格式化为固定长度的字符串（比如2位，不足补0），然后递归拼接成完整的排序路径。这种路径字符串的字典序和你需要的自然层级排序完全一致，无需额外函数处理。

修改后的完整SQL代码 #

WITH recursive cte AS
(
-- 获取父级菜单
       SELECT 1 as level,
              vchr_menu_name::text,
              vchr_menu_caption,
              pk_bint_menu_id,
              vchr_menu_caption::text AS parent_menu,
              -- 父级排序路径：int_order转固定2位字符串，不足补0
              lpad(int_order::text, 2, '0') AS sort_path
       FROM   tbl_menu
       WHERE  int_menu_type=0
        and int_show=1
       UNION ALL
-- 获取子级菜单
       SELECT 
          cte.level + 1,
              -- 按层级生成缩进，贴合期望输出格式
              repeat(' ', cte.level * 4) || '-->' || mr.vchr_menu_name,
              mr.vchr_menu_caption,
              mr.pk_bint_menu_id,
              cte.parent_menu || '->' || mr.vchr_menu_caption,
              -- 拼接父级路径与当前格式化后的int_order
              cte.sort_path || lpad(mr.int_order::text, 2, '0')
       FROM   tbl_menu mr
       JOIN   cte
       ON     mr.bint_parent_id=cte.pk_bint_menu_id
       WHERE  mr.int_show=1 and mr.int_menu_type=2
       )
SELECT   *
FROM     cte
ORDER BY sort_path ASC;

关键细节说明 #

1. 固定长度格式化：用lpad(int_order::text, 2, '0')把每个层级的排序值转成2位字符串，比如1变成01，10保持10。如果某层级排序值超过99，直接把长度改成3即可（比如lpad(..., 3, '0')）。
2. 排序路径拼接：递归过程中把父级的sort_path和当前层级的格式化值拼接，最终得到类似0101（父菜单1的子菜单1）、0102（父菜单1的子菜单2）的字符串，直接按字符串排序就会得到正确的层级顺序。
3. 缩进优化：把原有的repeat('.', ...)改成repeat(' ', ...)，更贴合你期望的输出格式，层级缩进更清晰。

效果验证 #

最终查询结果会按你期望的层级顺序排列，输出格式和你给出的示例一致，且完全不需要依赖naturalsort函数，性能更优。

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