mysql - 父子级的MySQL顺序

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

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排序。我的问题是如何排序？

最佳答案

将您的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

关于mysql - 父子级的MySQL顺序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44932152/