如何在纯MySQL中生成类别的面包屑? [英] How can I generate a breadcrumb of Categories in pure MySQL?
问题描述
说我有mdl_course_categories表:
Say I have the mdl_course_categories table:
select cat.id, cat.name, cat.parent, cat.depth, cat.path
from mdl_course_categories cat
| id | name | parent | depth | path |
|----|------|--------|-------|----------|
| 1 | a | 0 | 1 | /1 |
| 2 | b | 0 | 1 | /2 |
| 3 | c | 1 | 2 | /1/3 |
| 4 | d | 3 | 3 | /1/3/4 |
| 5 | e | 4 | 4 | /1/3/4/5 |
| 6 | f | 0 | 1 | /6 |
| 7 | g | 6 | 2 | /6/7 |
| 8 | h | 7 | 3 | /6/7/8 |
| 9 | i | 3 | 3 | /1/3/9 |
| 10 | j | 7 | 3 | /6/7/10 |
如何按照以下方式生成面包屑?我是SQL的完整新手,并且没有管理层次结构数据的经验.我查看了此答案,但无法将其转换为我的表格和情况.预先感谢!
How can I generate the breadcrumbs as per below? I'm a complete rookie to SQL, and have no experience managing hierarchial data. I had a look at this answer, but was unable to translate it to my table and situation. Thanks in advance!
| id | name | parent | depth | path | breadcrumb |
|----|------|--------|-------|----------|---------------|
| 1 | a | 0 | 1 | /1 | a |
| 2 | b | 0 | 1 | /2 | b |
| 3 | c | 1 | 2 | /1/3 | a - c |
| 4 | d | 3 | 3 | /1/3/4 | a - c - d |
| 5 | e | 4 | 4 | /1/3/4/5 | a - c - d - e |
| 6 | f | 0 | 1 | /6 | f |
| 7 | g | 6 | 2 | /6/7 | f - g |
| 8 | h | 7 | 3 | /6/7/8 | f - g - h |
| 9 | i | 3 | 3 | /1/3/9 | a - b - i |
| 10 | j | 7 | 3 | /6/7/10 | f - g - j |
推荐答案
WITH RECURSIVE
cte1 AS
(
SELECT 1 num
UNION ALL
SELECT num + 1
FROM cte1
WHERE num < ( SELECT MAX(LENGTH(path) - LENGTH(REPLACE(path, '/', '')))
FROM mdl_course_categories )
),
cte2 AS
(
SELECT cte1.num,
cat.*,
SUBSTRING_INDEX(SUBSTRING_INDEX(cat.path, '/', cte1.num+1), '/', -1) onechar
FROM cte1, mdl_course_categories cat
WHERE cte1.num <= (LENGTH(path) - LENGTH(REPLACE(cat.path, '/', '')))
),
cte3 AS
(
SELECT cte2.*, cat.name onecharname
FROM cte2, mdl_course_categories cat
WHERE cte2.onechar = cat.id
)
SELECT id, name, parent, depth, path, GROUP_CONCAT(onecharname ORDER BY num SEPARATOR ' - ') breadcrumb
FROM cte3
GROUP BY id, name, parent, depth, path;
当然可以压缩,但是我认为您可以自己完成.
Of course it can be compacted, but I think you can do it by yourself.
应 @Shadow 的要求,我还提供了一个简单的解决方案
At the request of @Shadow I also give a simple solution
SELECT t1.id,
t1.name,
t1.parent,
t1.depth,
t1.path,
GROUP_CONCAT(t2.name ORDER BY LOCATE(CONCAT('/', t2.id, '/'), CONCAT(t1.path, '/')) SEPARATOR ' - ') breadcrumb
FROM mdl_course_categories t1,
mdl_course_categories t2
WHERE LOCATE(CONCAT('/', t2.id, '/'), CONCAT(t1.path, '/'))
GROUP BY t1.id,
t1.name,
t1.parent,
t1.depth,
t1.path;
PS.请记住,第一个是 O(N * AVG(M))
,而第二个是 O(N * N)
-其中 N
是记录量,而 M
是树的深度.
PS. Remember, 1st is O(N*AVG(M))
whereas 2nd is O(N*N)
- where N
is amount of records, and M
is tree depth.
这篇关于如何在纯MySQL中生成类别的面包屑?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!