类别表
mysql> SELECT * FROM cats;
+------+-----------+
| c_id | c_name |
+------+-----------+
| 1 | cats 1 |
| 2 | cats 2 |
| 3 | cats 3 |
+------+-----------+
元表
mysql> SELECT * FROM meta;
+------+------+----------+-------------+-------+
| m_id | c_id | name | description | costs |
+------+------+----------+-------------+-------+
| 1 | 1 | Abhijit1 | description | 100 |
| 2 | 1 | Abhijit2 | description | 200 |
| 3 | 2 | Abhijit3 | description | 500 |
| 4 | 3 | Abhijit4 | description | 800 |
+------+------+----------+-------------+-------+
元和猫表常见的是c_id元表猫 c_id(1( 元表有 2 行 (Abhijit1,Abhijit2( 行与 m_id(1,2(
交易表
mysql> SELECT * FROM transactions;
+------+------+------------+--------+
| t_id | m_id | date | amount |
+------+------+------------+--------+
| 1 | 1 | 2016-02-01 | 50 |
| 2 | 1 | 2016-02-06 | 50 |
| 3 | 3 | 2016-02-15 | 400 |
| 4 | 4 | 2016-02-19 | 150 |
+------+------+------------+--------+
事务和元表通用是m_idm_id 1 的事务有 2 行 t_id(1,2(此表主要针对已付款金额和日期
我想为每个类别求和((所有成本(来自元表(和金额(来自交易表(。
表与
cats.c_id
|
|-----> meta.c_id
|-----> meta.m_id
|-----> transactions.m_id
错了。猫 id 1 的成本是 300,但这里我得到了 400
I Want Get Return From Query Like This:
+------+-----------+--------------+---------------+
| c_id | c_name | SUM(m.costs) | SUM(t.amount) |
+------+-----------+--------------+---------------+
| 1 | cats 1 | 300 | 100 |
| 2 | cats 2 | 500 | 400 |
| 3 | cats 3 | 800 | 150 |
+------+-----------+--------------+---------------+
这里的 SUM(m.cost( 都是一个类别的成本,而 SUM(t.amount( 都是为一个类别支付的
请帮助我或任何更好的表格管理想法。
问题源于您加入transactions
以获得SUM(t.amount)
的事实。因此,如果单个meta
记录与两个transactions
记录相关联,则costs
值将考虑两次。
您可以使用相关的子查询来解决此问题SUM(t.amount)
:
SELECT c.c_id,
c.c_name,
SUM(m.costs),
(SELECT SUM(t.amount)
FROM transactions AS t
WHERE m.m_id = t.m_id)
FROM cats AS c
LEFT JOIN meta AS m ON c.c_id = m.c_id
GROUP BY c.c_id, c.c_name
输出:
c_id c_name SUM(m.costs) SUM(t.amount)
-----------------------------------------
1 cats 1 300 100
2 cats 2 500 400
3 cats 3 800 150
在这里演示
试试这个
SELECT cats.c_id,
cats.c_name,
(SELECT SUM(m.cost) FROM meta WHERE cats.c_id = m.c_id) AS cost,
(SELECT SUM(t.cost) FROM meta WHERE t.id = m.id) AS amount
WHERE c.c_id = 1