我有以下3个表,我想知道正确的sql的预期结果如下。我这里的sql不工作;
select h.pid,
h.name,
sum(r.amount1) as total1,
sum(r.amount2) as total2,
count(g.pid) as times,
sum(g.take) as totaltaken
from history h
left join rpt_revenue r on h.pid=r.pid
left join guest g on g.pid=r.pid
group by h.pid, h.name;
history
pid name
1 peter
2 may
rpt_revenue
id pid amount1 amount2
1 1 10.00 11.00
2 2 20.00 20.00
3 1 2.00 2.00
4 2 2.00 2.00
guest
gid pid id take
1 1 1 2
2 1 3 2
3 2 2 3
expected result
pid total1 total2 times totaltaken
1 12.00 13.00 2 4
2 22.00 22.00 1 3 发布于 2020-10-09 02:39:49
因此,为了能够通过连接使用聚合函数,您应该首先在连接子查询中聚合数据,然后在顶级聚合所有数据
here聚合的一些示例
https://stackoverflow.com/questions/64238023
复制相似问题