mysql - 在查询中使用 GROUP_CONCAT 未获得正确的结果

Question

我有 7 个表可以在查询中使用:

tb_post, tb_spots, users, td_sports, tb_spot_types, tb_users_sports, tb_post_media

这是我正在使用的查询:

SELECT po.id_post AS id_post, 
po.description_post as description_post,
 sp.id_spot as id_spot, 
po.date_post as date_post,
 u.id AS userid, 
u.user_type As tipousuario, 
u.username AS username, 
spo.id_sport AS sportid, 
spo.sport_icon as sporticon, 
st.logo_spot_type as spottypelogo, 
sp.city_spot AS city_spot, 
sp.country_spot AS country_spot, 
sp.latitud_spot as latitudspot,
 sp.longitud_spot as longitudspot,
 sp.short_name AS spotshortname, 
sp.verified_spot AS spotverificado, 
 u.profile_image AS profile_image,
sp.verified_spot_by as spotverificadopor,
 uv.id AS spotverificador,
 uv.user_type AS spotverificadornivel,
 pm.media_type AS mediatype,
 pm.media_file AS mediafile,

GROUP_CONCAT(tus.user_sport_sport) sportsdelusuario,
GROUP_CONCAT(logosp.sport_icon) sportsdelusuariologos,
GROUP_CONCAT(pm.media_file) mediapost,
GROUP_CONCAT(pm.media_type) mediaposttype

FROM tb_posts po 

LEFT JOIN tb_spots sp ON po.spot_post = sp.id_spot 
LEFT JOIN users u ON po.uploaded_by_post = u.id 
LEFT JOIN tb_sports spo ON sp.sport_spot = spo.id_sport
LEFT JOIN tb_spot_types st ON sp.type_spot = st.id_spot_type
LEFT JOIN users uv ON sp.verified_spot_by = uv.id
LEFT JOIN tb_users_sports tus ON tus.user_sport_user = u.id
LEFT JOIN tb_sports logosp ON logosp.id_sport = tus.user_sport_sport
LEFT JOIN tb_post_media pm  ON pm.media_post = po.id_post 

WHERE po.status = 1
GROUP BY po.id_post,uv.id

我在使用某些 GROUP_CONCAT 组时遇到问题:

GROUP_CONCAT(tus.user_sport_sport) sportsdelusuario 给了我正确的项目，但重复了，所有项目两次

GROUP_CONCAT(logosp.sport_icon) sportsdelusuariologos 给了我正确的项目，但重复了，所有项目两次

GROUP_CONCAT(pm.media_file) mediapost 给了我正确的项目，但重复了四次

GROUP_CONCAT(pm.media_type) mediaposttype 给了我正确的项目，但重复了四次

如果您需要的话，我可以将所有表结构放在这里。

Answer 1

查询中连接的多个一对多关系对聚合结果有乘法影响；标准解决方案是子查询:

你可以改变

GROUP_CONCAT(pm.media_type) mediaposttype
...
LEFT JOIN tb_post_media pm  ON pm.media_post = po.id_post 

至

pm.mediaposttype
...
LEFT JOIN (
   SELECT media_post, GROUP_CONCAT(media_type) AS mediaposttype 
   FROM tb_post_media 
   GROUP BY media_post
) AS pm ON pm.media_post = po.id_post 

如果 tb_post_media 非常大，并且外部查询中的 po.status = 1 条件会显着减少子查询的结果，则值得在子查询中复制原始联接以进行过滤记录下结果。

同样，如果外部查询的结果相对较少，我在评论中提到的相关版本也可以提高性能。 (如果您实际上只使用后者的很少一部分结果，那么单独计算每个 GROUP_CONCAT() 的成本比一次性计算它的成本要低)。