mysql 多表查询数据重复的问题

最新推荐文章于 2022-11-28 16:10:52 发布

xiaolin_2016

最新推荐文章于 2022-11-28 16:10:52 发布

阅读量2.5k

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/xiaolin_2016/article/details/105041634

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

废话不多说直接贴SQL语句

SELECT
	moments.id AS id,
	COUNT(endorse.id) AS endorseCount,
	COUNT(reply.id) AS replyCount
FROM
	jq_circle_moments moments
LEFT JOIN jq_circle_endorse endorse ON moments.id = endorse.moments_id
LEFT JOIN jq_circle_reply reply ON moments.id = reply.moments_id
WHERE
	moments.`status` = "10B"
AND moments.id = "090bf03723fd4abba56dfecfa69ac410"
GROUP BY
	moments.id
LIMIT 10

结果是：

id	endorseCount	replyCount
090bf03723fd4abba56dfecfa69ac410	9	9

然后我在执行

select COUNT(id) FROM jq_circle_endorse where moments_id = "090bf03723fd4abba56dfecfa69ac410";

select COUNT(id) FROM jq_circle_reply where moments_id = "090bf03723fd4abba56dfecfa69ac410";

分别结果是：

COUNT(id)

然后我就纳闷了，为什么应该是9应该是3，后面灵机一动加了个去重的关键字DISTINCT

SELECT
	moments.id AS id,
	COUNT(DISTINCT endorse.id) AS endorseCount,
	COUNT(DISTINCT reply.id) AS replyCount
FROM
	jq_circle_moments moments
LEFT JOIN jq_circle_endorse endorse ON moments.id = endorse.moments_id
LEFT JOIN jq_circle_reply reply ON moments.id = reply.moments_id
WHERE
	moments.`status` = "10B"
AND moments.id = "090bf03723fd4abba56dfecfa69ac410"
GROUP BY
	moments.id
LIMIT 10

自从有了mybatis-plus SQL语句都写的少了