Mysql 按父子顺序

Question

我想显示按父级排序，然后按子级排序的查询结果。这是我的代码

SELECT * FROM 
mst_category AS parent 
LEFT JOIN mst_category AS child 
ON child.category_parent = parent.category_id
GROUP BY parent.category_id
ORDER BY parent.category_group, COALESCE(parent.category_parent, parent.category_id), parent.category_parent != 0, child.category_id

查询结果:

正如您所看到的，一些子项没有按父项 ID 排序。我的问题是如何排序？

Answer 1

将 order by 子句简化为您想要的排序顺序:

SELECT 
    parent.category_id as `parent_id`,
    parent.category_group as `parent_group`,
    child.category_id as `child_id`
    child.category_group as `child_group`
FROM mst_category AS parent 
LEFT JOIN mst_category AS child 
    ON child.category_parent = parent.category_id
GROUP BY parent.category_id
ORDER BY parent.category_group, child.category_group

对于 3 个级别:

SELECT 
    parent.category_id as `parent_id`,
    parent.category_group as `parent_group`,
    child.category_id as `child_id`
    child.category_group as `child_group`,
    child2.category_id as `child2_id`
    child2.category_group as `child2_group`
FROM mst_category AS parent 
LEFT JOIN mst_category AS child 
    ON child.category_parent = parent.category_id
LEFT JOIN mst_category AS child2
    ON child2.category_parent = child.category_id
WHERE parent.category_id = 0
GROUP BY parent.category_id, child.category_id, child2_category_id
ORDER BY parent.category_group, child.category_group, child2.category_group