|
|
--keyid
|
|
|
--体育运动场（馆）:1007
|
|
|
--体育器械配备:1006
|
|
|
--音乐器材配备:1012
|
|
|
--美术器材配备:1003
|
|
|
--理科实验仪器:1002
|
|
|
--校园网：5001
|
|
|
|
|
|
select sbr.organizationNo ,2 as schoolTypeGroupNo,'二、义务教育' as schoolTypeGroup,1 as schoolTypeGroupNo2,'（一）小学' as schoolTypeGroup2 ,sbr.businessTypeNo,sbr.businessType
|
|
|
,sbr.citycountrytypeno ,sbr.cityCountryGroup,sum(case when j.stringvalue='是' then 1 else 0 end) as dabiaoNo ,j.keyid
|
|
|
from schoolBusinessRelation sbr
|
|
|
inner join schoolinfo as j on j.organizationno =sbr .organizationno and keyid in ('1007','1006','1012','1003','1002','5001','1020')
|
|
|
where sbr.gatherRegionB ='长春市' and sbr.businessTypeNo in('211') and sbr.isLastYearCancel =0 and sbr.recordyear =2019 and j.recordyear =2019
|
|
|
group by sbr.organizationNo,sbr.businessTypeNo,sbr.businessType ,sbr.isCount,sbr.citycountrytypeno ,sbr.cityCountryGroup,j.keyid
|
|
|
order by sbr.businessTypeNo;
|
|
|
|
|
|
--互联网：
|
|
|
select sbr.organizationNo ,2 as schoolTypeGroupNo,'二、义务教育' as schoolTypeGroup,1 as schoolTypeGroupNo2,'（一）小学' as schoolTypeGroup2 ,sbr.businessTypeNo,sbr.businessType
|
|
|
,sbr.citycountrytypeno ,sbr.cityCountryGroup,sum(case when j.stringvalue in ('光纤','拨号','ADSL','无线','其他') then 1 else 0 end) as dabiaoNo ,j.keyid
|
|
|
from schoolBusinessRelation sbr
|
|
|
inner join schoolinfo as j on j.organizationno =sbr .organizationno and keyid in ('1016')
|
|
|
where sbr.gatherRegionB ='长春市' and sbr.businessTypeNo in('211') and sbr.isLastYearCancel =0 and sbr.recordyear =2019 and j.recordyear =2019
|
|
|
group by sbr.organizationNo,sbr.businessTypeNo,sbr.businessType ,sbr.isCount,sbr.citycountrytypeno ,sbr.cityCountryGroup,j.keyid
|
|
|
order by sbr.businessTypeNo; |
