使用 MySQL 在不同字段上的多个 GROUP_CONCAT

Multiple GROUP_CONCAT on different fields using MySQL

提问by Enrique

I have a query like this:

我有一个这样的查询：

SELECT product.id,
       GROUP_CONCAT(image.id) AS images_id,
       GROUP_CONCAT(image.title) AS images_title,
       GROUP_CONCAT(facet.id) AS facets_id
...
GROUP BY product.id

And the query works, but not as expected, because if I have a product with 5 facets and 1 image (suppose an image with id=7), then I get something like this in "images_id":
"7,7,7,7,7"
If I have 2 images (7 and 3) then I get something like:
"7,7,7,7,7,3,3,3,3,3"
and in facets I get something like:
"8,7,6,5,4,8,7,6,5,4"

并且查询有效，但不符合预期，因为如果我有一个具有 5 个方面和 1 个图像的产品（假设图像的 id=7），那么我会在“images_id”中得到类似的信息：
“7,7,7, 7,7"
如果我有 2 张图像（7 和 3），那么我会得到类似的信息：
“7,7,7,7,7,3,3,3,3,3”，
并且在各个方面，我会得到类似的信息：
“ 8,7,6,5,4,8,7,6,5,4"

I think MySQL is making some type of union of the differents rows returned by the query, and then concatenating everything.

我认为 MySQL 正在对查询返回的不同行进行某种类型的联合，然后连接所有内容。

My expected result is (for the last example):

我的预期结果是（对于最后一个例子）：

images_id = "7,3"
facets_id = "8,7,6,5,4"

images_id = "7,3"
facets_id = "8,7,6,5,4"

I can obtain that using DISTINCT in the GROUP_CONCAT, but then I have another problem: If I have two images with the same title, one of them is ommited, and then I get something like:
images_id = "7,3,5"
images_title = "Title7and3,Title5"

我可以在 GROUP_CONCAT 中使用 DISTINCT 获得它，但是我有另一个问题：如果我有两个具有相同标题的图像，其中一个被省略，然后我得到类似的信息：
images_id = "7,3,5"
images_title = "Title7and3,Title5"

So I miss the relation between images_id and images_title.

所以我想念images_id 和images_title 之间的关系。

Does somebody know if it's possible to make this query in MySQL?

有人知道是否可以在 MySQL 中进行此查询？

Maybe I'm complicating everything without any real benefits. I'm trying to execute only one query because performance, but now I'm not so sure if it's even faster to execute two queries (one for selecting the facets and another for the images for example).

也许我把一切都复杂化了，没有任何真正的好处。我试图只执行一个查询，因为性能，但现在我不太确定执行两个查询是否更快（例如，一个用于选择方面，另一个用于图像）。

Please explain what do you think is the best solution for this and why.

请解释您认为最好的解决方案是什么以及为什么。

Thanks !

谢谢 ！

回答by romrom

Just add DISTINCT.

只需添加DISTINCT.

Example:
GROUP_CONCAT(DISTINCT image.id) AS images_id

例子：
GROUP_CONCAT(DISTINCT image.id) AS images_id

回答by Bohemian

You'll need to get each group separately:

您需要分别获取每个组：

SELECT
    p.id,
    images_id,
    images_title,
    facets_id,
    ...
FROM PRODUCT p
JOIN (SELECT product.id, GROUP_CONCAT(image.id) AS images_id
      FROM PRODUCT GROUP BY product.id) a on a.id = p.id
JOIN (SELECT product.id, GROUP_CONCAT(image.title) AS images_title
      FROM PRODUCT GROUP BY product.id) b on b.id = p.id
JOIN (SELECT product.id, GROUP_CONCAT(facet.id) AS facets_id
      FROM PRODUCT GROUP BY product.id) b on c.id = p.id
...

回答by Sahir Ali

You can add just the DISTINCT keyword, you'll get your desire results.

您可以只添加 DISTINCT 关键字，您将获得您想要的结果。

SELECT tb_mod.*, tb_van.*,
GROUP_CONCAT(DISTINCT tb_voil.vt_id) AS voil,
GROUP_CONCAT(DISTINCT tb_other.oa_id) AS other, 
GROUP_CONCAT(DISTINCT tb_ref.rp_id) AS referral
FROM cp_modules_record_tbl tb_mod 
LEFT JOIN cp_vane_police_tbl tb_van ON tb_van.mr_id= tb_mod.id
LEFT JOIN cp_mod_voilt_tbl tb_voil ON tb_voil.mr_id= tb_mod.id
LEFT JOIN cp_mod_otheraction_tbl tb_other ON tb_other.mr_id= tb_mod.id
LEFT JOIN cp_mod_referral_tbl tb_ref ON tb_ref.mr_id= tb_mod.id
WHERE tb_mod.mod_type = 2 GROUP BY tb_mod.id

回答by Will Sheppard

If the issue is speed, then it may be a lot faster to simply select all the data you need as separate rows, and do the grouping in the application, i.e.:

如果问题是速度，那么简单地选择您需要的所有数据作为单独的行，并在应用程序中进行分组可能要快得多，即：

SELECT product.id, image.id, image.title, facet.id

Then in the application:

然后在应用程序中：

foreach row:
    push product_id onto list_of_product_ids
    push image_id onto list_of_image_ids
etc.