我希望仅使用 MySQL 从树形结构的所有子元素中获取其 ID。
我有一个如下所示的表:
ID parent_id name
1 0 cat1
2 1 subcat1
3 2 sub-subcat1
4 2 sub-subcat2
5 0 cat2
现在我想递归地获取cat1的所有子ID(2、3、4)。有没有什么方法可以实现这个目标?
有两种基本方法可以实现此操作:邻接表和嵌套列表。请查看在 MySQL 中管理分层数据。
您拥有的是邻接表。没有一种方法可以使用单个 SQL 语句递归地抓取所有后代。如果可能的话,只需获取它们并在代码中映射它们。
嵌套集可以做到您想要的,但我倾向于避免使用它,因为插入记录的成本很高,并且容易出错。
这里是一个简单的单查询MySql解决方案:
SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
SELECT @Ids := (
SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
FROM `table_name`
WHERE FIND_IN_SET(`parent_id`, @Ids)
) Level
FROM `table_name`
JOIN (SELECT @Ids := <id>) temp1
) temp2
只需将<id>
用父元素的ID
替换。
这将返回一个字符串,其中包含具有ID
= <id>
的元素的所有后代的ID
,由,
分隔。如果您希望返回多行,并在每行上有一个后代,请使用类似以下内容的内容:
SELECT *
FROM `table_name`
WHERE FIND_IN_SET(`ID`, (
SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
SELECT @Ids := (
SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
FROM `table_name`
WHERE FIND_IN_SET(`parent_id`, @Ids)
) Level
FROM `table_name`
JOIN (SELECT @Ids := <id>) temp1
) temp2
))
包括根/父元素
OP请求元素的子级,已在上面回答。某些情况下,将根/父元素包含在结果中可能有用。以下是我的建议解决方案:
逗号分隔的ID字符串:
SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
SELECT <id> Level
UNION
SELECT @Ids := (
SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
FROM `table_name`
WHERE FIND_IN_SET(`parent_id`, @Ids)
) Level
FROM `table_name`
JOIN (SELECT @Ids := <id>) temp1
) temp2
多行:
SELECT *
FROM `table_name`
WHERE `ID` = <id> OR FIND_IN_SET(`ID`, (
SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
SELECT @Ids := (
SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
FROM `table_name`
WHERE FIND_IN_SET(`parent_id`, @Ids)
) Level
FROM `table_name`
JOIN (SELECT @Ids := <id>) temp1
) temp2
))
WHERE
子句中必须有OR FIND_IN_SET(id, @Ids)
。谢谢! - WironeDROP TABLE IF EXISTS `parent_child`;
CREATE TABLE `parent_child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
insert into `parent_child`(`id`,`name`,`parent_id`)
values (1,'cat1',0),(2,'subcat1',1),
(3,'sub-subcat1',2),(4,'sub-subcat2',2),
(5,'cat2',0);
DELIMITER $$
USE `yourdatabase`$$
DROP FUNCTION IF EXISTS `GetAllNode1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `GetAllNode1`(GivenID INT) RETURNS TEXT CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE rv,q,queue,queue_children TEXT;
DECLARE queue_length,front_id,pos INT;
SET rv = '';
SET queue = GivenID;
SET queue_length = 1;
WHILE queue_length > 0 DO
SET front_id = queue;
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) AS qc
FROM `parent_child` 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$$
DELIMITER ;
SELECT GetAllNode1(id) FROM parent_child
or
SELECT GetAllNode1(id) FROM parent_child where id =1 //for specific parent's child element
如果您可以使用存储过程,那么您可能可以通过存储过程来完成它。
否则,您无法使用单个SQL语句完成它。
理想情况下,您应该从程序中进行递归调用以遍历树形结构。
你的问题似乎有点不精确。你想要它们,并且你所说的“在树形结构中”是什么意思?
你拥有的表格已经是(关系型表示方式的)树形结构了。
如果你想把它们“放入一个表格中”,并使用包含ID 4和ParentID 0对的行来保存它们,那么你需要你的SQL引擎版本的递归SQL来完成这个任务,前提是该引擎支持此功能。
我不知道MySQL具体情况,但我了解的情况是,他们曾计划使用Oracle相同的语法(即CONNECT BY)来实现递归SQL。
如果你在手册的目录中查找“递归查询”或“CONNECT BY”等关键词,我想你应该能够找到答案。
(很抱歉我不能提供更易于理解的答案。)