代码之家 › 专栏 › 技术社区 › Stuart

按父级排序的MySQL查询,然后按子级排序

mysql sql php

Stuart · 技术社区 · 17 年前

我的数据库中有一个页面表,每个页面都可以有一个父级,如下所示:

id            parent_id            title
1             0                    Home
2             0                    Sitemap
3             0                    Products
4             3                    Product 1
5             3                    Product 2
6             4                    Product 1 Review Page

如果有多个级别,那么选择所有按父、子、子顺序排序的页面的MySQL查询最好是什么?如果有多个级别,则最多有三个级别。上述示例将生成所需的顺序:

Home
Sitemap
Products
    Product 1
        Product 1 Review Page
    Product 2

6 回复 | 直到 15 年前

Wael Dalloul 17 年前

我认为您应该在表中再放入一个名为level的字段,并将其存储在节点的级别中,然后按级别然后按父级对查询进行排序。

Leonel Martins 17 年前

如果您必须坚持您的模型,我建议您进行以下查询:

SELECT p.id, p.title, 
       (
        SELECT LPAD(parent.id, 5, '0') 
        FROM page parent 
        WHERE parent.id = p.id AND parent.parent_id = 0 

        UNION

        SELECT CONCAT(LPAD(parent.id, 5, '0'), '.', LPAD(child.id, 5, '0')) 
        FROM page parent 
        INNER JOIN page child ON (parent.id = child.parent_id) 
        WHERE child.id = p.id AND parent.parent_id = 0 

        UNION

        SELECT CONCAT(LPAD(parent.id, 5, '0'), '.', LPAD(child.id, 5, '0'), '.', LPAD(grandchild.id, 5, '0')) 
        FROM page parent 
        INNER JOIN page child ON (parent.id = child.parent_id) 
        INNER JOIN page grandchild ON (child.id = grandchild.parent_id) 
        WHERE grandchild.id = p.id AND parent.parent_id = 0 
       ) AS level  
FROM page p
ORDER BY level;

结果集示例:

+-----+-------------------------+-------------------+
| id  | title                   | level             |
+-----+-------------------------+-------------------+
|   1 | Home                    | 00001             |
|   2 | Sitemap                 | 00002             |
|   3 | Products                | 00003             |
|   4 | Product 1               | 00003.00004       |
|   6 | Product 1 Review Page 1 | 00003.00004.00006 |
| 646 | Product 1 Review Page 2 | 00003.00004.00646 |
|   5 | Product 2               | 00003.00005       |
| 644 | Product 3               | 00003.00644       |
| 645 | Product 4               | 00003.00645       |
+-----+-------------------------+-------------------+
9 rows in set (0.01 sec)

解释输出:

+------+--------------------+--------------+--------+---------------+---------+---------+--------------------------+------+----------------+
| id   | select_type        | table        | type   | possible_keys | key     | key_len | ref                      | rows | Extra          |
+------+--------------------+--------------+--------+---------------+---------+---------+--------------------------+------+----------------+
|  1   | PRIMARY            | p            | ALL    | NULL          | NULL    | NULL    | NULL                     |  441 | Using filesort |
|  2   | DEPENDENT SUBQUERY | parent       | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.p.id                 |    1 | Using where    |
|  3   | DEPENDENT UNION    | child        | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.p.id                 |    1 |                |
|  3   | DEPENDENT UNION    | parent       | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.child.parent_id      |    1 | Using where    |
|  4   | DEPENDENT UNION    | grandchild   | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.p.id                 |    1 |                |
|  4   | DEPENDENT UNION    | child        | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.grandchild.parent_id |    1 |                |
|  4   | DEPENDENT UNION    | parent       | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.child.parent_id      |    1 | Using where    |
| NULL | UNION RESULT       | <union2,3,4> | ALL    | NULL          | NULL    | NULL    | NULL                     | NULL |                |
+------+--------------------+--------------+--------+---------------+---------+---------+--------------------------+------+----------------+
8 rows in set (0.00 sec)

我使用了这个表格布局:

CREATE TABLE `page` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `title` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `idx1` (`parent_id`)
);

请注意,为了提高性能,我在父级上包含了一个索引。

Wez Furlong 17 年前

如果您对表模式有一些控制,那么您可能需要考虑使用嵌套集表示。Mike Hillyer为此写了一篇文章:

Managing Hierarchical Data in MySQL

Brad Larson 15 年前

更聪明的工作,而不是更努力的工作:

SELECT menu_name , CONCAT_WS('_', level3, level2, level1) as level  FROM (SELECT 
t1.menu_name as menu_name , 
t3.sorting AS level3, 
t2.sorting AS level2, 
t1.sorting AS level1 
FROM 
en_menu_items  as t1
LEFT JOIN
en_menu_items  as t2
on 
t1.parent_id = t2.id
LEFT JOIN
en_menu_items  as t3
on
t2.parent_id = t3.id
) as depth_table
ORDER BY
level

就这样……

Noon Silk 17 年前

呃。像这样涉及树的查询是很烦人的,一般来说,如果您希望它可以扩展到任意数量的级别,那么您不需要使用单个查询,而是在每个级别使用一些构建树的方法。

Tom PaÅ¾ourek 17 年前

好吧,您总是可以在一个查询中获得所有信息并用PHP处理它。这可能是获得一棵树的简单方法。