我在一个查询中有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给了我正确的项目,但却重复了四次。
如果你需要的话,我可以把所有的表格结构都放在这里。
发布于 2019-06-06 04:15:22
查询中的多个一对多关系JOINed会对聚合结果产生乘性影响;标准解决方案是子查询:
你可以改变
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()比一次性计算它的开销要小)。
发布于 2019-06-06 04:30:41
或者仅将DISTINCT添加到所有group_concat,例如GROUP_CONCAT(DISTINCT pm.media_type)
https://stackoverflow.com/questions/56466964
复制相似问题