MySQL：子查询中的值总和

一些建议：

• 加入

  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