php - SQL SUM() All with check 来自其他表

Question

表 con_projects

mysql> SELECT * FROM con_projects;
+----+---------------+---------------------+--------+
| id | project_name  | project_description | status |
+----+---------------+---------------------+--------+
|  1 | Project 1     | Description IS Here |      1 |
|  2 | Project 2     | Description IS Here |      0 |
|  3 | Project 3     | Description IS Here |      1 |
|  4 | Project 4     | Description IS Here |      0 |
|  5 | Project 5     | Description IS Here |      1 |
+----+---------------+---------------------+--------+

这里状态 1=active 和 0=inactive

mysql> SELECT * FROM con_transactions;
+------+------+---------------------+--------+
| t_id | p_id | date                | amount |
+------+------+---------------------+--------+
|   10 |    1 | 2016-02-17 19:24:05 | 1800   |
|   12 |    2 | 2016-02-18 11:40:13 | 200    |
|   17 |    3 | 2016-02-18 11:42:04 | 100    |
|   19 |    4 | 2016-02-18 11:45:43 | 1      |
|   20 |    5 | 2016-02-18 11:45:54 | 999    |
|   21 |    1 | 2016-02-18 11:46:02 | 1500   |
|   41 |    2 | 2016-02-18 17:23:14 | 500    |
|   42 |    3 | 2016-02-18 17:23:14 | 500    |
|   43 |    4 | 2016-02-18 17:23:15 | 500    |
|   44 |    5 | 2016-02-18 17:23:16 | 500    |
|   45 |    1 | 2016-02-18 17:23:16 | 500    |
|   46 |    2 | 2016-02-18 17:23:16 | 500    |
|   47 |    3 | 2016-02-18 17:23:17 | 500    |
|   48 |    4 | 2016-02-18 17:23:17 | 500    |
|   49 |    5 | 2016-02-18 17:23:18 | 500    |
|   50 |    1 | 2016-02-18 17:25:54 | 1000   |
|   51 |    1 | 2016-02-18 17:26:22 | 3000   |
|   52 |    2 | 2016-02-18 17:48:59 | 10     |
|   53 |    1 | 2016-02-18 17:48:59 | 10     |
|   55 |    1 | 2016-02-19 10:20:12 | 1000   |
+------+------+---------------------+--------+

现在我想 SUM() 来自 con_trnsactions 的所有金额，其 p_id 来自 con_projects status = 1

我试过这个还有很多

mysql> SELECT (SELECT SUM(t.amount) FROM con_transactions t WHERE p.id=t.p_id) as total FROM con_projects p WHERE status='1'
+-------+
| total |
+-------+
| 14120 |
+-------+

和

mysql> SELECT SUM(amount) from con_transactions;
+-------------+
| SUM(amount) |
+-------------+
|       14120 |
+-------------+

两者相同。但是减法会是这样的。

mysql> SELECT 1800+100+999+1500+500+500+500+500+500+1000+3000+10+1000;
+---------------------------------------------------------+
| 1800+100+999+1500+500+500+500+500+500+1000+3000+10+1000 |
+---------------------------------------------------------+
|                                                   11909 |
+---------------------------------------------------------+

这里是所有活跃的项目 (con_trsaction.p_id=con_projects.id AND con_projects.status=1) id (p_id) 来自 con_trsanction.amount

Answer 1

试试这个:

SELECT SUM(t.amount) 
FROM con_transactions t 
INNER JOIN con_projects AS p ON t.p_id = p.id
WHERE p.status = 1

这是一个简单的连接操作。只有“事件”项目才会计入总和。