php - 父子mysql

Question

假设我有一个这样的表:

=================================
| ID |  Parent_ID | Page_Name   |
=================================
| 1  |  NULL      |  Home       |
| 2  |  NULL      |  Services   |
| 3  |  2         |  Baking     |
| 4  |  3         |  Cakes      |
| 5  |  3         |  Bread      |
| 6  |  5         |  Flat Bread |
---------------------------------

我怎样才能以这种格式对结果进行实际排序？ IE。由父-> 子-> 子子命令，在此基础上我只需要说最多 5 个级别？我研究了“嵌套集模型”，但它似乎对我的要求来说太复杂了。我不确定的是我是否真正理解了可用于显示上述结果的 SQL 查询，或者在这种情况下我是否应该使用服务器端语言(如 PHP)来为我执行此操作？

Answer 1

编辑

解决戈登笔记的工作示例

查询计算节点路径，因为你有固定的最大树深度，并按它排序。

SQL Fiddle

MySQL 5.5.30 架构设置:

create table mytable(id int, parent_id int, name varchar(100));

insert mytable(id, parent_id, name)
values (1, null, 'Home'),
(2, null, 'Services'),
(3, 2, 'Baking'),
(4, 3, 'Cakes'),
(5, 3, 'Bread'),
(6, 5, 'Flat Bread'),
(7, 1, 'Something');

查询 1:

select t0.*,
  concat(
      case coalesce(t4.Parent_ID, 0) 
        when 0 then ''
        else concat(cast(t4.Parent_ID as char), '\\')
      end,
      case coalesce(t3.Parent_ID, 0) 
        when 0 then ''
        else concat(cast(t3.Parent_ID as char), '\\')
      end,
      case coalesce(t2.Parent_ID, 0) 
        when 0 then ''
        else concat(cast(t2.Parent_ID as char), '\\')
      end,
      case coalesce(t1.Parent_ID, 0) 
        when 0 then ''
        else concat(cast(t1.Parent_ID as char), '\\')
      end,
      case coalesce(t0.Parent_ID, 0) 
        when 0 then ''
        else concat(cast(t0.Parent_ID as char), '\\')
      end,
      cast(t0.id as char)
    ) as path
from mytable t0 
    left join mytable t1 on t0.Parent_ID = t1.Id
    left join mytable t2 on t1.Parent_ID = t2.Id
    left join mytable t3 on t2.Parent_ID = t3.Id
    left join mytable t4 on t3.Parent_ID = t4.Id
order by 
  concat(
      case coalesce(t4.Parent_ID, 0) 
        when 0 then ''
        else concat(cast(t4.Parent_ID as char), '\\')
      end,
      case coalesce(t3.Parent_ID, 0) 
        when 0 then ''
        else concat(cast(t3.Parent_ID as char), '\\')
      end,
      case coalesce(t2.Parent_ID, 0) 
        when 0 then ''
        else concat(cast(t2.Parent_ID as char), '\\')
      end,
      case coalesce(t1.Parent_ID, 0) 
        when 0 then ''
        else concat(cast(t1.Parent_ID as char), '\\')
      end,
      case coalesce(t0.Parent_ID, 0) 
        when 0 then ''
        else concat(cast(t0.Parent_ID as char), '\\')
      end,
      cast(t0.id as char)
    )

Results :

| ID | PARENT_ID |       NAME |    PATH |
-----------------------------------------
|  1 |    (null) |       Home |       1 |
|  7 |         1 |  Something |     1\7 |
|  2 |    (null) |   Services |       2 |
|  3 |         2 |     Baking |     2\3 |
|  4 |         3 |      Cakes |   2\3\4 |
|  5 |         3 |      Bread |   2\3\5 |
|  6 |         5 | Flat Bread | 2\3\5\6 |