我有一个表(“转储”)与交易,我想列出按类别,按月分组的总金额,如:月|类别|类别ID |和 . 涉及的表格如下所示:
TABLE dump:
id INT
date DATE
event VARCHAR(100)
amount DECIMAL(10, 2)
TABLE dump_cat:
id INT
did INT (id in dump)
cid INT (id in categories)
TABLE categories:
id INT
name 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;
并在该表上进行查询,一切正常,总和正常 . 有没有办法在原始查询中执行此操作,使用子查询或类似的东西?