具有JOIN时的SUM MySQL列

Question

我在尝试从MySQL中对结果集进行求和时遇到了一个新手问题。

有2张桌子：

formalities
- id
- total
- group_id
- book_id
- status_id
payments
- id
- amount
- formality_id (FK)

考虑形式上的1:N关系，其中1个formality可以有1个或多个payments。我需要通过两种方式获得总和：

第一个，通过从formalities.total获得总数，它按预期工作：

SELECT SUM(formalities.total)
FROM formalities
WHERE group_id = 2 AND book_id = 23 AND status_id IN (1,3)

第二个，通过从payments.amount:中获得总数

SELECT
SUM(f.amount) AS total
FROM
(
SELECT
payments.amount
FROM payments
JOIN formalities ON payments.formality_id = formalities.id AND formalities.group_id = 2 AND formalities.book_id = 23 AND formalities.status_id IN (1,3)
GROUP BY payments.id
) f

FYI:

• 所有付款都与手续有关
• 所有手续至少有一笔付款
• formalities.total总是等于其payments.amount的总和

我想知道我的第二个查询出了什么问题，因为总数不匹配。

顺便说一句，我正在处理一个最近迁移的数据库，所以这可能与错误的迁移有关，但我需要确保我的查询符合我的要求。

Answer 1

好吧，派生表f中的查询无效。payments.amount必须在GROUP BY子句中，或者传递给聚合函数。旧的MySQL或配置错误的MySQL版本确实允许在没有进一步警告的情况下打破该规则。但它可能会产生有趣的结果，甚至可能无法重现。

SELECT sum(p.amount)
FROM formalities f
LEFT JOIN payments p
ON p.formality_id = f.id
WHERE f.group_id = 2
AND f.book_id = 23
AND f.status_id IN (1, 3);

就我理解你的问题而言，应该给你想要的结果。

还有一件事：似乎formalities.total是为了保持该形式的所有payments.amount的和。如果是这样，请删除列formalities.total。它是多余的，可能会导致不一致——可能是你现在正在经历的。总额不应具体化。为了方便起见，您可以创建一个包含总数的视图。大致如下：

CREATE VIEW formalities_with_total
AS
SELECT f.id,
sum(p.amount) total,
f.group_id,
f.book_id,
f.status_id
FROM formalities f
LEFT JOIN payments p
ON p.formality_id = f.id
GROUP BY f.id,
f.group_id,
f.book_id,
f.status_id;