原来的sql:
SELECT count(a.id)as total,count(if(a.organization_key!=4,1,null)) as criterionNum, count(if(a.organization_key=4,1,null)) as generalNum,sum(tc.service_area) as areaSum, SUM(tom2.cc) machinerySum ,count(tc2.id) as peasantSum FROM t_organization a left join t_contract tc on a.id=tc.organization_id left join ( select count(1)cc,tom.organization_id from t_organization_machinery tom JOIN t_organization tor on tom.organization_id=tor.id GROUP BY tom.organization_id ) tom2 on tom2.organization_id=a.id left join t_contract tc2 on a.id=tc2.organization_id and tc2.is_peasant='01'
优化后的sql:
SELECT count(a.id)as total,count(if(a.organization_key!=4,1,null)) as criterionNum, count(if(a.organization_key=4,1,null)) as generalNum,sum(tc.service_area) as areaSum, SUM((select count(*) from t_organization_machinery where organization_id=a.id)) machinerySum ,count(if(tc.is_peasant='01',1,null)) as peasantSum FROM t_organization a left join t_contract tc on a.id=tc.organization_id