mysql - CONCAT 类别的所有父级

Question

示例数据库(嵌套集模型中的类别):

CREATE TABLE `category` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `lft` int(11) NOT NULL,
    `rgt` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

INSERT INTO category(name, lft, rgt) values("Primary", 0, 1000);
INSERT INTO category(name, lft, rgt) values("Secondary", 1, 500);
INSERT INTO category(name, lft, rgt) values("Tertiary", 2, 20);
INSERT INTO category(name, lft, rgt) values("Tertiary2", 21, 30);

我想获取底部子类别的所有父级(可能是串联？)，如下所示:

Primary > Secondary > Tertiary
Primary > Secondary > Tertiary2

除了使用 BETWEEN lft AND rgt 和 CONCAT 之外，我还很困难，可以使用一些有关获得所需结果的提示和信息。谢谢!

SQL Fiddle

Answer 1

下面的查询怎么样？

SELECT 
  GROUP_CONCAT(parent.name SEPARATOR '/') as parent_name
FROM category AS node,
        category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.lft;

将返回请求的输出:

Primary
Primary > Secondary
Primary > Secondary > Tertiary
Primary > Secondary > Tertiary2

可测试@ http://sqlfiddle.com/#!9/4f4e97/2

如果您只想要叶节点

如果我正确地阅读了原始问题，您的预期输出应该是:

Primary > Secondary > Tertiary
Primary > Secondary > Tertiary2

通过将上面的查询稍微修改为以下内容也可以轻松实现:

SELECT 
  GROUP_CONCAT(parent.name SEPARATOR '/') as parent_name
FROM category AS node,
        category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.lft, node.rgt
HAVING node.lft = (node.rgt - 1)
ORDER BY node.lft;

但是，这假设您的所有叶节点(正如它们应该的那样)都具有 rgt = lft + 1!!

测试@ http://sqlfiddle.com/#!9/57acd4/1