我的目标是用最新的MySQL和递归方法构造一个树。
我的表名为categories，有2行。 ID和ParentId行。
我的类别表:
. ID . | ParentID
--------|----------
. 1 . | null
. 2 . | 1
. 3 . | 1
. 4 . | 1
. 6 . | 1
. 7 . | 1
. 8 . | 1
. 9 . | 1
. 10 . | 1
. 11 . | 13
. 12 . | 14
. 13 . | 12
.... . | ...
ID从2到9，具有相同的父级，即ID=1的父级。 这就是我试图通过在递归公用表表达式的第二个SELECT查询中提供“Limit 5”来限制的内容。
上表在树中的光学表示如下:我的问题是限制同一级别的子级的数量（在下面的插图中标记为Y项）。
+ Item X .............. (level 1)
+ Item Y .............. (level 2)
+ Item Y .............. (level 2)
+ Item Y .............. (level 2)
+ .... LIMIT to 5 Items
+ Item X
+ Item X
+ Item X
+ Item X
+ Item X
+ Item X
这是我的mySQL递归公用表表达式查询，其中的LIMIT子句导致了问题:
WITH RECURSIVE cte AS
(
SELECT ID, 0 AS depth, CAST(ID AS CHAR(200)) AS path
FROM categories WHERE parentID = 1
UNION ALL
SELECT c.ID, cte.depth+1, CONCAT(cte.path, ',', c.ID)
FROM categories c
JOIN cte ON cte.ID = c.parentID
WHERE FIND_IN_SET(c.ID, cte.path)=0 AND depth <= 10
LIMIT 5
)
SELECT * FROM cte
逻辑上，我希望通过在CTE的第二个Select部分使用LIMIT子句来约束第二个Select语句返回的行数来对问题进行排序。 但它给了我一个错误:
This version of MySQL doesn't yet support 'ORDER BY / LIMIT over UNION in recursive Common Table Expression'
注意，我使用的是MySQL8.0+版本。 我知道错误很明显。 但是如果我有一百万个孩子在同一个父母下面呢？ 它会冻结系统！
我将非常感谢一个变通方法。
谢谢。
如果我没听错，row_number()可以做您想做的事情。 其思想是枚举循环部分中的categories行，然后筛选前5个:
with recursive cte as (
select id, 0 as depth, cast(id as char(200)) as path
from categories
where parentid = 1
union all
select c.id, cte.depth+1, concat(cte.path, ',', c.id)
from cte
inner join (
select c.*, row_number() over(partition by c.parentid order by c.id) rn
from categories c
) c on cte.id = c.parentid
where find_in_set(c.id, cte.path) = 0 and depth <= 10 and c.rn <= 5
)
select * from cte
您可以通过预筛选数据集稍微优化一下:
with recursive
cats as (
select *
from (
select c.*, row_number() over(partition by parentid order by id) rn
from categories c
) t
where rn <= 5
),
cte as (
select id, 0 as depth, cast(id as char(200)) as path
from cats
where parentid = 1
union all
select c.id, cte.depth+1, concat(cte.path, ',', c.id)
from cte
inner join cats c on cte.id = c.parentid
where find_in_set(c.id, cte.path) = 0 and depth <= 10 and c.rn <= 5
)
select * from cte
