如何创建一个MySQL分层递归查询
我有一个MySQL表，如下所示：
id | name | parent_id
19 | category1 | 0
20 | category2 | 19
21 | category3 | 20
22 | category4 | 21
......
现在，我想要一个简单的提供id的MySQL查询[例如'id = 19']，那么我应该得到它的所有子id [即结果应该有'20,21,22']。 ...此外，孩子的层次结构是不知道它可以变化....
另外，我已经有了使用for循环的解决方案.....如果可能的话，让我知道如何使用单个MySQL查询来实现相同的功能。
对于不支持公用表表达式（高达5.7版）的MySql版本，可以通过以下查询来实现：
select id,
name,
parent_id
from (select * from products
order by parent_id, id) products_sorted,
(select @pv := '19') initialisation
where find_in_set(parent_id, @pv)
and length(@pv := concat(@pv, ',', id))
这是一个小提琴。
在@pv := '19'指定的值应该被设置为你想选择所有后代的父代的id 。
如果父母有多个孩子，这也将起作用。 但是，要求每个记录满足条件parent_id < id ，否则结果将不完整。
该查询使用特定的MySql语法：在执行期间分配和修改变量。 对执行顺序做了一些假设：
from子句首先被评估。 所以这就是@pv被初始化的地方。 from别名中检索的顺序为每条记录评估where子句。 因此，这是一个条件，只包含父代已被识别为在后代树中的记录（主要父代的所有后代逐渐添加到@pv ）。 where子句中顺序进行评估，并且一旦总的结果是肯定的评价被中断。 因此，第二个条件必须排在第二位，因为它将id添加到父列表中，并且只有当id通过第一个条件时才会发生这种情况。 只有调用length函数才能确保此条件始终为真，即使由于某种原因pv字符串会产生虚假值。 总而言之，人们可能会发现这些假设过于依赖风险 - 它们没有形成文件的保证，即使它始终如一地运行，当您将此查询用作视图或子视图时，理论上评估顺序可能仍会发生变化，在更大的查询中查询。
另请注意，对于非常大的数据集，此解决方案可能会变慢，因为find_in_set操作并不是在列表中找到数字的最理想方式，当然不是在与数量相同的数量级达到大小的列表中的记录返回。
备选方案1： WITH RECURSIVE ， CONNECT BY
越来越多的数据库为递归查询（例如Postgres 8.4+，SQL Server 2005+，DB2，Oracle 11gR2 +，SQLite 3.8.4+，Firebird 2.1+，H2，HyperSQL 2.1）实现SQL：1999 ISO标准WITH [RECURSIVE]语法。 0+，Teradata，MariaDB 10.2.2+）。 从版本8.0开始，MySql也支持它。 使用该语法，查询如下所示：
with recursive cte (id, name, parent_id) as
(
select id,
name,
parent_id
from products
where parent_id = 19
union all
select p.id,
p.name,
p.parent_id
from products p
inner join cte
on p.parent_id = cte.id
)
select * from cte;
某些数据库具有用于分层查找的替代非标准语法，例如Oracle数据库上提供的CONNECT BY子句。 DB2也支持这种替代语法。
MySql版本5.7不提供这样的功能。 当你的数据库引擎提供这种语法时，那当然是最好的选择。 如果不是，那么也考虑以下选择。
备选方案2：路径样式标识符
如果您要分配包含分层信息的id值，则事情变得更加容易：路径。 例如，在你的情况下，这可能看起来像这样：
ID | NAME
19 | category1
19/1 | category2
19/1/1 | category3
19/1/1/1 | category4
然后你的select将如下所示：
select id,
name
from products
where id like '19/%'
备选方案3：重复的自我连接
如果您知道您的层次结构树可以变得多深的上限，则可以使用如下所示的标准sql ：
select p6.parent_id as parent6_id,
p5.parent_id as parent5_id,
p4.parent_id as parent4_id,
p3.parent_id as parent3_id,
p2.parent_id as parent2_id,
p1.parent_id as parent_id,
p1.id as product_id,
p1.name
from products p1
left join products p2 on p2.id = p1.parent_id
left join products p3 on p3.id = p2.parent_id
left join products p4 on p4.id = p3.parent_id
left join products p5 on p5.id = p4.parent_id
left join products p6 on p6.id = p5.parent_id
where 19 in (p1.parent_id,
p2.parent_id,
p3.parent_id,
p4.parent_id,
p5.parent_id,
p6.parent_id)
order by 1, 2, 3, 4, 5, 6, 7;
看到这个小提琴
where条件指定您想要检索哪个父代的后代。 您可以根据需要使用更多级别扩展此查询。
从管理MySQL中的分层数据的博客
表结构
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+----------------------+--------+
查询：
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';
产量
+-------------+----------------------+--------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
+-------------+----------------------+--------------+-------+
大多数用户曾经在SQL数据库中处理过分层数据，并且毫无疑问地知道分层数据的管理不是关系数据库的目标。 关系数据库的表不是分层的（比如XML），而只是一个简单的列表。 分层数据具有在关系数据库表中不自然表示的父子关系。 阅读更多
请参阅博客了解更多详情。
编辑：
select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv
输出：
category_id name parent
19 category1 0
20 category2 19
21 category3 20
22 category4 21
参考：如何在Mysql中执行递归SELECT查询？
我想到的最好的理由是
沿袭方法descr。 可以在任何地方找到，例如在这里或这里。 就功能而言 - 这就是激励我的东西。
最后 - 获得了或多或少简单，相对快速和简单的解决方案。
功能的身体
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
READS SQL DATA
BEGIN
DECLARE v_rec INT DEFAULT 0;
DECLARE done INT DEFAULT FALSE;
DECLARE v_res text DEFAULT '';
DECLARE v_papa int;
DECLARE v_papa_papa int DEFAULT -1;
DECLARE csr CURSOR FOR
select _id,parent_id -- @n:=@n+1 as rownum,T1.*
from
(SELECT @r AS _id,
(SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := the_id, @l := 0,@n:=0) vars,
table m
WHERE @r <> 0
) T1
where T1.parent_id is not null
ORDER BY T1.lvl DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open csr;
read_loop: LOOP
fetch csr into v_papa,v_papa_papa;
SET v_rec = v_rec+1;
IF done THEN
LEAVE read_loop;
END IF;
-- add first
IF v_rec = 1 THEN
SET v_res = v_papa_papa;
END IF;
SET v_res = CONCAT(v_res,'-',v_papa);
END LOOP;
close csr;
return v_res;
END
然后你就是
select get_lineage(the_id)
希望它有助于某人:)
链接地址: http://www.djcxy.com/p/93867.html