MySQL去除查询结果重复

发布时间：2020-12-14 18:05:43 所属栏目：大数据来源：网络整理

导读：出现结果重复数SQL(四表关联): SELECTCOUNT(post.ID )FROM wp_posts AS post LEFT JOINwp_term_relationships AS relationON(post.menu_order =relation.term_order) LEFT JOIN wp_term_taxonomyAS taxonomyON(relation.term_taxonomy_id = taxonomy.term_id

出现结果重复数SQL(四表关联):

SELECT
COUNT(post.ID )
FROM wp_posts AS post LEFT JOIN
wp_term_relationships AS relation
ON(post.menu_order =
relation.term_order) LEFT JOIN wp_term_taxonomy
AS taxonomy
ON(relation.term_taxonomy_id = taxonomy.term_id) LEFT JOIN
wp_terms AS
term ON(taxonomy.term_id = term.term_id)

正常的结果应该显示490条数据，但是结果显示了224941。

解决这个办法是在对应的COUNT()里面加上DISTINCT

DISTINCT这个关键字主要用于过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。

注意:它有局限性，比如吧不能对应多个目标字段，只能对应一个目标字段。

解决重复结果书的SQL如下:

SELECT
COUNT(DISTINCT post.ID)
FROM wp_posts AS post LEFT JOIN
wp_term_relationships AS relation
ON(post.menu_order =
relation.term_order) LEFT JOIN wp_term_taxonomy
AS taxonomy
ON(relation.term_taxonomy_id = taxonomy.term_id) LEFT JOIN
wp_terms AS
term ON(taxonomy.term_id = term.term_id)

参考资料:
MySQL中count函数使用方法详解:https://blog.csdn.net/qq_31135027/article/details/79858184

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!