如何按日期排序自引用表并保持父/子节点分组？

问题 #

我有一个自引用表，其中parent_id是id的外键，表结构及数据如下：

+----+-----------+------------+
| id | parent_id | date       |
+----+-----------+------------+
| 1  | NULL      | 2023-04-10 |
| 2  | 4         | 2023-04-13 |
| 3  | 7         | 2023-05-20 |
| 4  | NULL      | 2023-04-13 |
| 5  | 1         | 2023-05-20 |
| 6  | 7         | 2023-03-14 |
| 7  | NULL      | 2023-04-15 |
| 8  | 1         | 2023-03-18 |
| 9  | 1         | 2023-04-19 |
+----+-----------+------------+

期望输出：父节点按日期排序，子节点紧跟对应父节点，子节点自身也按日期排序（示例为降序），格式如下：

+----+-----------+------------+
| id | parent_id | date       |
+----+-----------+------------+
| 7  | NULL      | 2023-04-15 |<--
| 3  | 7         | 2023-05-20 |
| 6  | 7         | 2023-03-14 |
| 4  | NULL      | 2023-04-13 |<--
| 2  | 4         | 2023-02-20 |
| 1  | NULL      | 2023-04-10 |<--
| 5  | 1         | 2023-05-20 |
| 9  | 1         | 2023-04-19 |
| 8  | 1         | 2023-03-18 |
+----+-----------+------------+

尝试过以下COALESCE排序语句，但无法满足父节点按日期排序的需求：

SELECT * FROM my_table ORDER BY COALESCE(parent_id, id), date

需要无需昂贵连接的解决方案，开发环境为H2数据库，生产环境为MS SQL，数据访问层使用JPA/Hibernate。

解决方案 #

SQL层面（兼容H2和MS SQL） #

核心思路是为每条记录生成分组排序键：父节点用自身date作为键，子节点用对应父节点的date作为键，以此保证父节点按日期排序且子节点紧跟分组，再补充节点类型和子节点内部排序规则。

SELECT t.*
FROM my_table t
ORDER BY 
    -- 父节点用自身date，子节点取对应父节点的date，降序排列父节点组
    (SELECT parent.date FROM my_table parent WHERE parent.id = COALESCE(t.parent_id, t.id)) DESC,
    -- 确保父节点在子节点之前
    CASE WHEN t.parent_id IS NULL THEN 0 ELSE 1 END,
    -- 子节点内部按date降序排序（匹配示例）
    t.date DESC;

逻辑说明

1. 第一个排序字段：通过主键匹配获取当前记录所属父节点的date，确保同组的父、子节点使用相同的排序基准，实现父节点按日期分组排序。
2. 第二个排序字段：用0/1标记区分父/子节点，强制父节点排在子节点前面。
3. 第三个排序字段：控制子节点内部的日期排序顺序。

JPA/Hibernate实现 #

JPQL写法

@Query("SELECT t FROM MyTable t " +
       "ORDER BY " +
       "(SELECT p.date FROM MyTable p WHERE p.id = COALESCE(t.parentId, t.id)) DESC, " +
       "CASE WHEN t.parentId IS NULL THEN 0 ELSE 1 END, " +
       "t.date DESC")
List<MyTable> findAllGroupedByParentOrderedByDate();

Criteria API写法

public List<MyTable> findAllGroupedAndOrdered() {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<MyTable> cq = cb.createQuery(MyTable.class);
    Root<MyTable> root = cq.from(MyTable.class);
    
    // 子查询获取父节点的date
    Subquery<LocalDate> parentDateSubquery = cq.subquery(LocalDate.class);
    Root<MyTable> parentRoot = parentDateSubquery.from(MyTable.class);
    parentDateSubquery.select(parentRoot.get("date"))
                     .where(cb.equal(parentRoot.get("id"), 
                                     cb.coalesce(root.get("parentId"), root.get("id"))));
    
    cq.orderBy(
        cb.desc(parentDateSubquery),
        cb.selectCase()
          .when(cb.isNull(root.get("parentId")), 0)
          .otherwise(1),
        cb.desc(root.get("date"))
    );
    
    return entityManager.createQuery(cq).getResultList();
}

性能说明 #

这里的子查询是通过主键单条匹配，主键和外键parent_id通常会有索引加持，查询开销极低，不属于“昂贵连接”范畴，在H2和MS SQL中均可高效执行。

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