按父级和子级地点排序的MySQL查询

Question

按父级和子级地点排序的MySQL查询

3

我有一个页面表在我的数据库中，每个页面都可以有一个父级页面，如下：

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

- Stuart

6个回答

6

我认为你应该在表中再增加一个字段，叫做“级别”，并将节点的级别存储在其中，然后按级别和父节点排序你的查询结果。

- Wael Dalloul

3

如果您对表模式有一定的控制权，则可以考虑改用嵌套集表示法。Mike Hillyer在这方面撰写了一篇文章：

《在MySQL中管理分层数据》

- Wez Furlong

2

工作要做得更聪明，而不是更辛苦：

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

这是它..

- tarbeya

1

哎呀，像这样涉及树的查询很烦人，通常情况下，如果你希望它能够适用于任意层级，你不会使用单个查询来完成，而是会在每个层级上使用几个查询来构建树形结构。

- Noon Silk

0

嗯，你可以在一个查询中获取所有数据并在 PHP 中处理它。这可能是获取树形结构的更简单的方法。

- Tom Pažourek

1

PHP 不像 SQL 那样快速！ - Amir Fo

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Leonel Martins · Accepted Answer

如果您必须坚持使用您的模型，我建议使用以下查询：

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)

EXPLAIN结果输出:

+------+--------------------+--------------+--------+---------------+---------+---------+--------------------------+------+----------------+
| 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`)
);

请注意，我在parent_id上包含了一个索引以提高性能。