我有一个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查询来实现相同的结果。
发布于 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;
发布于 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
https://stackoverflow.com/questions/-100007441
复制相似问题