显示存储过程中的简单COUNT（s）

Question

I've created my first (hopefully) working MySQL stored procedure, and I'm trying to figure out how to display "COUNT" - the number of children, grandchildren, etc.

The table I'm targeting (gz_life_mammals) features scientific names arranged in a parent-child relationship (fields Taxon and Parent), along with a numerical parent ID, like this:

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

So, if I visit MySite/life/carnivora, I would like it to display the number of children (2 - Felidae and Canidae), grand-children (2) and the number of great grand-children (3, all level 5 [species]). If I visit MySite/life/mammalia, it would display 1 child (Carnivora), 2 grand-children, 2 great grandchildren and 3 great great grandchildren.

And this is the code from my stored procedure:

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

Answer 1

This is a spin-off from the answer I wrote for you for the counts. What you are looking to do is to, instead of dumping the counts out at the end, that is the counts of children, the counts of grand-children etc, consider the following.

You are left with a temporary table (xxFindChildenxx) that has id's in them.

You also have your taxon table gz_life_mammals that you show at the top of your question. The thing you needed to show in that table was the first column, the auto_inc id that represents that rows primary key.

So with those 2 tables, both with id's in them, you join xxFindChildenxx.ParentId back to gz_life_mammals.id, ordered by level

The million dollar question is whether or not you like that structure I left you with. I was driving it in this direction: ParentId is an id that Drew (that is me) made up. You had it originally as a varchar or something. So you need to determine if that whole thing needs to be turned upside down the way I did it, and going back to strings (not id's)