在连接两个表的聚合时如何避免笛卡尔积?

Question

我需要连接来自两个表的聚合，但我得到一个笛卡尔积。我怎样才能避免呢?

SELECT TO_CHAR(c.date, 'YYYY-MM') AS "Year-Month",
SUM(c.amount) as c_amount,
SUM(o.amount) as o_amount
FROM c JOIN o ON c.common = o.common AND c.date = o.date
GROUP BY TO_CHAR(c.date, 'YYYY-MM')
ORDER BY TO_CHAR(c.date, 'YYYY-MM');

Answer 1

预聚合:

select c.yyyymm, c.c_amount, o.o_amount
from (select to_char(c.date, 'YYYY-MM') as yyyymm,
sum(c.amount) as c_amount
from c
group by to_char(c.date, 'YYYY-MM')
) c left join
(select to_char(c.date, 'YYYY-MM') as yyyymm,
sum(o.amount) as o_amount
from c join
o
on c.common = o.common
group by to_char(c.date, 'YYYY-MM')
) o
on c.yyyymm = o.yyyymm;