gpt4 book ai didi

mysql - 奇怪的MySQL用聚合函数连接查询结果

转载 作者:搜寻专家 更新时间:2023-10-30 20:27:18 25 4
gpt4 key购买 nike

我编写了以下join 查询以使用聚合 函数获取报告

SELECT users.id, SUM(orders.totalCost) AS bought, COUNT(comment.id) AS commentsCount, COUNT(topics.id) AS topicsCount, COUNT(users_login.id) AS loginCount, COUNT(users_download.id) AS downloadsCount 
FROM users
LEFT JOIN orders ON users.id=orders.userID AND orders.payStatus=1
LEFT JOIN comment ON users.id=comment.userID
LEFT JOIN topics ON users.id=topics.userID
LEFT JOIN users_login ON users.id=users_login.userID
LEFT JOIN users_download ON users.id=users_download.userID
GROUP BY users.id
ORDER BY bought DESC

但我不知道为什么会得到以下输出?

聚合函数的结果相互相乘!!!

我不知道为什么?

例如,对于最后一行,我希望得到以下结果

821    |    48000    |    63    |    0    |    10    |    10

enter image description here

执行EXPLAIN查询的结果如下所示

enter image description here

最佳答案

出现这种类型结果的一个原因是您正在对用户表使用左连接,结果集可能包含每个用户的重复行,因此您得到的计数比预期的多,您可以使用 DISTINCT 在计数中仅计算每个用户的唯一关联,对于 totalCost 的总和,您可以使用子选择为每个用户计算总和,而无需为用户订单重复值

SELECT 
u.id,
COALESCE(o.bought,0) bought
COUNT(DISTINCT c.id) AS commentsCount,
COUNT(DISTINCT t.id) AS topicsCount,
COUNT(DISTINCT ul.id) AS loginCount,
COUNT(DISTINCT ud.id) AS downloadsCount
FROM users u
LEFT JOIN (SELECT
userID,
SUM(totalCost) bought
FROM orders
WHERE payStatus=1
GROUP BY userID) o
ON u.id=o.userID
LEFT JOIN `comment` c ON u.id=c.userID
LEFT JOIN topics t ON u.id=t.userID
LEFT JOIN users_login ul ON u.id=ul.userID
LEFT JOIN users_download ud ON u.id=ud.userID
GROUP BY u.id
ORDER BY bought DESC

关于mysql - 奇怪的MySQL用聚合函数连接查询结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/25956765/

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