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

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

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 ELECTRONICSGROUPBY 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)

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

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

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

我不明白为什么这个 GROUP_CONCAT 不起作用，就外部查询而言，返回了 3 行，所以我想以此为基础进行 group_concat，但它不喜欢它... 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 ELECTRONICSGROUPBY 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) 逗号是默认分隔符，你可以看到，如果你想更改分隔符，就像 GROUP_CONCAT(rs.category_id SEPARATOR |) 第二:删除最后一个: 按 rs.category_id 分组 如果您group by每个category_id，这意味着每个category_id都在它自己的集合中，例如group_concat 每行只有一个类别。