使用GROUP BY和WHERE对两个表中的值求和

Question

下面有两个名为sent_table和received_table的表。我正试图在查询中将它们混合在一起以实现output_table。到目前为止，我所有的尝试都导致了大量的重复和完全伪造的和值。

我假设我需要使用GROUP BY和WHERE来实现这个目标。我希望能够根据用户名进行筛选。

sent_table

+----+------+-------+----------+
| id | name | value | order_id |
+----+------+-------+----------+
|  1 | dave |   100 |        1 |
|  2 | dave |   200 |        1 |
|  3 | dave |   300 |        2 |
+----+------+-------+----------+

接收表

+----+------+-------+----------+
| id | name | value | order_id |
+----+------+-------+----------+
|  1 | dave |   400 |        1 |
|  2 | dave |   500 |        2 |
|  3 | dave |   600 |        2 |
+----+------+-------+----------+

输出表

+------+----------+----------+
| sent | received | order_id |
+------+----------+----------+
|  300 |      400 |        1 |
|  300 |     1100 |        2 |
+------+----------+----------+

我尝试了以下内容，但没有感到高兴。这并没有对我希望如何解决这个问题施加任何限制。这正是我试图做到的。

SELECT *
FROM
( select SUM(value) as sent, order_id FROM sent_table WHERE name='dave' GROUP BY order_id) A
CROSS JOIN 
( select SUM(value) as received, order_id FROM received_table WHERE name='dave' GROUP BY order_id) B

如有任何帮助，我们将不胜感激。

Answer 1

对每个表求和，按order_id分组，然后将结果相加。即使缺少一侧，也要获取行，请执行FULL OUTER JOIN:

SELECT COALESCE(s.order_id, r.order_id) AS order_id, s.sent, r.received
FROM (
SELECT order_id, SUM(value) AS sent
FROM sent
GROUP BY order_id
) s
FULL OUTER JOIN (
SELECT order_id, SUM(value) AS received
FROM received
GROUP BY order_id
) r
USING (order_id)
ORDER BY 1

结果：

| order_id | sent | received |
| -------- | ---- | -------- |
| 1        | 300  | 400      |
| 2        |      | 1100     |

注意order_id上的COALESCE，这样，如果sent中缺少它，它将从recevied中获取，这样该值就永远不会为NULL。

如果您想用0来代替NULL(例如，当发送或接收中都没有该order_id的记录时(，您可以使用COALESCE(s.sent, 0) AS sent, COALESCE(r.received, 0) AS received。

https://www.db-fiddle.com/f/nq3xYrcys16eUrBRHT6xLL/2