I have the two following tables:
我有以下两个表:
content:
========
cid | iid | qty
---------------
1 | 7 | 42
2 | 7 | 1
3 | 8 | 21
ret:
====
rid | cid | qty
--------------
1 | 1 | 2
2 | 1 | 10
3 | 2 | 1
I would like to retrieve, for each iid, the sum of content.qty and ret.qty For exemple, for given tables, the result would be:
我想为每个iid检索content.qty和ret.qty的总和例如,对于给定的表,结果将是:
iid=7, SUM(content.qty) = 43, SUM(ret.qty)=13
iid = 7,SUM(content.qty)= 43,SUM(ret.qty)= 13
iid=8, SUM(content.qty) = 21, SUM(ret.qty)=0
iid = 8,SUM(content.qty)= 21,SUM(ret.qty)= 0
Is there any way to do it in one query?
有没有办法在一个查询中执行此操作?
In advance, thank you!
提前谢谢!
1 个解决方案
#1
0
This is a bit complicated, because you don't want duplicates in your sums. To fix that problem, do the aggregations separately as subqueries. The first is directly on content the second joins back to content from ret to get the iid column.
这有点复杂,因为您不需要在总和中重复。要解决该问题,请将聚合作为子查询单独进行。第一个是直接内容,第二个连接到ret的内容以获取iid列。
The following query follows this approach, and assumes that cid is a unique key on content:
以下查询遵循此方法,并假定cid是内容上的唯一键:
select c.iid, c.qty + coalesce(r.qty, 0)
from (select c.iid, SUM(qty) as cqty
from content c
group by c.iid
) c left outer join
(select c.iid, SUM(r.qty) as rqty
from ret r join
content c
on r.cid = c.cid
group by c.iid
) r
on c.iid = r.iid;
#1
0
This is a bit complicated, because you don't want duplicates in your sums. To fix that problem, do the aggregations separately as subqueries. The first is directly on content the second joins back to content from ret to get the iid column.
这有点复杂,因为您不需要在总和中重复。要解决该问题,请将聚合作为子查询单独进行。第一个是直接内容,第二个连接到ret的内容以获取iid列。
The following query follows this approach, and assumes that cid is a unique key on content:
以下查询遵循此方法,并假定cid是内容上的唯一键:
select c.iid, c.qty + coalesce(r.qty, 0)
from (select c.iid, SUM(qty) as cqty
from content c
group by c.iid
) c left outer join
(select c.iid, SUM(r.qty) as rqty
from ret r join
content c
on r.cid = c.cid
group by c.iid
) r
on c.iid = r.iid;