MySQL II中的分层查询

希望人们会同意这不是一个没有答案的仅作答的答案，因为该代码在整个文档中都有详细记录。

基本上，它是一个自联接表，其中一行引用其父级是谁。存储的proc将使用工作表来查找子代，子代子等，并维持一个等级。

例如，级别= 1代表孩子，级别= 2代表孙子，等等。

最后，检索计数。由于ID位于工作表中，因此可以根据需要进行扩展。

架构图

create schema TaxonSandbox; -- create a separate database so it does not mess up your stuff
use TaxonSandbox; -- use that db just created above (stored proc created in it)

-- drop table t;
CREATE TABLE t (
 N int auto_increment primary key,
 Taxon varchar(50) not null,
 Parent int not null,   -- 0 can mean top-most for that branch, or NULL if made nullable
 NameCommon varchar(50) not null,
 Rank int not null,
 key(parent)
);
-- truncate table t;
insert t(taxon,parent,NameCommon,rank) values ('FrogGrandpa',0,'',0); -- N=1
insert t(taxon,parent,NameCommon,rank) values ('FrogDad',1,'',0); -- N=2  (my parent is N=1)
insert t(taxon,parent,NameCommon,rank) values ('FrogMe',2,'',0); -- N=3 (my parent is N=2)
insert t(taxon,parent,NameCommon,rank) values ('t4',1,'',0); -- N=4 (my parent is N=2)

insert t(taxon,parent,NameCommon,rank) values 
('t5',4,'',0),('t6',4,'',0),('t7',5,'',0),('t8',5,'',0),('t9',7,'',0),('t10',7,'',0),('t11',7,'',0),('t12',11,'',0);

储存程序

use TaxonSandbox;

drop procedure if exists showHierarchyUnder;
DELIMITER $$ -- will be discussed separately at bottom of answer
create procedure showHierarchyUnder
(
theId int -- the id of the Taxon to search for it's decendants (my awkward verbiage)
)
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 't'
        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
        parent 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,parent) 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,parent)
            select N,0,next_level,parent
            from t
            where parent=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
$$ -- tell mysql that it has reached the end of my block (this is important)
DELIMTER ; -- sets the default delimiter back to a semi-colon

测试存储过程

use TaxonSandbox; -- create a separate database so it does not mess up your stuff
call showHierarchyUnder(1);
+-------+----------+
| level | lvlCount |
+-------+----------+
|     1 |        2 |
|     2 |        3 |
|     3 |        2 |
|     4 |        3 |
|     5 |        1 |
+-------+----------+

所以有2个孩子，3个孙子，2个曾孙，3个曾曾和1个曾曾曾

是将ID传递给不存在的proc或没有子进程的id，不返回任何结果集行。

编辑：其他评论，由于让OP坚持理解我相信的他的第一个存储过程创建。加上其他问题，请回到此处。

定界符

分隔符对于包装存储的proc创建块很重要。原因是mysql理解，直到到达指定的定界符之前，后面的语句序列仍然是存储proc的一部分。在上面的例子中，我组成了一个称为$$的字符，它与我们都习惯的分号的默认分隔符不同。这样，当在创建过程中在存储的proc中遇到分号时，db引擎将仅将其视为其中的许多语句之一，而不是终止存储的proc创建。如果不执行此定界符包装，则可能会浪费时间尝试创建其第一个存储的proc，从而收到错误1064语法错误。在create块的末尾，我只有一行

$$

它告诉mysql那是我创建块的末尾，然后用调用将后面的分号的默认定界符设置回去。

DELIMITER ;

Mysql手册页将Delimiters与MySqlScript一起使用。imo不是一个很棒的手册页，但是请相信我。创建触发器和事件时出现相同的问题。

的PHP

要从php调用此存储的proc，它只是一个字符串，“调用sh​​owHierarchyUnder（1）”。它如上所述返回结果集，如上所述，该结果集可以返回无行的结果集。

请记住，1是存储过程的参数。并且这存在于创建的数据库中，TaxonSandbox如果您按照上面的说明进行调用。