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

在应用程序中，有一个表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时，我又得到了一行。为什么？什么会导致这种行为？

最佳答案

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

要获得一个类别的所有项目：

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;