Mysql 排序依据(其中 menu_name = child_name)

Question

我有一些 mysql 查询来显示菜单。我想要 parent 和 child 订购的菜单。这是我的基本查询:

select * from (
select a.menu_id, a.menu_name, a.menu_parent parent, a.menu_parent parent_id, a.menu_order 
from menu_backend a
where a.menu_parent = '0'

union all

select b.menu_id, concat('--',b.menu_name) menu_name, c.menu_name parent, b.menu_parent parent_id, b.menu_order
from menu_backend b
left join menu_backend c on c.menu_id = b.menu_parent
where b.menu_parent != '0'
) alias

这就是结果:

http://xomf.com/vgjgc

我想准确地向父级订购子菜单，但我不知道该怎么做。谢谢

Answer 1

我没有对你的基本 SQL 进行太多修改，这样怎么样？

select *
  from (select a.menu_id,
               a.menu_name,
               a.menu_parent parent,
               a.menu_parent parent_id,
               a.menu_order,
               a.menu_id     sort_id
          from menu_backend a
         where a.menu_parent = '0'
        union all
        select b.menu_id,
               concat('--', b.menu_name) menu_name,
               c.menu_name parent,
               b.menu_parent parent_id,
               b.menu_order,
               b.menu_parent sort_id
          from menu_backend b
          left join menu_backend c
            on c.menu_id = b.menu_parent
         where b.menu_parent != '0') alias
 order by sort_id, parent_id, menu_order