如何查询含parentID的MySQL分类表得到子随父排的层级导航树

MySQL 8.0+ 递归CTE方案（支持无限层级分类树） #

该方案可以适配任意深度的分类层级，不需要提前限定层级数量，是目前最通用的实现方式：

WITH RECURSIVE category_tree AS (
    -- 取出所有一级分类，构造排序用的路径字段
    SELECT 
        categoryID, 
        categoryName, 
        parentID,
        CAST(categoryID AS CHAR(200)) AS sort_path
    FROM category 
    WHERE parentID = 0
    UNION ALL
    -- 递归关联查询所有子分类，拼接父分类路径
    SELECT 
        c.categoryID, 
        c.categoryName, 
        c.parentID,
        CONCAT(ct.sort_path, ',', c.categoryID) AS sort_path
    FROM category c
    INNER JOIN category_tree ct ON c.parentID = ct.categoryID
)
SELECT categoryID, categoryName, parentID
FROM category_tree
ORDER BY sort_path;

核心逻辑是构造sort_path排序字段，子分类的路径会继承父分类的ID作为前缀，排序时天然就会实现父分类在前、对应子分类紧随其后的效果。

MySQL 5.x 兼容方案（适用于最多2级分类的场景） #

如果你的数据库版本不支持CTE，且业务中分类最多只有2级，可以使用更简单的排序逻辑实现需求：

SELECT 
    categoryID,
    categoryName,
    parentID
FROM category
ORDER BY 
    IF(parentID = 0, categoryID, parentID),
    parentID;

逻辑是将一级分类的自身ID、二级分类的父ID作为第一排序依据，保证同一个父分类的所有子分类都会紧跟在父分类下方。

内容的提问来源于stack exchange，提问作者D.B.