SQL实现自关联父子表按指定顺序显示行（日期降序）

要实现父节点在前、子节点紧跟，且整体按日期降序的输出，核心是先将同一父子组归为一类，再按组排序并区分父子节点的先后。以下是具体的SQL实现方案：

思路拆解 #

• 确定分组依据：每个节点（无论父子）都归属到对应的父节点组，父节点的组ID是自身ID，子节点的组ID是父节点ID。
• 获取组排序基准：以父节点的日期作为整个组的排序依据，确保同一组的所有节点按父节点日期参与整体降序排序。
• 组内排序：组内先显示父节点，再显示子节点；子节点可按自身日期或ID排序（示例按日期降序）。

示例SQL代码 #

假设表名为your_table，以下是通用的SQL写法（适配多数关系型数据库）：

SELECT
    CASE WHEN t.ID2 IS NULL THEN '' ELSE CAST(t.ID1 AS VARCHAR) END AS `Col 1`,
    CASE WHEN t.ID2 IS NULL THEN CAST(t.ID1 AS VARCHAR) ELSE CAST(t.ID2 AS VARCHAR) END AS `Col 2`,
    t.Date
FROM (
    SELECT
        main.ID1,
        main.ID2,
        main.Date,
        -- 获取父节点的日期作为组排序基准
        COALESCE(parent.Date, main.Date) AS root_date,
        -- 标记是否为父节点，用于组内排序（父节点优先）
        CASE WHEN main.ID2 IS NULL THEN 0 ELSE 1 END AS is_child
    FROM your_table main
    LEFT JOIN your_table parent ON main.ID2 = parent.ID1
) t
ORDER BY
    t.root_date DESC, -- 整体按父节点日期降序
    t.is_child ASC,   -- 组内父节点在前
    t.Date DESC;      -- 子节点按自身日期降序（可替换为t.ID1排序）

代码说明 #

• 子查询部分：通过自连接获取每个节点对应的父节点日期，同时用is_child标记区分父子节点。
• 外层SELECT：用CASE语句生成符合要求的Col 1和Col 2，父节点的Col 1为空，Col 2填自身ID；子节点的Col 1填自身ID，Col 2填父节点ID。
• 排序逻辑：先按父节点日期降序排列所有组，组内父节点优先，子节点再按自身日期降序排列。

适配不同数据库的注意点 #

• 如果是MySQL，CAST可替换为CONVERT，或直接使用字段（因为ID通常是数字，输出时会自动转为字符串）。
• 如果是SQL Server，字符串转换可使用CONVERT(VARCHAR, ...)。

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