gpt4 book ai didi

mysql - 当 LEFT JOINing 两个不同的表时,GROUP CONCAT concats 太多重复值

转载 作者:行者123 更新时间:2023-11-29 04:38:12 32 4
gpt4 key购买 nike

我有一个查询,用于获取我离开加入两个表的帖子:

类别和标签:LEFT JOIN 到链接表→‖INNER JOIN 到类别和标签名称表。

LEFT JOIN wp_term_relationships AS term_link ON wpp.ID = term_link.object_id 
INNER JOIN wp_terms AS terms ON term_link.term_taxonomy_id = terms.term_id

作为帖子元的额外帖子详细信息:

LEFT JOIN wp_postmeta AS postmeta ON wpp.ID = postmeta.post_id

现在。我想用逗号在列表中显示所有类别,使用 MySQL GROUP_CONCAT:

GROUP_CONCAT(terms.name SEPARATOR ', ') AS allcatname

但是,问题是,由于我对 wp_postmeta 的第二次 LEFT JOIN,以某种方式为要找到的每个 wp_postmeta 记录复制了所有类别。所以我的“allcatname”看起来像这样:

drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drink

所以发生了...
我想保留 GROUP_CONCAT 的原因是因为我需要它通过“HAVING”进行过滤。 因此,我正在寻找一种方法来仅显示链接到每个帖子的每个类别记录的一个类别:让组 concat 显示。例如。 “饮料、汽水、可乐”,如果它有这三个类别。

这是完整的查询:

SELECT
SQL_CALC_FOUND_ROWS
wpp.ID, wpp.post_title, wpp.post_author,
wpp.post_status,
GROUP_CONCAT(terms.name SEPARATOR ', ') AS allcatname
FROM wp_posts AS wpp

LEFT JOIN wp_term_relationships AS term_link ON wpp.ID = term_link.object_id
INNER JOIN wp_terms AS terms ON term_link.term_taxonomy_id = terms.term_id
LEFT JOIN wp_postmeta AS postmeta ON wpp.ID = postmeta.post_id

WHERE wpp.post_type = 'post'
AND wpp.post_warning <> 'no_image'
AND wpp.post_status <> 'trash'
AND wpp.post_status <> 'auto-draft'
AND (post_title LIKE '%$search_string%' OR postmeta.meta_value LIKE '%$search_string%')
GROUP BY wpp.ID
ORDER BY post_date DESC
LIMIT 20

最佳答案

使用:

GROUP_CONCAT(DISTINCT terms.name SEPARATOR ', ') AS allcatname

代替:

GROUP_CONCAT(terms.name SEPARATOR ', ') AS allcatname

关于mysql - 当 LEFT JOINing 两个不同的表时,GROUP CONCAT concats 太多重复值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36258399/

32 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com