如何创建MySQL分层递归查询？
如何创建MySQL分层递归查询？
提问于 2018-02-26 06:18:16
我有一个MySQL表，如下所示：
id | name | parent_id
19 | category1 | 0
20 | category2 | 19
21 | category3 | 20
22 | category4 | 21
......现在，我希望有一个mysql查询，只需向其提供id。例如，说‘id=19’那么我应该得到它所有的子ID也就是说，结果应该有ID‘20，21，22’。
如果可能的话，让我知道如何使用一个MySQL查询来实现相同的结果。
回答 2
Stack Overflow用户
发布于 2018-02-26 14:49:50
对于不支持公共表表达式(直到5.7版)的MySQL版本，可以通过以下查询实现这一点：
select id,
name,
parent_id
from (select * from products
order by parent_id, id) products_sorted,
(select @pv := '19') initialisation
where find_in_set(parent_id, @pv) > 0
and @pv := concat(@pv, ',', id)对于非常大的数据集，此解决方案可能会变慢，因为find_in_set操作不是在列表中查找数字的最理想方法。
备选案文1：查询如下所示：
with recursive cte (id, name, parent_id) as
(
select id,
name,
parent_id
from products
where parent_id = 19
union all
select p.id,
p.name,
p.parent_id
from products p
inner join cte
on p.parent_id = cte.id
)
select * from cte;有些数据库有一种用于分层查找的非标准语法，例如CONNECT BY子句在Oracle数据库中可用。DB2还支持这种替代语法。
MySQLVersion 5.7不提供这样的功能。当数据库引擎提供了这种语法时，这无疑是最好的选择。如果没有，那么也考虑以下备选方案。
备选方案2：路径样式标识符
如果你愿意id包含分层信息的值：路径。例如，在您的示例中，这个示例可能如下所示：
ID | NAME
19 | category1
19/1 | category2
19/1/1 | category3
19/1/1/1 | category4 那你的select会是这样的：
select id,
name
from products
where id like '19/%'备选方案3：重复自联接
如果您知道层次结构树的深度的上限，则可以使用标准。sql就像这样：
select p6.parent_id as parent6_id,
p5.parent_id as parent5_id,
p4.parent_id as parent4_id,
p3.parent_id as parent3_id,
p2.parent_id as parent2_id,
p1.parent_id as parent_id,
p1.id as product_id,
p1.name
from products p1
left join products p2 on p2.id = p1.parent_id
left join products p3 on p3.id = p2.parent_id
left join products p4 on p4.id = p3.parent_id
left join products p5 on p5.id = p4.parent_id
left join products p6 on p6.id = p5.parent_id
where 19 in (p1.parent_id,
p2.parent_id,
p3.parent_id,
p4.parent_id,
p5.parent_id,
p6.parent_id)
order by 1, 2, 3, 4, 5, 6, 7;Stack Overflow用户
发布于 2018-02-26 15:49:37
表结构
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+----------------------+--------+查询：
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';输出：
+-------------+----------------------+--------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
+-------------+----------------------+--------------+-------+或者
select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv数出：
category_id name parent
19 category1 0
20 category2 19
21 category3 20
22 category4 21页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100007441
复制相关文章
相似问题