SQL Server同表父子XML聚合：Timestamp降序排序失效问题求解

以下是修正后的SQL语句，可实现主消息和回复均按timestamp降序排列，并生成你需要的XML结构：

SELECT
    DISPLAY_ACCT,
    (
        SELECT
            m1.ID AS '@id',
            m1.timestamp AS '@timestamp',
            CAST(m1.message_body AS XML) AS 'message_body',
            (
                SELECT
                    r.ID AS '@id',
                    r.timestamp AS '@timestamp',
                    CAST(r.message_body AS XML) AS 'message_body'
                FROM Messages r
                WHERE r.parent_id = m1.ID
                ORDER BY r.timestamp DESC
                FOR XML PATH('reply'), TYPE
            ) AS 'replies'
        FROM Messages m1
        WHERE m1.parent_id IS NULL
          AND m1.DISPLAY_ACCT = m2.DISPLAY_ACCT
        ORDER BY m1.timestamp DESC
        FOR XML PATH('main_message'), ROOT('all_messages'), TYPE
    ) AS Message
FROM Messages m2
GROUP BY m2.DISPLAY_ACCT;

关键修正说明 #

1. 排序生效逻辑：
   • 主消息层级：在筛选主消息的子查询中直接添加ORDER BY m1.timestamp DESC，确保主消息按时间倒序排列在XML中。
   • 回复层级：在回复子查询中添加ORDER BY r.timestamp DESC，让每个主消息下的回复也按时间倒序生成。
2. 字段修正：
   • 替换了原SQL中不存在的NAME字段为DISPLAY_ACCT，修正了msg_body为表实际字段message_body。
3. XML结构优化：
   • 使用ROOT('all_messages')直接生成根节点，避免多余的类型转换嵌套。
   • 保留XML原生类型返回，无需多次CAST转换为字符串再转回XML，提升性能。
4. 分组逻辑：
   外层通过GROUP BY DISPLAY_ACCT确保每个账号仅返回一条记录，聚合其所有主消息及回复的XML结构。

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