我已经创建了我的第一个(希望)工作MySQL存储过程,我正试图弄清楚如何显示"COUNT" -子女,孙子等的数量
我所针对的表(gz_life_哺乳动物)的特征是以父子关系排列的科学名称(字段Taxon和Parent),以及一个数字父ID,如下所示:
Taxon | Parent | ParentID
Mammalia | Chordata | 1
Carnivora | Mammalia | 2
Felidae | Carnivora | 3
Panthera | Felidae | 4
Panthera-leo | Panthera | 5
Panthera-tigris | Panthera | 5
Canidae | Carnivora | 3
Canis | Canidae | 4
Canis-lupus | Canis | 5
因此,如果我访问MySite/life/肉食动物,我希望它显示子代(2 - Felidae和Canidae),孙辈(2)和重孙辈(3)的数量,所有级别5[物种])。如果我访问MySite/life/mammalia,它会显示1个孩子(食肉动物),2个孙子,2个曾孙和3个曾曾孙。
这是我的存储过程中的代码:
BEGIN
-- theId parameter means i am anywhere in hierarchy of Taxon
-- and i want all decendent Taxons
declare bDoneYet boolean default false;
declare working_on int;
declare next_level int; -- parent's level value + 1
declare theCount int;
CREATE temporary TABLE xxFindChildenxx
( -- A Helper table to mimic a recursive-like fetch
N int not null, -- from OP's table called 'gz_life_mammals'
processed int not null, -- 0 for not processed, 1 for processed
level int not null, -- 0 is the id passed in, -1=trying to figure out, 1=children, 2=grandchildren, etc
ParentID int not null -- helps clue us in to figure out level
-- NOTE: we don't care about level or parent when N=parameter theId passed into stored proc
-- in fact we will be deleting that row near the bottom or proc
);
set bDoneYet=false;
insert into xxFindChildenxx (N,processed,level,ParentID) select theId,0,0,0; -- prime the pump, get sp parameter in here
-- stay inside below while til all retrieved children/children of children are retrieved
while (!bDoneYet) do
-- see if there are any more to process for children
-- simply look in worktable for ones where processed=0;
select count(*) into theCount from xxFindChildenxx where processed=0;
if (theCount=0) then
-- found em all, we are done inside this while loop
set bDoneYet=true;
else
-- one not processed yet, insert its children for processing
SELECT N,level+1 INTO working_on,next_level FROM xxFindChildenxx where processed=0 limit 1; -- order does not matter, just get one
-- insert the rows where the parent=the one we are processing (working_on)
insert into xxFindChildenxx (N,processed,level,ParentID)
select N,0,next_level,ParentID
from gz_life_mammals
where ParentID=working_on;
-- mark the one we "processed for children" as processed
-- so we processed a row, but its children rows are yet to be processed
update xxFindChildenxx set processed=1 where N=working_on;
end if;
end while;
delete from xxFindChildenxx where N=theId; -- don't really need the top level row now (stored proc parameter value)
select level,count(*) as lvlCount from xxFindChildenxx group by level;
drop table xxFindChildenxx;
END
这是我为你写的关于计数的答案的衍生。你要做的是,而不是在最后丢弃计数,那是子代的计数,子代的计数等等,考虑以下内容。
你留下了一个临时表(xxFindChildenxx
),里面有id。
您也有您的分类表gz_life_mammals
,您显示在您的问题的顶部。您需要在该表中显示的是第一列,即表示该行主键的auto_inc id。
有了这两个表,都有id,你把xxFindChildenxx.ParentId
连接回gz_life_mammals.id
,按level
排序