我有这两个表;
- 行程
id | |||
---|---|---|---|
1 | 2020年1月1日 | 3000 | |
3 | 2020年1月2日 | >td style="text-align:left;">4000||
4 |
您可以按行程预聚合费用,然后在外部查询中再次聚合:
select t.date, sum(t.revenue) as revenue, coalesce(sum(e.expense), 0) as expense
from trips t
left join (
select tripid, sum(amount) as expense
from expenses
group by tripid
) e on e.tripid = t.id
group by t.date