mysql - 在 MySQL 中的同一语句中使用 GROUP_CONCAT、GROUP 和 HAVING

Question

在应用程序中，有表 projects、categories 和一个处理它们之间的 n:m 关系的表(project_category_info):

现在我想获取一个类别的所有项目(我用HAVING解决这个问题)。我还需要结果集中的信息，即每个项目属于哪些类别(CONCAT_GROUP 与 GROUP 组合):

SELECT
    `projects`.*,
    `categories`.`id` AS `id`,
    GROUP_CONCAT(categories.id SEPARATOR '|||') AS `categories`,
    GROUP_CONCAT(categories.short_name SEPARATOR '|||') AS `category_names`
FROM
    `projects`
INNER JOIN
    `project_category_info` ON `project_category_info`.`project_id` = `projects`.`id`
LEFT JOIN
    `categories` ON `project_category_info`.`category_id` = `categories`.`id`
GROUP BY
    `projects`.`id`
HAVING
    (`categories`.`id` = 3)
;

结果集包含 13 行。但是，当我省略 GROUP_CONCAT 和 GROUP 时，我会多一行。为什么？什么会导致这种行为？

Answer 1

我开始相信您的查询中的逻辑是错误的。我建议尝试重写它。最好的方法是退后一步，将问题分解，然后再将其重新组合在一起。

获取某个类别的所有项目:

SELECT project_id
FROM project_category_info
WHERE category_id = 3;

现在，将其连接回category_info 表以获取这些项目的所有行:

SELECT *
FROM project_category_info
WHERE project_id IN(
   SELECT project_id
   FROM project_category_info
   WHERE category_id = 3);

您可以将其加入项目和类别以获取名称:

SELECT p.id, p.title, c.title
FROM project_category_info pci
JOIN projects p ON p.id = pci.project_
JOIN categories c ON c.id = pci.catgory_id
WHERE pci.project_id IN(
   SELECT project_id
   FROM project_category_info
   WHERE category_id = 3);

现在，您可以将 GROUP_CONAT() 添加到 c.title 列，并按 p.id 分组:

SELECT p.id, p.title, GROUP_CONCAT(c.short_name SEPARATOR '|||') AS category_names
FROM project_category_info pci
JOIN projects p ON p.id = pci.project_
JOIN categories c ON c.id = pci.catgory_id
WHERE pci.project_id IN(
   SELECT project_id
   FROM project_category_info
   WHERE category_id = 3)
GROUP BY p.id;