父子mysql [英] Parent child mysql

查看:78
本文介绍了父子mysql的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

说我有一个这样的表:

=================================
| 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这样的服务器端语言来帮我吗?

How can I go about actually ordering the results in this format? I.e. Ordered by the Parent -> Child -> Sub Child, on the basis I would only ever require say a maximum of 5 levels? I have looked into the "Nested Set Model" but it seems too complex for my requirements. What I am unsure about is really understanding a SQL query I can use to display my results like above, or in this situation should I be using a server side language like PHP to do this for me?

推荐答案

您可以尝试以下操作:

select t.*,
       (case when t4.parent_id is not NULL then 5
             when t4.id is not null then 4
             when t3.id is not null then 3
             when t2.id is not null then 2
             when t1.id is not null then 1
             else 0
        end) as level
from t left outer join
     t t1
     on t.parent_id = t1.id left outer join
     t t2
     on t1.parent_id = t2.id left outer join
     t t3
     on t2.parent_id = t3.id left outer join
     t t4
     on t3.parent_id = t4.id
order by coalesce(t4.parent_id, t4.id, t3.id, t2.id, t1.id, t.id),
         coalesce(t4.id, t3.id, t2.id, t1.id, t.id),
         coalesce(t3.id, t2.id, t1.id, t.id),
         coalesce(t1.id, t.id),
         t.id

如果层次结构是有限的,则不需要递归查询.

Recursive queries are not needed if the hierarchy is finite.

order by子句是棘手的部分.它只是从最高级别开始按层次结构的级别进行排序.

The order by clause is the tricky part. It just orders by the levels of the hierarchy, starting at the topmost level.

此版本的原始版本适用于问题中的数据.更广泛的测试发现它并不总是有效.我相信这个版本会一直有效.

The original version of this worked on the data in the question. More extensive testing found that it did not always work. I believe this version always works.

这篇关于父子mysql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆