gpt4 book ai didi

mysql - 使用单个列对多个表进行内连接,对结果值产生 'multiplier' 影响

转载 作者:行者123 更新时间:2023-11-29 09:56:14 26 4
gpt4 key购买 nike

尝试在 4 个表(项目、任务、支出和捐赠)上运行简单的内连接查询。连接似乎在某处缠结。例如,SUM(tasks.budget_amount) 应该给出 3,211,385.21 的组合值,但我得到的是 49,659,564.60

SELECT projects.id, projects.name, SUM(tasks.budget_amount) as budget, SUM(spendings.amount_spent) AS spending, SUM(donations.donation_amount) AS donation 
FROM (((projects
INNER JOIN tasks on tasks.project_id=projects.id)
INNER JOIN spendings on spendings.task_id=tasks.id)
INNER JOIN donations on donations.task_id=tasks.id)
GROUP BY projects.id

有办法防止这种情况发生吗?

最佳答案

从 SELECT 列表 ( SUM ) 中删除聚合表达式并删除 GROUP BY ,返回每个表的主键/唯一标识符,看看发生了什么。

我们将看到的是半笛卡尔积(或叉积),一个表中的多行与另一个表中的多行相匹配。 SQL 规范是返回所有匹配项。

<小时/>

避免这种情况的一种方法是通过预先聚合结果,然后进行连接来避免叉积。

举个例子:

SELECT p.id
, p.name
, IFNULL(b.budget,0) AS budget
, IFNULL(s.spending,0) AS spending
, IFNULL(d.donation,0) AS donation
FROM projects p
LEFT
JOIN ( SELECT bt.project_id
, SUM(bt.budget_amount) AS budget
FROM tasks bt
GROUP BY bt.project_id
) b
ON b.project_id = p.id
LEFT
JOIN ( SELECT st.project_id
, SUM(ss.amount_spent) AS spending
FROM tasks st
JOIN spendings ss
ON ss.task_id = st.id
GROUP BY st.project_id
) s
ON s.project_id = p.id
LEFT
JOIN ( SELECT dt.project_id
, SUM(dd.donation_amount) AS donation
FROM tasks dt
JOIN donations dd
ON dd.task_id = dt.id
GROUP BY dt.project_id
) d
ON d.project_id = p.id

请注意,每个内联 View d , s ,和b保证返回 project_id 的唯一值。

关于mysql - 使用单个列对多个表进行内连接,对结果值产生 'multiplier' 影响,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/53783862/

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