如何修改SQL Server递归查询以实现指定树形层级排序？

你的核心需求是让每个父节点下的子节点按ID降序排列，同时保持树形层级关系，并且整体线程按该线程的最大ID降序分组排序。原查询的问题在于递归CTE没有维护子节点的排序路径，导致子节点顺序不符合预期。

修改后的查询语句 #

WITH rcte AS (
    SELECT 
        t.id AS 'thread', 
        t.id, 
        t.parent_id, 
        t.name,
        -- 生成固定长度的逆序ID字符串，确保排序逻辑正确
        CAST(REPLACE(STR(999999 - t.id, 6), ' ', '0') AS VARCHAR(100)) AS sort_path
    FROM tree_table t 
    WHERE t.parent_id = 0

    UNION ALL

    SELECT 
        r.thread, 
        t.id, 
        t.parent_id, 
        t.name,
        -- 递归拼接父节点排序路径与当前节点的逆序ID字符串
        CAST(r.sort_path + REPLACE(STR(999999 - t.id, 6), ' ', '0') AS VARCHAR(100)) AS sort_path
    FROM tree_table t 
    JOIN rcte r ON r.id = t.parent_id
), sorted_threads AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY MAX(r.id) DESC) AS sort_number, 
        r.thread 
    FROM rcte r 
    GROUP BY r.thread
)
SELECT 
    st.sort_number, 
    r.id, 
    r.parent_id, 
    r.name 
FROM sorted_threads st 
JOIN rcte r ON r.thread = st.thread 
ORDER BY st.sort_number, r.sort_path;

关键修改说明 #

1. 新增sort_path字段：

   • 初始查询中，通过999999 - t.id将ID转换为逆序数值，再转为6位固定长度的字符串（补前导零）。这样原ID越大，转换后的值越小，升序排序时就等价于原ID的降序排列。
   • 递归阶段将父节点的sort_path与当前节点的转换值拼接，确保整个树形路径的排序逻辑符合子节点ID降序的要求。

2. 最终排序逻辑调整：
   原查询仅按线程分组的sort_number排序，现在新增r.sort_path作为第二排序条件，让每个线程内部的节点按照我们生成的排序路径排列，完美实现子节点ID降序的树形结构。

效果验证 #

执行该查询后，会完全匹配你期望的排序结果：

• 每个父节点下的子节点按ID从大到小排列（比如2Title下先显示ID9，再ID8、ID7）
• 树形层级关系保持完整（比如4Title→17→20→19的嵌套顺序）
• 整体线程仍按该线程的最大ID降序分组

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