MySQL-邻接表模型-获取深度 [英] MySQL - Adjacency List Model - Get Depth
问题描述
我有一个组织表,该表具有一个id
,一个parent_id
和一个name
列.该表中大约有5万行.只有一个顶级父级,其余都在此之下.在Oracle中,我可以使用level
伪列轻松地检索特定组织的当前深度:
SELECT id, parent_id, level, name
FROM organizations
START WITH parent_id = 1
CONNECT BY PRIOR id = parent_id
我不知道在MySQL中执行上述操作的正确方法.我需要在一个查询中获取整棵树以及节点的深度.
关于StackOverflow的问题很多,但是似乎没有一个很好的答案,主要是链接到带有可疑解决方案的博客.当然可以通过某种简单的方式来做到这一点吗?
不幸的是,以任何方式修改表都不是一种选择,因此嵌套集是不可能的.
这完全很有趣.我昨天刚从一个类似的问题上得到了+50的赏金:如何使用DBA StackExchange中的存储过程来实现此目的(2011年10月24日) >
我将发布相同的存储过程以及DBA StackExchange答案中的示例:
获取任何给定节点的父代的代码
DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetParentIDByID` $$
CREATE FUNCTION `junk`.`GetParentIDByID` (GivenID INT) RETURNS INT
DETERMINISTIC
BEGIN
DECLARE rv INT;
SELECT IFNULL(parent_id,-1) INTO rv FROM
(SELECT parent_id FROM pctable WHERE id = GivenID) A;
RETURN rv;
END $$
DELIMITER ;
获取任意给定节点的密码的代码
DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetAncestry` $$
CREATE FUNCTION `junk`.`GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
DECLARE rv VARCHAR(1024);
DECLARE cm CHAR(1);
DECLARE ch INT;
SET rv = '';
SET cm = '';
SET ch = GivenID;
WHILE ch > 0 DO
SELECT IFNULL(parent_id,-1) INTO ch FROM
(SELECT parent_id FROM pctable WHERE id = ch) A;
IF ch > 0 THEN
SET rv = CONCAT(rv,cm,ch);
SET cm = ',';
END IF;
END WHILE;
RETURN rv;
END $$
DELIMITER ;
获取任何给定节点的家族树(或后代)的代码
DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$
CREATE FUNCTION `junk`.`GetFamilyTree` (GivenID INT) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE rv,q,queue,queue_children VARCHAR(1024);
DECLARE queue_length,front_id,pos INT;
SET rv = '';
SET queue = GivenID;
SET queue_length = 1;
WHILE queue_length > 0 DO
SET front_id = FORMAT(queue,0);
IF queue_length = 1 THEN
SET queue = '';
ELSE
SET pos = LOCATE(',',queue) + 1;
SET q = SUBSTR(queue,pos);
SET queue = q;
END IF;
SET queue_length = queue_length - 1;
SELECT IFNULL(qc,'') INTO queue_children
FROM (SELECT GROUP_CONCAT(id) qc
FROM pctable WHERE parent_id = front_id) A;
IF LENGTH(queue_children) = 0 THEN
IF LENGTH(queue) = 0 THEN
SET queue_length = 0;
END IF;
ELSE
IF LENGTH(rv) = 0 THEN
SET rv = queue_children;
ELSE
SET rv = CONCAT(rv,',',queue_children);
END IF;
IF LENGTH(queue) = 0 THEN
SET queue = queue_children;
ELSE
SET queue = CONCAT(queue,',',queue_children);
END IF;
SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
END IF;
END WHILE;
RETURN rv;
END $$
为演示所有操作的执行情况,以下是示例数据
USE junk
DROP TABLE IF EXISTS pctable;
CREATE TABLE pctable
(
id INT NOT NULL AUTO_INCREMENT,
parent_id INT,
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO pctable (parent_id) VALUES (0);
INSERT INTO pctable (parent_id) SELECT parent_id+1 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+2 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+3 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+4 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+5 FROM pctable;
SELECT * FROM pctable;
这里是查看所有父母,祖先和家谱的查询
SELECT
id,parent_id,
GetParentIDByID(id),
GetAncestry(id),
GetFamilyTree(id)
FROM pctable;
尝试一下!
I have an organization table that has an id
, a parent_id
, and a name
column. There are roughly 50k rows in this table. There is just one top level parent and the rest are all under that. In Oracle, I am able to easily retrieve the current depth of a particular organization with the level
pseudocolumn:
SELECT id, parent_id, level, name
FROM organizations
START WITH parent_id = 1
CONNECT BY PRIOR id = parent_id
I am at a loss of what the proper way to do the above in MySQL is. I need to fetch the entire tree along with the node's depth in one query.
There are a plethora of questions on StackOverflow that have to do with this, but none of them seem to have a really good answer to it, mostly links to blogs with dubious solutions. Surely this is doable in some sort of straight-forward manner?
Unfortunately modifying the table in any way is not an option, so nested sets is not a possibility.
This is totally hilarious. I just picked up a +50 bounty on a similar question literally yesterday : Using MySQL query to traverse rows to make a recursive tree
I referenced how to do this with Stored Procedures in the DBA StackExchange (October 24, 2011)
I will post the same stored procedures along with the examples from my DBA StackExchange answer:
Code to Get the Parent for Any Given Node
DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetParentIDByID` $$
CREATE FUNCTION `junk`.`GetParentIDByID` (GivenID INT) RETURNS INT
DETERMINISTIC
BEGIN
DECLARE rv INT;
SELECT IFNULL(parent_id,-1) INTO rv FROM
(SELECT parent_id FROM pctable WHERE id = GivenID) A;
RETURN rv;
END $$
DELIMITER ;
Code to Get the Ancenstry for Any Given Node
DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetAncestry` $$
CREATE FUNCTION `junk`.`GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
DECLARE rv VARCHAR(1024);
DECLARE cm CHAR(1);
DECLARE ch INT;
SET rv = '';
SET cm = '';
SET ch = GivenID;
WHILE ch > 0 DO
SELECT IFNULL(parent_id,-1) INTO ch FROM
(SELECT parent_id FROM pctable WHERE id = ch) A;
IF ch > 0 THEN
SET rv = CONCAT(rv,cm,ch);
SET cm = ',';
END IF;
END WHILE;
RETURN rv;
END $$
DELIMITER ;
Code to Get the Family Tree (or Descendants) for Any Given Node
DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$
CREATE FUNCTION `junk`.`GetFamilyTree` (GivenID INT) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE rv,q,queue,queue_children VARCHAR(1024);
DECLARE queue_length,front_id,pos INT;
SET rv = '';
SET queue = GivenID;
SET queue_length = 1;
WHILE queue_length > 0 DO
SET front_id = FORMAT(queue,0);
IF queue_length = 1 THEN
SET queue = '';
ELSE
SET pos = LOCATE(',',queue) + 1;
SET q = SUBSTR(queue,pos);
SET queue = q;
END IF;
SET queue_length = queue_length - 1;
SELECT IFNULL(qc,'') INTO queue_children
FROM (SELECT GROUP_CONCAT(id) qc
FROM pctable WHERE parent_id = front_id) A;
IF LENGTH(queue_children) = 0 THEN
IF LENGTH(queue) = 0 THEN
SET queue_length = 0;
END IF;
ELSE
IF LENGTH(rv) = 0 THEN
SET rv = queue_children;
ELSE
SET rv = CONCAT(rv,',',queue_children);
END IF;
IF LENGTH(queue) = 0 THEN
SET queue = queue_children;
ELSE
SET queue = CONCAT(queue,',',queue_children);
END IF;
SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
END IF;
END WHILE;
RETURN rv;
END $$
To demonstrate the execution of everything, here is the sample data
USE junk
DROP TABLE IF EXISTS pctable;
CREATE TABLE pctable
(
id INT NOT NULL AUTO_INCREMENT,
parent_id INT,
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO pctable (parent_id) VALUES (0);
INSERT INTO pctable (parent_id) SELECT parent_id+1 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+2 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+3 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+4 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+5 FROM pctable;
SELECT * FROM pctable;
Here is the query to see all Parents, Ancestries, and Family Trees
SELECT
id,parent_id,
GetParentIDByID(id),
GetAncestry(id),
GetFamilyTree(id)
FROM pctable;
Give it a Try !!!
这篇关于MySQL-邻接表模型-获取深度的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!