php - MySQL 中不允许在递归公用表表达式中使用 LIMIT
问题描述
我的目标是使用最新的 MySQL 的 WITH RECURSIVE 方法构造一棵树。
我的表被称为categories
有 2 行。ID
和parentID row
。_
我的分类表:
. 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
.... . | ...
从 2 到 9 的 ID 具有相同的父级,即 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
Recursive Common Table Expression
这是我的带有导致问题的 LIMIT 子句的mySQL查询:
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'
请注意,我使用的是 MySQL 8.0 + 版本。我明白错误很明显。但是,如果我在同一个父母之下有 100 万个孩子呢?它会冻结系统!
我将非常感谢一种解决方法。
谢谢你。
解决方案
如果我正确地跟随你,row_number()
可以做你想做的事。这个想法是枚举categories
recusive部分中的行,然后过滤前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
推荐阅读
- vba - 在工作表 1 的数据下粘贴具有工作表 2 中特定单词的数据
- java - 如何将 spring DataSource tomcat 迁移到 hikari 属性?
- node.js - chai-http - res.json 从单元测试调用时不是一个函数
- javascript - 从大数组中删除重复的字符串
- elasticsearch - 将 DSL 查询转换为 NEST.net
- c - C memcpy 字节缓冲区到打包结构,好决定吗?
- javascript - 我的用户登录错误验证消息曾经显示在 chrome 上,但现在不显示了
- reactjs - 如何在 ReactXP 中检测屏幕尺寸变化/设备旋转?
- c# - 打开街道地图 - Itinero C# - 道路限制
- windows - Windows 10 上的 Jenkins - 如何将默认 shell 设置为 Ubuntu.exe