如何在纯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屋！