MySQL II中的分层查询 [英] Hierarchical Query in MySQL II
问题描述
我正在尝试找到一种方法，在关注动物的网站上显示孙子孙女，孙子孙女等的数量.有人告诉我有关一个非常酷的查询@ MySQL中的分层查询
下面是我的改编.
$stm = $pdo->prepare("SELECT COUNT(@id := (
SELECT `Taxon`
FROM gz_life_mammals
WHERE `Parent` = @id
)) AS numDescendants
FROM (
SELECT @id := :MyURL
) vars
STRAIGHT_JOIN gz_life_mammals
WHERE @id IS NOT NULL");
$stm->execute(array(
'MyURL'=>$MyURL
));
while ($row = $stm->fetch())
{
$ChildrenCount = $row['numDescendants'];
}
echo $ChildrenCount;
实际上，我认为我的系统可以计算孩子的数量，但是接下来我将研究孙子.无论如何，当我导航到一个物种页面时，它正确显示计数为0.但是当我导航到父页面时，我得到以下错误消息:
违反基数:1242子查询返回多于1行
任何人都可以告诉我发生了什么事以及如何解决该问题吗?
我的数据库表在分类单元字段中以父子关系显示动物分类单元，如下所示:
Taxon | Parent
Mammalia | Chordata
Carnivora | Mammalia
Canidae | Carnivora
Canis | Canidae
Canis-lupus | Canis
要查看有关狼(犬狼疮)的信息，我将导航至MySite/life/canis-lupus
编辑中
这是表架构.但是，我无法使其与SQFiddle一起使用.一个错误接一个错误.
CREATE TABLE t (
N INT(6) default None auto_increment,
Taxon varchar(50) default NULL,
Parent varchar(25) default NULL,
NameCommon varchar(50) default NULL,
Rank smallint(2) default 0
PRIMARY KEY (N)
) ENGINE=MyISAM
希望人们会同意，这不是无答案的仅作答，因为该代码在整个文档中都有详细记录.
基本上，它是一个自联接表，其中一行引用其父级是谁.存储的proc将使用工作表来查找子代，子代子等，并维持一个等级.
例如，level = 1代表孩子，level = 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手册页将定界符与MySqlScript . imo不是一个很棒的手册页，但是请相信我.创建触发器和
请记住，1是存储的proc的参数.并且这已经存在于创建的数据库中，如果您按照上述操作进行操作，则该数据库称为 I'm trying to figure out a way to display the number of grandchildren, great grandchildren, etc. on a website focusing on animals. Someone told me about a really cool query @ Hierarchical queries in MySQL Below is my adaptation. I think I have it set up to count children, actually, but I'll work on grandchildren next. Anyway, when I navigate to a species page, it correctly displays a count of 0. But when I navigate to a parent page, I get this error message: Cardinality violation: 1242 Subquery returns more than 1 row Can anyone tell me what's going on and how I can fix that? My database table features animal taxa in a parent-child relationship in the field Taxon, like this: To see information about the wolf (Canis lupus), I would navigate to MySite/life/canis-lupus ON EDIT Here's the table schema. I can't make it work with SQFiddle, though; one error after another.
Hopefully one would agree that this is not an answer-only Answer without explanation, since the code is quite documented throughout. Basically, it is a self-join table with a row having a reference to who its parent is. The stored proc will use a worktable to find children, children-of-children, etc. And maintain a level. For instance, level=1 represents children, level=2 represents grandchildren, etc. At the end, the counts are retrieved. As the id's are in the worktable, expand as you wish with it.TaxonSandbox.$stm = $pdo->prepare("SELECT COUNT(@id := (
SELECT `Taxon`
FROM gz_life_mammals
WHERE `Parent` = @id
)) AS numDescendants
FROM (
SELECT @id := :MyURL
) vars
STRAIGHT_JOIN gz_life_mammals
WHERE @id IS NOT NULL");
$stm->execute(array(
'MyURL'=>$MyURL
));
while ($row = $stm->fetch())
{
$ChildrenCount = $row['numDescendants'];
}
echo $ChildrenCount;
Taxon | Parent
Mammalia | Chordata
Carnivora | Mammalia
Canidae | Carnivora
Canis | Canidae
Canis-lupus | Canis
CREATE TABLE t (
N INT(6) default None auto_increment,
Taxon varchar(50) default NULL,
Parent varchar(25) default NULL,
NameCommon varchar(50) default NULL,
Rank smallint(2) default 0
PRIMARY KEY (N)
) ENGINE=MyISAM
Schema
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);
Stored Procedure
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
Test Stored Proc
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 |
+-------+----------+
So there are 2 children, 3 grandchildren, 2 great-grandchildren, 3 great-great, and 1 great-great-great
Were one to pass an id to the stored proc that does not exist, or one that has no children, no result set rows are returned.
Edit: other comments, due to leaving the OP hanging on understanding his first stored proc creation I believe. Plus other questions that point back here.
Delimiters
Delimiters are important to wrap the block of the stored proc creation. The reason is so that mysql understands that the sequence of statements that follow are still part of the stored proc until it reaches the specified delimiter. In the case above, I made up one called $$ that is different from the default delimiter of a semi-colon that we are all used to. This way, when a semi-colon is encountered inside the stored proc during creation, the db engine will just consider it as one the many statements inside of it instead of terminating the stored proc creation. Without doing this delimiter wrapping, one can waste hours trying to create their first stored proc getting Error 1064 Syntax errors. At the end of the create block I merely have a line
$$
which tell mysql that that is the end of my creation block, and then the default delimiter of a semi-colon is set back with the call to
DELIMITER ;
Mysql manual page Using Delimiters with MySqlScript. Not a great manual page imo, but trust me on this one. Same issue when creating Triggers and Events.
PHP
To call this stored proc from php, it is just a string, "call showHierarchyUnder(1)". It returns a result set as described above, which, as described, can return a result set with no rows.
Remember that the 1 is a parameter to the stored proc. And that this exists in a database created, called TaxonSandbox if you followed the above.
这篇关于MySQL II中的分层查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
