一些建议:
- 加入
seasons
一次。联接会导致左侧表中的行重复,因此可以通过汇总将它们相加两次sum
。如有疑问,请在没有group by
示例学校的情况下运行查询。 - 您必须将子查询与外部查询相关联,例如
inner_schools.id = outer_schools.id
- 但据我所知,您根本不需要子查询
例如:
SELECt schools.*, sum(cashflows.amount) total_branding_cashflowFROM schoolsJOIN seasonsON seasons.school_id = schools.id and seasons.year = 2010JOIN cashflowsON cashflows.season_id = seasons.id and cashflow_group_id = 12GROUP BY schools.id HAVINg total_branding_cashflow BETWEEN 50000000 AND 100000000
对于多个类别,您可以使用一个案例:
SELECt schools.*, sum(case when cashflow_group_id = 1 then cashflows.amount end) total1, sum(case when cashflow_group_id = 12 then cashflows.amount end) total12FROM schoolsJOIN seasonsON seasons.school_id = schools.id and seasons.year = 2010JOIN cashflowsON cashflows.season_id = seasons.id GROUP BY schools.id
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)