mysql - 为什么这个 group_concat 不起作用？

我不明白为什么这个 GROUP_CONCAT 不起作用，就外部查询而言，返回了 3 行，所以我想以此为基础进行 group_concat，但它不喜欢它...

http://sqlfiddle.com/#!2/24764/3

CREATETABLE nested_category (
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        nameVARCHAR(20) NOTNULL,
        lft INTNOTNULL,
        rgt INTNOTNULL
);

INSERTINTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
 (4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
 (9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);

SELECTGROUP_CONCAT(rs.category_id, ',')
FROM 
    (
        SELECT node.category_id, node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) ASdepthFROM nested_category AS node,
                nested_category ASparent,
                nested_category AS sub_parent,
                (
                        SELECT node.category_id, node.name, (COUNT(parent.name) - 1) ASdepthFROM nested_category AS node,
                                nested_category ASparentWHERE node.lft BETWEEN parent.lft AND parent.rgt
                            AND node.name = 'PORTABLE ELECTRONICS'GROUPBY node.name
                        ORDERBY node.lft
                )AS sub_tree
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
                AND sub_parent.name = sub_tree.name
        GROUPBY node.name
        HAVINGdepth = 1ORDERBY node.lft
    ) as rs
GROUPBY rs.category_id

最佳答案

两件事:

首先:将 GROUP_CONCAT(rs.category_id, ',') 更改为 GROUP_CONCAT(rs.category_id)

逗号是默认分隔符，from the docs你可以看到，如果你想更改分隔符，就像 GROUP_CONCAT(rs.category_id SEPARATOR '|')

第二:删除最后一个: 按 rs.category_id 分组

如果您group by每个category_id，这意味着每个category_id都在它自己的集合中，例如group_concat 每行只有一个类别。

http://sqlfiddle.com/#!2/24764/7