Select a.factoryshort,b.factorycode,b.abc From [factoryData] As a Left Join (select factorycode,sum(employcitycount) as abc from EmployRecords group by factorycode) b On a.factorycode=b.factorycode试试
调试欢乐多
,企业编码=a.factoryCode
,企业人数=a.factoryPersonCount
,录用备案=isnull(录用备案,0)
,就业证=isnull(就业证,0)
,合同签证=isnull(合同签证,0)
,新签=isnull(新签,0)
,汇总=isnull(录用备案,0)+isnull(就业证,0)+isnull(合同签证,0)+isnull(新签,0)
from factoryData a
left join(
select factoryCode,录用备案=sum(EmployCityCount+EmployCountryCount)
from EmployRecords group by factoryCode
) b on a.factoryCode=b.factoryCode
left join(
select factoryCode,就业证=sum(WorkingCity+WorkingProvince+WorkingOtherProvince)
from WorkingVisa group by factoryCode
) c on a.factoryCode=c.factoryCode
left join(
select factoryCode,合同签证=sum(BargainNewVisa+BargainOldVisa),新签=sum(BargainNewVisa)
from BargainVisa group by factoryCode
) d on a.factoryCode=d.factoryCode
,企业编码=a.factoryCode
,企业人数=a.factoryPersonCount
,录用备案=isnull(b.录用备案,0)
,就业证=isnull(c.就业证,0)
,合同签证=isnull(d.合同签证,0)
,新签=isnull(d.新签,0)
,汇总=isnull(b.录用备案,0)+isnull(c.就业证,0)+isnull(d.合同签证,0)+isnull(d.新签,0)
from factoryData a
left join(
select factoryCode,录用备案=sum(EmployCityCount+EmployCountryCount)
from EmployRecords group by factoryCode
) b on a.factoryCode=b.factoryCode
left join(
select factoryCode,就业证=sum(WorkingCity+WorkingProvince+WorkingOtherProvince)
from WorkingVisa group by factoryCode
) c on a.factoryCode=c.factoryCode
left join(
select factoryCode,合同签证=sum(BargainNewVisa+BargainOldVisa),新签=sum(BargainNewVisa)
from BargainVisa group by factoryCode
) d on a.factoryCode=d.factoryCode
factoryData.factoryShort as 企业简称,
factoryData.factoryPersonCount as 企业人数,
EmployCityCount +EmployCountryCount as 录用备案,WorkingProvince+WorkingOtherProvince as 就业证,
BargainNewVisa as 新签人数,
BargainNewVisa+BargainOldVisa as 汇总人数from factoryData left join EmployRecords on factoryData.factoryCode= EmployRecords.factoryCode
left join WorkingVisa on factoryData.factoryCode =WorkingVisa.factoryCode
left join BargainVisa on factoryData.factoryCode =BargainVisa.factoryCode