我有包含以下列的表 categories:
I have table categories that contains following columns:
category_id
category_name
parent_id
我需要获取给定主类别的所有级别的所有子类别的列表，因此，例如，如果我给出某些 3 级类别的 ID，我将返回所有 4、5、6 级......类别的列表是那个 lvl 3 类别的孩子.
I need to get list of all subcategories on all levels for a given main category, so if for example I give id of some lvl 3 category I would get back list of all lvl 4, 5, 6... categories that are children of that one lvl 3 category.
不需要保留层次结构，只需一个简单的列表.
No hierarchy needs to be preserved, just a simple list.
我一开始想只用几个连接和子查询来做，但我认为之后类别会更深，所以这不是一条路.
I first thought about just doing it with several joins and subqueries but than I figured categories will bee much deeper afterwards so that's not a way to go.
由于我刚刚开始使用 SQL，我仍然不知道如何编写递归查询，所以这将是一个很好的帮助和学习材料.
Since I've just started SQL I still don't know how to write recursive queries so this would be a great help and learning material.
先阅读底部的请注意.好的，你回来了.
Read Please Note at bottom first. Ok good, you are back.
为类似递归的层次结构检索创建存储过程.
Creation of a Stored Procedure for recursive-like hierarchy retrieval.
请注意，您不希望按级别进行设置，但这很容易做到.
Note, you didn't want it by levels but that can easily be done.
create table category
( category_id int not null auto_increment primary key,
category_name varchar(40) not null,
parent_id int null, -- index on this column not a shabby idea
unique key (category_name)
);
insert category(category_name,parent_id) values ('car',null),('food',null); -- 1,2
insert category(category_name,parent_id) values ('ford',1),('chevy',1),('fruit',2); -- 3,4,5
insert category(category_name,parent_id) values ('economy',3),('escort',6),('exhaust',7); -- 6,7,8
insert category(category_name,parent_id) values ('chassis',7),('loud',8),('banana',5); -- 9,10,11
-- ok granted I could have explicity inserted category_id to make it more obvious
-- drop procedure showHierarchyBelow;
delimiter $$
create procedure showHierarchyBelow
(
catname varchar(40)
)
BEGIN
-- deleteMe parameter means i am anywhere in hierarchy of role
-- and i want me and all my offspring deleted (no orphaning of children or theirs)
declare bDoneYet boolean default false;
declare working_on int;
declare theCount int;
declare findFirst int;
select ifnull(category_id,0) into findFirst from category where category_name=catname;
CREATE TABLE xx_RecursishHelper_xx
( -- it's recurshish, not recursive
category_id int not null,
processed int not null
);
if isnull(findFirst) then
set findFirst=0;
end if;
insert into xx_RecursishHelper_xx (category_id,processed) select findFirst,0;
if (findFirst=0) then
set bDoneYet=true;
else
set bDoneYet=false;
end if;
while (!bDoneYet) do
-- I am not proud of this next line, but oh well
select count(*) into theCount from xx_RecursishHelper_xx where processed=0;
if (theCount=0) then
-- found em all
set bDoneYet=true;
else
-- one not processed yet, insert its children for processing
SELECT category_id INTO working_on FROM xx_RecursishHelper_xx where processed=0 limit 1;
insert into xx_RecursishHelper_xx (category_id,processed)
select category_id,0 from category
where parent_id=working_on;
-- mark the one we "processed for children" as processed
update xx_RecursishHelper_xx set processed=1 where category_id=working_on;
end if;
end while;
delete from xx_RecursishHelper_xx where category_id=findFirst;
select x.category_id,c.category_name
from xx_RecursishHelper_xx x
join category c
on c.category_id=x.category_id;
drop table xx_RecursishHelper_xx;
END
$$
call showHierarchyBelow('food');
+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
| 5 | fruit |
| 11 | banana |
+-------------+---------------+
call showHierarchyBelow('car');
+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
| 3 | ford |
| 4 | chevy |
| 6 | economy |
| 7 | escort |
| 8 | exhaust |
| 9 | chassis |
| 10 | loud |
+-------------+---------------+
call showHierarchyBelow('ford');
+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
| 6 | economy |
| 7 | escort |
| 8 | exhaust |
| 9 | chassis |
| 10 | loud |
+-------------+---------------+
call showHierarchyBelow('xxx');
-- no rows
请注意，我只是根据您的需要修改了我几个月前的答案.
Note I merely modified this Answer of mine from a few months ago for your needs.
以上仅用于说明目的.在现实世界中，我永远不会在存储过程中创建表.DDL 开销很大.相反，我会使用具有会话概念的预先存在的非临时表.并将其从已完成会话的行中清除.所以不要把上面的东西当作稻草人，等着你让它变得更好.询问这是否令人困惑.
The above is for illustrative purposes only. In a real world situation, I would never do create tables in a stored proc. The DDL overhead is significant. Instead, I would use pre-existing non temp tables with a session concept. And clean it out of rows for the session done. So do not take the above as any more than a straw man, waiting for you to make it more performant as such. Ask if that is confusing.
这篇关于mysql 层次结构自加入，检索所有子类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！