mysql - 嵌套集中的MySQL Dynamic Pivot

我有以下架构和查询，可获取嵌套集中的每个项目，并返回以逗号分隔的祖先列表：

CREATE TABLE Tree
(title varchar(20) PRIMARY KEY,
 `tree` int,
 `left` int,
 `right` int);

INSERT Tree
VALUES
("Food", 1, 1, 24),
('Fruit', 1, 2, 13),
('Red', 1, 3, 8),
('Cherry', 1, 4, 7),
('Cherry_pie', 1, 5, 6),
('Yellow', 1, 9, 12),
('Banana', 1, 10, 11),
('Meat', 1, 14, 23),
('Beef', 1, 15, 16),
('Pork', 1, 17, 22),
('Bacon', 1, 18, 21),
('Bacon_Sandwich', 1, 19, 20);

询问

SELECT T0.title node
  ,(SELECT GROUP_CONCAT(T2.title ORDER BY T2.left)
                FROM Tree T2
                WHERE T2.left < T0.left AND T2.right > T0.right
                ) ancestors
FROM Tree T0
GROUP BY T0.title;

小提琴：
http://sqlfiddle.com/#!9/0a854/10

结果：

title          | ancestors
--------------------------
Bacon          | Food,Meat,Pork
Bacon_Sandwich | Food,Meat,Pork,Bacon
Banana         | Food,Fruit,Yellow
etc.....

我想将祖先旋转/分开来分隔数字有标题的列，如下所示：

title          | 1    | 2      | 3      | 4
----------------------------------------------
Bacon          | Food | Meat   | Pork   |
Bacon_Sandwich | Food | Meat   | Pork   | Bacon
Banana         | Food | Fruit  | Yellow |
etc.....

祖先实际上可以是任何东西，我无法知道会有多少。

我不知道从哪里开始，但是如果有帮助，我可以使用准备好的语句，存储过程，函数..整个过程。

最佳答案

一种方法是将您的查询放入其中。它将拆分查询结果：

SELECT
  d.node
  , REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT(d.ancestors,',,,,'), ',', 1)),',',1)) AS `1`
  , REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT(d.ancestors,',,,,'), ',', 2)),',',1)) AS `2`
  , REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT(d.ancestors,',,,,'), ',', 3)),',',1)) AS `3`
  , REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT(d.ancestors,',,,,'), ',', 4)),',',1)) AS `4`

FROM (
  SELECT T0.title node
        ,(SELECT GROUP_CONCAT(T2.title ORDER BY T2.left)
                      FROM Tree T2
                      WHERE T2.left < T0.left AND T2.right > T0.right
                      ) ancestors
  FROM Tree T0
  GROUP BY T0.title
) AS d;

关于mysql - 嵌套集中的MySQL Dynamic Pivot，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38121594/