如何对含order、parent_id的pages表排序，使子页面紧跟对应父页面

解决方案 #

方案1：仅支持两级层级（父子两级），兼容所有支持关联查询的数据库

通过自关联拿到父页面的排序值，将同父子页面与父页面分到同一排序组后再区分父子优先级：

SELECT p.`id`, p.`title`, p.`order`, p.`parent_id`
FROM pages p
LEFT JOIN pages parent_p ON p.parent_id = parent_p.id
ORDER BY 
  COALESCE(parent_p.`order`, p.`order`), -- 第一优先级：按父页面order分组，同父子页面与父页面共享同一排序值
  p.parent_id IS NOT NULL, -- 第二优先级：父页面排在所属子页面之前
  p.`order` -- 第三优先级：同组内按当前页面自身order排序

方案2：支持无限级层级嵌套，适用于MySQL 8.0+/PostgreSQL/SQL Server等支持递归CTE的数据库

通过递归查询生成层级排序路径，无论嵌套多少层都能保证父页面排在所有子页面之前：

WITH RECURSIVE page_hierarchy AS (
  -- 递归初始节点：所有顶级页面
  SELECT id, title, `order`, parent_id, CAST(`order` AS CHAR(200)) AS sort_path
  FROM pages
  WHERE parent_id IS NULL
  UNION ALL
  -- 递归遍历子页面，拼接层级排序路径
  SELECT child.id, child.title, child.`order`, child.parent_id, CONCAT(parent.sort_path, '/', child.`order`)
  FROM pages child
  INNER JOIN page_hierarchy parent ON child.parent_id = parent.id
)
SELECT id, title, `order`, parent_id
FROM page_hierarchy
ORDER BY sort_path;

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