sql - 在 mysql 的不同行上获取 SUM()

Question

我有一个包含交易的表(“转储”)，我想列出每月按类别分组的总金额，例如:月 |类别 |类别编号 |和。涉及的表如下所示:

TABLE dump:id INTdate DATEevent VARCHAR(100)amount DECIMAL(10, 2)

TABLE dump_cat:id INTdid INT (id in dump)cid INT (id in categories)

TABLE categories:id INTname VARCHAR(100)

现在我尝试使用的查询是:

SELECT SUBSTR(d.date,1,7) AS month, c.name, c.id AS catid, SUM(d.amount) AS sum FROM dump as d, dump_cat as dc, categories AS c WHERE dc.did = d.id AND c.id = dc.cid AND SUBSTR(d.date, 1, 7) >= '2008-08' GROUP BY month, c.name ORDER BY month;

但大多数类别的总和是应有的两倍。我的猜测是，这是因为连接返回多行，但在字段部分添加“DISTINCT d.id”没有任何区别。查询返回的示例是:

+---------+--------------------------+-------+-----------+| month   | name                     | catid | sum       |+---------+--------------------------+-------+-----------+| 2008-08 | Cash                     |    21 |  -6200.00 | | 2008-08 | Gas                      |     8 |  -2936.19 | | 2008-08 | Rent                     |     1 | -15682.00 | 

在哪里

SELECT DISTINCT d.id, d.amount FROM dump AS d, dump_cat AS dc WHERE d.id = dc.did AND SUBSTR(d.date, 1, 7) ='2008-08' AND dc.cid = 21;

返回

+------+----------+| id   | amount   |+------+----------+| 3961 |  -600.00 | | 2976 |  -200.00 | | 2967 |  -400.00 | | 2964 |  -200.00 | | 2957 |  -300.00 | | 2962 | -1400.00 | +------+----------+

总计 3100，是上面所列总和的一半。如果我从最后一个查询中删除“DISTINCT d.id”，每一行都会列出两次。我认为这是问题所在，但我需要帮助才能弄清楚如何解决它。提前致谢。

补充:如果我将 dump 和 dump_cat 表合二为一，用

CREATE table dumpwithcat SELECT DISTINCT d.id, d.date, d.event, d.amount, dc.cid  FROM dump AS d, dump_cat AS c WHERE c.did = d.id;

然后对该表进行查询，一切正常且总和正确。有没有办法在原始查询中使用子查询或类似的东西来做到这一点？

Answer 1

That makes a total of 3100, half of the sum listed above. If I remove "DISTINCT d.id" from the last query, every row is listed twice.

虽然每个转储可能只有一个类别，因此每个转储在dump_cat 中必须有多个行。您应该考虑定义一个 UNIQUE 约束，以确保每对 did、cid 只存在一行:

ALTER TABLE dump_cat ADD CONSTRAINT UNIQUE (did, cid);

鉴于您表中的当前数据，我预计此语句将失败。当这些列已经包含重复项时，它无法创建唯一约束!

您可以通过这种方式删除重复项，例如:

DELETE dc1 FROM dump_cat dc1 JOIN dump_cat dc2 USING (did, cid)
WHERE dc1.id > dc2.id; -- only delete the second duplicate entry

---

编辑:顺便说一句，在您确认我的问题是正确的之前，不要将我的问题标记为已接受! :-)

您可以按照我的建议使用如下查询来验证是否确实存在重复项:

SELECT did, COUNT(*)
FROM dump_cat
GROUP BY did
HAVING COUNT(*) > 1;

另一种可能性:您有多个同名类别？ (对不起，我第一次尝试这个查询是错误的，这是一个编辑过的版本)

SELECT c.name, GROUP_CONCAT(c.id) AS cat_id_list, COUNT(*) AS c
FROM category c
GROUP BY c.name
HAVING COUNT(*) > 1;

---

FWIW，我确实测试了我展示的 DELETE 命令:

INSERT INTO dump_cat (did, cid) VALUES (1, 2), (3,4), (3,4); -- duplicates!

DELETE dc1 FROM dump_cat dc1 JOIN dump_cat dc2 USING (did, cid) WHERE dc1.id > dc2.id
Query OK, 1 row affected (0.00 sec)

---

PS:这与您的问题无关，但是 DISTINCT 查询修饰符始终适用于整行，而不仅仅是第一列。这是很多SQL程序员的一个普遍误解。