sql主表查询，附带显示符合条件子表记录数量。

最新推荐文章于 2023-08-25 20:02:54 发布

刘文鹤crod

最新推荐文章于 2023-08-25 20:02:54 发布

阅读量499

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/lnm666/article/details/127870509

版权

原来的代码：

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'

优化后的代码：

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,
select sum( (select count(*) from t_organization_machinery  where organization_id=tor.id) )from t_organization tor
,count(tc2.id) as peasantSum
FROM
t_organization a
left join t_contract tc on a.id=tc.organization_id
left join t_contract tc2 on a.id=tc2.organization_id and tc2.is_peasant='01'