使用MySQL在不同字段上的多个GROUP_CONCAT [英] Multiple GROUP_CONCAT on different fields using MySQL

问题描述

我有这样的查询:

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

该查询有效，但未达到预期效果，因为如果我有一个具有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"

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"

我认为MySQL正在对查询返回的差异行进行某种类型的联合，然后将所有内容串联在一起.

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

我的预期结果是(对于最后一个示例):

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"

我可以在GROUP_CONCAT中使用DISTINCT来获得该信息，但是我还有另一个问题: 如果我有两张具有相同标题的图像，则其中一张被忽略，然后出现类似以下内容的信息:
images_id ="7,3,5"
images_title ="Title7and3，Title5"

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"

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

So I miss the relation between images_id and images_title.

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

Does somebody know if it's possible to make this query in 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.

谢谢！

推荐答案

只需添加DISTINCT.

示例:
GROUP_CONCAT(DISTINCT image.id) AS images_id

这篇关于使用MySQL在不同字段上的多个GROUP_CONCAT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！