存储过程脚本如下…或者大家有什么优化建议…ALTER PROCEDURE [dbo].[proc_GetJcdZcSbShQk_Nationwide] @date nvarchar(20) AS BEGIN select Alias,Market,ID,--1 isnull(gpj.zhuceCount,0) as gpjZhuce, isnull(gpj.shangbaoCount,0) as gpjShangbao, isnull(gpj.shenheCount,0) as gpjShenhe, cast(cast(cast((case when gpj.zhuceCount is null then 0 else cast(isnull(gpj.shangbaoCount,0) as decimal)/cast(isnull(gpj.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as gpjShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出 cast(cast(cast((case when gpj.shangBaoCount is null then 0 else cast(isnull(gpj.shenheCount,0) as decimal)/cast(isnull(gpj.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as gpjShenheBili, --2 isnull(cll.zhuceCount,0) as cllZhuce, isnull(cll.shangbaoCount,0) as cllShangbao, isnull(cll.shenheCount,0) as cllShenhe, cast(cast(cast((case when cll.zhuceCount is null then 0 else cast(isnull(cll.shangbaoCount,0) as decimal)/cast(isnull(cll.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as cllShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出 cast(cast(cast((case when cll.shangBaoCount is null then 0 else cast(isnull(cll.shenheCount,0) as decimal)/cast(isnull(cll.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as cllShenheBili, --3 isnull(xsyc.zhuceCount,0) as xsycZhuce, isnull(xsyc.shangbaoCount,0) as xsycShangbao, isnull(xsyc.shenheCount,0) as xsycShenhe, cast(cast(cast((case when xsyc.zhuceCount is null then 0 else cast(isnull(xsyc.shangbaoCount,0) as decimal)/cast(isnull(xsyc.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as xsycShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出 cast(cast(cast((case when xsyc.shangBaoCount is null then 0 else cast(isnull(xsyc.shenheCount,0) as decimal)/cast(isnull(xsyc.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as xsycShenheBili, --4 isnull(pfjg.zhuceCount,0) as pfjgZhuce, isnull(pfjg.shangbaoCount,0) as pfjgShangbao, isnull(pfjg.shenheCount,0) as pfjgShenhe, cast(cast(cast((case when pfjg.zhuceCount is null then 0 else cast(isnull(pfjg.shangbaoCount,0) as decimal)/cast(isnull(pfjg.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as pfjgShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出 cast(cast(cast((case when pfjg.shangBaoCount is null then 0 else cast(isnull(pfjg.shenheCount,0) as decimal)/cast(isnull(pfjg.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as pfjgShenheBili, --5 isnull(zxjg.zhuceCount,0) as zxjgZhuce, isnull(zxjg.shangbaoCount,0) as zxjgShangbao, isnull(zxjg.shenheCount,0) as zxjgShenhe, cast(cast(cast((case when zxjg.zhuceCount is null then 0 else cast(isnull(zxjg.shangbaoCount,0) as decimal)/cast(isnull(zxjg.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as zxjgShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出 cast(cast(cast((case when zxjg.shangBaoCount is null then 0 else cast(isnull(zxjg.shenheCount,0) as decimal)/cast(isnull(zxjg.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as zxjgShenheBili, --6 isnull(yhqk.zhuceCount,0) as yhqkZhuce, isnull(yhqk.shangbaoCount,0) as yhqkShangbao, isnull(yhqk.shenheCount,0) as yhqkShenhe, cast(cast(cast((case when yhqk.zhuceCount is null then 0 else cast(isnull(yhqk.shangbaoCount,0) as decimal)/cast(isnull(yhqk.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as yhqkShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出 cast(cast(cast((case when yhqk.shangBaoCount is null then 0 else cast(isnull(yhqk.shenheCount,0) as decimal)/cast(isnull(yhqk.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as yhqkShenheBili, --7 isnull(kcqk.zhuceCount,0) as kcqkZhuce, isnull(kcqk.shangbaoCount,0) as kcqkShangbao, isnull(kcqk.shenheCount,0) as kcqkShenhe, cast(cast(cast((case when kcqk.zhuceCount is null then 0 else cast(isnull(kcqk.shangbaoCount,0) as decimal)/cast(isnull(kcqk.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as kcqkShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出 cast(cast(cast((case when kcqk.shangBaoCount is null then 0 else cast(isnull(kcqk.shenheCount,0) as decimal)/cast(isnull(kcqk.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as kcqkShenheBili, --8 isnull(jyzk.zhuceCount,0) as jyzkZhuce, isnull(jyzk.shangbaoCount,0) as jyzkShangbao, isnull(jyzk.shenheCount,0) as jyzkShenhe, cast(cast(cast((case when jyzk.zhuceCount is null then 0 else cast(isnull(jyzk.shangbaoCount,0) as decimal)/cast(isnull(jyzk.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as jyzkShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出 cast(cast(cast((case when jyzk.shangBaoCount is null then 0 else cast(isnull(jyzk.shenheCount,0) as decimal)/cast(isnull(jyzk.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as jyzkShenheBili, --9 isnull(lcjg.zhuceCount,0) as lcjgZhuce, isnull(lcjg.shangbaoCount,0) as lcjgShangbao, isnull(lcjg.shenheCount,0) as lcjgShenhe, cast(cast(cast((case when lcjg.zhuceCount is null then 0 else cast(isnull(lcjg.shangbaoCount,0) as decimal)/cast(isnull(lcjg.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as lcjgShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出 cast(cast(cast((case when lcjg.shangBaoCount is null then 0 else cast(isnull(lcjg.shenheCount,0) as decimal)/cast(isnull(lcjg.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as lcjgShenheBili, --10 isnull(lsyc.zhuceCount,0) as lsycZhuce, isnull(lsyc.shangbaoCount,0) as lsycShangbao, isnull(lsyc.shenheCount,0) as lsycShenhe, cast(cast(cast((case when lsyc.zhuceCount is null then 0 else cast(isnull(lsyc.shangbaoCount,0) as decimal)/cast(isnull(lsyc.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as lsycShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出 cast(cast(cast((case when lsyc.shangBaoCount is null then 0 else cast(isnull(lsyc.shenheCount,0) as decimal)/cast(isnull(lsyc.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as lsycShenheBili, --11 isnull(pfyc.zhuceCount,0) as pfycZhuce, isnull(pfyc.shangbaoCount,0) as pfycShangbao, isnull(pfyc.shenheCount,0) as pfycShenhe, cast(cast(cast((case when pfyc.zhuceCount is null then 0 else cast(isnull(pfyc.shangbaoCount,0) as decimal)/cast(isnull(pfyc.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as pfycShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出 cast(cast(cast((case when pfyc.shangBaoCount is null then 0 else cast(isnull(pfyc.shenheCount,0) as decimal)/cast(isnull(pfyc.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as pfycShenheBili,END
继续:--heji isnull(gpj.zhuceCount,0)+isnull(cll.zhuceCount,0)+isnull(xsyc.zhuceCount,0)+isnull(pfjg.zhuceCount,0) +isnull(zxjg.zhuceCount,0) +isnull(yhqk.zhuceCount,0)+isnull(kcqk.zhuceCount,0) +isnull(jyzk.zhuceCount,0)+isnull(lcjg.zhuceCount,0)+isnull(lsyc.zhuceCount,0)+isnull(pfyc.zhuceCount,0) as hejiZhuce, isnull(gpj.shangbaoCount,0)+isnull(cll.shangbaoCount,0)+isnull(xsyc.shangbaoCount,0)+isnull(pfjg.shangbaoCount,0)+isnull(zxjg.shangbaoCount,0)+isnull(yhqk.shangbaoCount,0)+isnull(kcqk.shangbaoCount,0)+isnull(jyzk.shangbaoCount,0)+isnull(lcjg.shangbaoCount,0)+isnull(lsyc.shangbaoCount,0) +isnull(pfyc.shangbaoCount,0) as hejiShangbao, isnull(gpj.shenheCount,0)+isnull(cll.shenheCount,0)+isnull(xsyc.shenheCount,0)+isnull(pfjg.shenheCount,0)+isnull(zxjg.shenheCount,0)+isnull(yhqk.shenheCount,0)+isnull(kcqk.shenheCount,0)+isnull(jyzk.shenheCount,0)+isnull(lcjg.shenheCount,0)+isnull(lsyc.shenheCount,0) +isnull(pfyc.shenheCount,0) as hejiShenhe, case when (isnull(gpj.zhuceCount,0)+isnull(cll.zhuceCount,0)+isnull(xsyc.zhuceCount,0)+isnull(pfjg.zhuceCount,0) +isnull(zxjg.zhuceCount,0) +isnull(yhqk.zhuceCount,0)+isnull(kcqk.zhuceCount,0) +isnull(jyzk.zhuceCount,0)+isnull(lcjg.zhuceCount,0)+isnull(lsyc.zhuceCount,0)+isnull(pfyc.zhuceCount,0))=0 then '0%' else cast(cast(cast((isnull(gpj.shangbaoCount,0)+isnull(cll.shangbaoCount,0)+isnull(xsyc.shangbaoCount,0)+isnull(pfjg.shangbaoCount,0)+isnull(zxjg.shangbaoCount,0)+isnull(yhqk.shangbaoCount,0)+isnull(kcqk.shangbaoCount,0)+isnull(jyzk.shangbaoCount,0)+isnull(lcjg.shangbaoCount,0)+isnull(lsyc.shangbaoCount,0) +isnull(pfyc.shangbaoCount,0)) as decimal)/cast((isnull(gpj.zhuceCount,0)+isnull(cll.zhuceCount,0)+isnull(xsyc.zhuceCount,0)+isnull(pfjg.zhuceCount,0) +isnull(zxjg.zhuceCount,0) +isnull(yhqk.zhuceCount,0)+isnull(kcqk.zhuceCount,0) +isnull(jyzk.zhuceCount,0)+isnull(lcjg.zhuceCount,0)+isnull(lsyc.zhuceCount,0)+isnull(pfyc.zhuceCount,0))as decimal)*100 as numeric(10,0)) as varchar(10)) + '%' end as hejiShangbaoBili, case when isnull(gpj.shangbaoCount,0)+isnull(cll.shangbaoCount,0)+isnull(xsyc.shangbaoCount,0)+isnull(pfjg.shangbaoCount,0)+isnull(zxjg.shangbaoCount,0)+isnull(yhqk.shangbaoCount,0)+isnull(kcqk.shangbaoCount,0)+isnull(jyzk.shangbaoCount,0)+isnull(lcjg.shangbaoCount,0)+isnull(lsyc.shangbaoCount,0) +isnull(pfyc.shangbaoCount,0)=0 then '0%' else cast(cast(cast(isnull(gpj.shenheCount,0)+isnull(cll.shenheCount,0)+isnull(xsyc.shenheCount,0)+isnull(pfjg.shenheCount,0)+isnull(zxjg.shenheCount,0)+isnull(yhqk.shenheCount,0)+isnull(kcqk.shenheCount,0)+isnull(jyzk.shenheCount,0)+isnull(lcjg.shenheCount,0)+isnull(lsyc.shenheCount,0)+isnull(pfyc.shenheCount,0) as decimal)/cast(isnull(gpj.shangbaoCount,0)+isnull(cll.shangbaoCount,0)+isnull(xsyc.shangbaoCount,0)+isnull(pfjg.shangbaoCount,0)+isnull(zxjg.shangbaoCount,0)+isnull(yhqk.shangbaoCount,0)+isnull(kcqk.shangbaoCount,0)+isnull(jyzk.shangbaoCount,0)+isnull(lcjg.shangbaoCount,0)+isnull(lsyc.shangbaoCount,0) +isnull(pfyc.shangbaoCount,0) as decimal)*100 as numeric(10,0)) as varchar(10)) + '%' end as hejiShenheBilifrom Dic_Organization left join ( --加油站挂牌销售价格 select zhuce.Province,zhuceCount,shangbaoCount,shenheCount from ( select Sys_GasStationInfo.Province,count(*) as zhuceCount from Rel_User_SurveyTable left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Rel_User_SurveyTable.ObjectId left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='01' group by Sys_GasStationInfo.Province )zhuce left join ( select Province,count(*) as shangbaoCount from Sys_ReportData_01 left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Sys_ReportData_01.ObjectId left join Sys_User on Sys_ReportData_01.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date group by Province )shangbao on zhuce.Province = shangbao.Province left join ( select Province,count(*) as shenheCount from Sys_ReportData_01 left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Sys_ReportData_01.ObjectId left join Sys_User on Sys_ReportData_01.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date AND RecordStatus=4 group by Province )shenhe on zhuce.Province = shenhe.Province )gpj on Dic_Organization.Id = gpj.Province left join ( --加油站车流量 select zhuce.Province,zhuceCount,shangbaoCount,shenheCount from ( select Sys_GasStationInfo.Province,count(*) as zhuceCount from Rel_User_SurveyTable left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Rel_User_SurveyTable.ObjectId left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='02' group by Sys_GasStationInfo.Province )zhuce left join ( select Sys_GasStationInfo.Province,count(*) as shangbaoCount from Sys_ReportData_02 left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Sys_ReportData_02.ObjectId left join Sys_User on Sys_ReportData_02.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date group by Sys_GasStationInfo.Province )shangbao on zhuce.Province = shangbao.Province left join ( select Sys_GasStationInfo.Province,count(*) as shenheCount from Sys_ReportData_02 left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Sys_ReportData_02.ObjectId left join Sys_User on Sys_ReportData_02.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date AND RecordStatus=4 group by Sys_GasStationInfo.Province )shenhe on zhuce.Province = shenhe.Province )cll on Dic_Organization.Id = cll.Province left join ( --加油站销售异常情况 select zhuce.Province,zhuceCount,shangbaoCount,shenheCount from ( select Sys_GasStationInfo.Province,count(*) as zhuceCount from Rel_User_SurveyTable left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Rel_User_SurveyTable.ObjectId left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='03' group by Sys_GasStationInfo.Province )zhuce left join ( select Sys_GasStationInfo.Province,count(*) as shangbaoCount from Sys_ReportData_03 left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Sys_ReportData_03.ObjectId left join Sys_User on Sys_ReportData_03.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date group by Sys_GasStationInfo.Province )shangbao on zhuce.Province = shangbao.Province left join ( select Sys_GasStationInfo.Province,count(*) as shenheCount from Sys_ReportData_03 left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Sys_ReportData_03.ObjectId left join Sys_User on Sys_ReportData_03.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date AND RecordStatus=4 group by Sys_GasStationInfo.Province )shenhe on zhuce.Province = shenhe.Province )xsyc on Dic_Organization.Id = xsyc.Province left join ( --汽柴油批发价格 select zhuce.ProvinceID,zhuceCount,shangbaoCount,shenheCount from ( select Sys_PrefecturalInfo.ProvinceID,count(*) as zhuceCount from Rel_User_SurveyTable left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Rel_User_SurveyTable.ObjectId left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='04' group by Sys_PrefecturalInfo.ProvinceID )zhuce left join ( select Sys_PrefecturalInfo.ProvinceID,count(*) as shangbaoCount from Sys_ReportData_04 left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Sys_ReportData_04.ObjectId left join Sys_User on Sys_ReportData_04.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date group by Sys_PrefecturalInfo.ProvinceID )shangbao on zhuce.ProvinceID = shangbao.ProvinceID left join ( select Sys_PrefecturalInfo.ProvinceID,count(*) as shenheCount from Sys_ReportData_04 left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Sys_ReportData_04.ObjectId left join Sys_User on Sys_ReportData_04.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date AND RecordStatus=4 group by Sys_PrefecturalInfo.ProvinceID )shenhe on zhuce.ProvinceID = shenhe.ProvinceID )pfjg on Dic_Organization.Id = pfjg.ProvinceID
继续: left join ( --汽柴油直销价格 select zhuce.ProvinceID,zhuceCount,shangbaoCount,shenheCount from ( select Sys_PrefecturalInfo.ProvinceID,count(*) as zhuceCount from Rel_User_SurveyTable left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Rel_User_SurveyTable.ObjectId left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='05' group by Sys_PrefecturalInfo.ProvinceID )zhuce left join ( select Sys_PrefecturalInfo.ProvinceID,count(*) as shangbaoCount from Sys_ReportData_05 left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Sys_ReportData_05.ObjectId left join Sys_User on Sys_ReportData_05.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date group by Sys_PrefecturalInfo.ProvinceID )shangbao on zhuce.ProvinceID = shangbao.ProvinceID left join ( select Sys_PrefecturalInfo.ProvinceID,count(*) as shenheCount from Sys_ReportData_05 left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Sys_ReportData_05.ObjectId left join Sys_User on Sys_ReportData_05.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date AND RecordStatus=4 group by Sys_PrefecturalInfo.ProvinceID )shenhe on zhuce.ProvinceID = shenhe.ProvinceID )zxjg on Dic_Organization.Id = zxjg.ProvinceID left join ( --加油卡优惠情况 select zhuce.ProvinceID,zhuceCount,shangbaoCount,shenheCount from ( select Sys_PrefecturalInfo.ProvinceID,count(*) as zhuceCount from Rel_User_SurveyTable left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Rel_User_SurveyTable.ObjectId left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='06' group by Sys_PrefecturalInfo.ProvinceID )zhuce left join ( select Sys_PrefecturalInfo.ProvinceID,count(*) as shangbaoCount from Sys_ReportData_06 left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Sys_ReportData_06.ObjectId left join Sys_User on Sys_ReportData_06.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND UpdateMonth = (select CONVERT(varchar(6) ,cast(@date as datetime), 112 )) group by Sys_PrefecturalInfo.ProvinceID )shangbao on zhuce.ProvinceID = shangbao.ProvinceID left join ( select Sys_PrefecturalInfo.ProvinceID,count(*) as shenheCount from Sys_ReportData_06 left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Sys_ReportData_06.ObjectId left join Sys_User on Sys_ReportData_06.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND UpdateMonth = (select CONVERT(varchar(6) ,cast(@date as datetime), 112 )) AND RecordStatus=4 group by Sys_PrefecturalInfo.ProvinceID )shenhe on zhuce.ProvinceID = shenhe.ProvinceID )yhqk on Dic_Organization.Id = yhqk.ProvinceID left join ( --竞争对手库存情况调查 select zhuce.Province,zhuceCount,shangbaoCount,shenheCount from ( select Sys_OilDepotInfo.Province,count(*) as zhuceCount from Rel_User_SurveyTable left join Sys_OilDepotInfo on Sys_OilDepotInfo.Id = Rel_User_SurveyTable.ObjectId left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='08' group by Sys_OilDepotInfo.Province )zhuce left join ( select Sys_OilDepotInfo.Province,count(*) as shangbaoCount from Sys_ReportData_08 left join Sys_OilDepotInfo on Sys_OilDepotInfo.Id = Sys_ReportData_08.ObjectId left join Sys_User on Sys_ReportData_08.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND (CONVERT(varchar(10),Startdate,120)+' 00:00:00')<=(@date+' 00:00:00') and (CONVERT(varchar(10),EndDate,120)+' 23:59:59')>=(@date+' 00:00:00') group by Sys_OilDepotInfo.Province )shangbao on zhuce.Province = shangbao.Province left join ( select Sys_OilDepotInfo.Province,count(*) as shenheCount from Sys_ReportData_08 left join Sys_OilDepotInfo on Sys_OilDepotInfo.Id = Sys_ReportData_08.ObjectId left join Sys_User on Sys_ReportData_08.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND (CONVERT(varchar(10),Startdate,120)+' 00:00:00')<=(@date+' 00:00:00') and (CONVERT(varchar(10),EndDate,120)+' 23:59:59')>=(@date+' 00:00:00') AND RecordStatus=4 group by Sys_OilDepotInfo.Province )shenhe on zhuce.Province = shenhe.Province )kcqk on Dic_Organization.Id = kcqk.Province left join ( --企业经营状况快速调查报告 select zhuce.ProvinceID,zhuceCount,shangbaoCount,shenheCount from ( select Sys_User.ProvinceID,count(*) as zhuceCount from Rel_User_SurveyTable left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='09' group by Sys_User.ProvinceID )zhuce left join ( select Sys_User.ProvinceID,count(*) as shangbaoCount from Sys_ReportData_09 left join Sys_User on Sys_ReportData_09.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND (CONVERT(varchar(10),Startdate,120)+' 00:00:00')<=(@date+' 00:00:00') and (CONVERT(varchar(10),EndDate,120)+' 23:59:59')>=(@date+' 00:00:00') group by Sys_User.ProvinceID )shangbao on zhuce.ProvinceID = shangbao.ProvinceID left join ( select Sys_User.ProvinceID,count(*) as shenheCount from Sys_ReportData_09 left join Sys_User on Sys_ReportData_09.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND (CONVERT(varchar(10),Startdate,120)+' 00:00:00')<=(@date+' 00:00:00') and (CONVERT(varchar(10),EndDate,120)+' 23:59:59')>=(@date+' 00:00:00') AND RecordStatus=4 group by Sys_User.ProvinceID )shenhe on zhuce.ProvinceID = shenhe.ProvinceID )jyzk on Dic_Organization.Id = jyzk.ProvinceID left join ( --地方炼厂汽柴油销售价格 select zhuce.Province,zhuceCount,shangbaoCount,shenheCount from ( select Sys_PotrerillosInfo.Province,count(*) as zhuceCount from Rel_User_SurveyTable left join Sys_PotrerillosInfo on Sys_PotrerillosInfo.Id = Rel_User_SurveyTable.ObjectId left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='10' group by Sys_PotrerillosInfo.Province )zhuce left join ( select Sys_PotrerillosInfo.Province,count(*) as shangbaoCount from Sys_ReportData_10 left join Sys_PotrerillosInfo on Sys_PotrerillosInfo.Id = Sys_ReportData_10.ObjectId left join Sys_User on Sys_ReportData_10.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date group by Sys_PotrerillosInfo.Province )shangbao on zhuce.Province = shangbao.Province left join ( select Sys_PotrerillosInfo.Province,count(*) as shenheCount from Sys_ReportData_10 left join Sys_PotrerillosInfo on Sys_PotrerillosInfo.Id = Sys_ReportData_10.ObjectId left join Sys_User on Sys_ReportData_10.UserId = Sys_User.Id left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date AND RecordStatus=4 group by Sys_PotrerillosInfo.Province )shenhe on zhuce.Province = shenhe.Province )lcjg on Dic_Organization.Id = lcjg.Province
@date nvarchar(20)
AS
BEGIN
select Alias,Market,ID,--1
isnull(gpj.zhuceCount,0) as gpjZhuce,
isnull(gpj.shangbaoCount,0) as gpjShangbao,
isnull(gpj.shenheCount,0) as gpjShenhe,
cast(cast(cast((case when gpj.zhuceCount is null then 0 else cast(isnull(gpj.shangbaoCount,0) as decimal)/cast(isnull(gpj.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as gpjShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出
cast(cast(cast((case when gpj.shangBaoCount is null then 0 else cast(isnull(gpj.shenheCount,0) as decimal)/cast(isnull(gpj.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as gpjShenheBili,
--2
isnull(cll.zhuceCount,0) as cllZhuce,
isnull(cll.shangbaoCount,0) as cllShangbao,
isnull(cll.shenheCount,0) as cllShenhe,
cast(cast(cast((case when cll.zhuceCount is null then 0 else cast(isnull(cll.shangbaoCount,0) as decimal)/cast(isnull(cll.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as cllShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出
cast(cast(cast((case when cll.shangBaoCount is null then 0 else cast(isnull(cll.shenheCount,0) as decimal)/cast(isnull(cll.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as cllShenheBili,
--3
isnull(xsyc.zhuceCount,0) as xsycZhuce,
isnull(xsyc.shangbaoCount,0) as xsycShangbao,
isnull(xsyc.shenheCount,0) as xsycShenhe,
cast(cast(cast((case when xsyc.zhuceCount is null then 0 else cast(isnull(xsyc.shangbaoCount,0) as decimal)/cast(isnull(xsyc.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as xsycShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出
cast(cast(cast((case when xsyc.shangBaoCount is null then 0 else cast(isnull(xsyc.shenheCount,0) as decimal)/cast(isnull(xsyc.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as xsycShenheBili,
--4
isnull(pfjg.zhuceCount,0) as pfjgZhuce,
isnull(pfjg.shangbaoCount,0) as pfjgShangbao,
isnull(pfjg.shenheCount,0) as pfjgShenhe,
cast(cast(cast((case when pfjg.zhuceCount is null then 0 else cast(isnull(pfjg.shangbaoCount,0) as decimal)/cast(isnull(pfjg.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as pfjgShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出
cast(cast(cast((case when pfjg.shangBaoCount is null then 0 else cast(isnull(pfjg.shenheCount,0) as decimal)/cast(isnull(pfjg.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as pfjgShenheBili,
--5
isnull(zxjg.zhuceCount,0) as zxjgZhuce,
isnull(zxjg.shangbaoCount,0) as zxjgShangbao,
isnull(zxjg.shenheCount,0) as zxjgShenhe,
cast(cast(cast((case when zxjg.zhuceCount is null then 0 else cast(isnull(zxjg.shangbaoCount,0) as decimal)/cast(isnull(zxjg.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as zxjgShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出
cast(cast(cast((case when zxjg.shangBaoCount is null then 0 else cast(isnull(zxjg.shenheCount,0) as decimal)/cast(isnull(zxjg.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as zxjgShenheBili,
--6
isnull(yhqk.zhuceCount,0) as yhqkZhuce,
isnull(yhqk.shangbaoCount,0) as yhqkShangbao,
isnull(yhqk.shenheCount,0) as yhqkShenhe,
cast(cast(cast((case when yhqk.zhuceCount is null then 0 else cast(isnull(yhqk.shangbaoCount,0) as decimal)/cast(isnull(yhqk.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as yhqkShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出
cast(cast(cast((case when yhqk.shangBaoCount is null then 0 else cast(isnull(yhqk.shenheCount,0) as decimal)/cast(isnull(yhqk.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as yhqkShenheBili,
--7
isnull(kcqk.zhuceCount,0) as kcqkZhuce,
isnull(kcqk.shangbaoCount,0) as kcqkShangbao,
isnull(kcqk.shenheCount,0) as kcqkShenhe,
cast(cast(cast((case when kcqk.zhuceCount is null then 0 else cast(isnull(kcqk.shangbaoCount,0) as decimal)/cast(isnull(kcqk.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as kcqkShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出
cast(cast(cast((case when kcqk.shangBaoCount is null then 0 else cast(isnull(kcqk.shenheCount,0) as decimal)/cast(isnull(kcqk.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as kcqkShenheBili,
--8
isnull(jyzk.zhuceCount,0) as jyzkZhuce,
isnull(jyzk.shangbaoCount,0) as jyzkShangbao,
isnull(jyzk.shenheCount,0) as jyzkShenhe,
cast(cast(cast((case when jyzk.zhuceCount is null then 0 else cast(isnull(jyzk.shangbaoCount,0) as decimal)/cast(isnull(jyzk.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as jyzkShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出
cast(cast(cast((case when jyzk.shangBaoCount is null then 0 else cast(isnull(jyzk.shenheCount,0) as decimal)/cast(isnull(jyzk.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as jyzkShenheBili,
--9
isnull(lcjg.zhuceCount,0) as lcjgZhuce,
isnull(lcjg.shangbaoCount,0) as lcjgShangbao,
isnull(lcjg.shenheCount,0) as lcjgShenhe,
cast(cast(cast((case when lcjg.zhuceCount is null then 0 else cast(isnull(lcjg.shangbaoCount,0) as decimal)/cast(isnull(lcjg.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as lcjgShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出
cast(cast(cast((case when lcjg.shangBaoCount is null then 0 else cast(isnull(lcjg.shenheCount,0) as decimal)/cast(isnull(lcjg.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as lcjgShenheBili,
--10
isnull(lsyc.zhuceCount,0) as lsycZhuce,
isnull(lsyc.shangbaoCount,0) as lsycShangbao,
isnull(lsyc.shenheCount,0) as lsycShenhe,
cast(cast(cast((case when lsyc.zhuceCount is null then 0 else cast(isnull(lsyc.shangbaoCount,0) as decimal)/cast(isnull(lsyc.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as lsycShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出
cast(cast(cast((case when lsyc.shangBaoCount is null then 0 else cast(isnull(lsyc.shenheCount,0) as decimal)/cast(isnull(lsyc.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as lsycShenheBili,
--11
isnull(pfyc.zhuceCount,0) as pfycZhuce,
isnull(pfyc.shangbaoCount,0) as pfycShangbao,
isnull(pfyc.shenheCount,0) as pfycShenhe,
cast(cast(cast((case when pfyc.zhuceCount is null then 0 else cast(isnull(pfyc.shangbaoCount,0) as decimal)/cast(isnull(pfyc.zhuceCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as pfycShangbaoBili, --逻辑:首先判断注册数量(即被除数)是否为null,如果为null则直接返回0。 不为null时将注册和上报数量转成decimal进行计算,计算后乘以100然后四舍五入保留整数,最后转成varchar输出
cast(cast(cast((case when pfyc.shangBaoCount is null then 0 else cast(isnull(pfyc.shenheCount,0) as decimal)/cast(isnull(pfyc.shangbaoCount,0) as decimal) end) * 100 as numeric(10,0)) as decimal(10,0) ) as varchar(10) ) +'%' as pfycShenheBili,END
isnull(gpj.zhuceCount,0)+isnull(cll.zhuceCount,0)+isnull(xsyc.zhuceCount,0)+isnull(pfjg.zhuceCount,0) +isnull(zxjg.zhuceCount,0) +isnull(yhqk.zhuceCount,0)+isnull(kcqk.zhuceCount,0) +isnull(jyzk.zhuceCount,0)+isnull(lcjg.zhuceCount,0)+isnull(lsyc.zhuceCount,0)+isnull(pfyc.zhuceCount,0) as hejiZhuce,
isnull(gpj.shangbaoCount,0)+isnull(cll.shangbaoCount,0)+isnull(xsyc.shangbaoCount,0)+isnull(pfjg.shangbaoCount,0)+isnull(zxjg.shangbaoCount,0)+isnull(yhqk.shangbaoCount,0)+isnull(kcqk.shangbaoCount,0)+isnull(jyzk.shangbaoCount,0)+isnull(lcjg.shangbaoCount,0)+isnull(lsyc.shangbaoCount,0) +isnull(pfyc.shangbaoCount,0) as hejiShangbao,
isnull(gpj.shenheCount,0)+isnull(cll.shenheCount,0)+isnull(xsyc.shenheCount,0)+isnull(pfjg.shenheCount,0)+isnull(zxjg.shenheCount,0)+isnull(yhqk.shenheCount,0)+isnull(kcqk.shenheCount,0)+isnull(jyzk.shenheCount,0)+isnull(lcjg.shenheCount,0)+isnull(lsyc.shenheCount,0) +isnull(pfyc.shenheCount,0) as hejiShenhe,
case when (isnull(gpj.zhuceCount,0)+isnull(cll.zhuceCount,0)+isnull(xsyc.zhuceCount,0)+isnull(pfjg.zhuceCount,0) +isnull(zxjg.zhuceCount,0) +isnull(yhqk.zhuceCount,0)+isnull(kcqk.zhuceCount,0) +isnull(jyzk.zhuceCount,0)+isnull(lcjg.zhuceCount,0)+isnull(lsyc.zhuceCount,0)+isnull(pfyc.zhuceCount,0))=0 then '0%' else cast(cast(cast((isnull(gpj.shangbaoCount,0)+isnull(cll.shangbaoCount,0)+isnull(xsyc.shangbaoCount,0)+isnull(pfjg.shangbaoCount,0)+isnull(zxjg.shangbaoCount,0)+isnull(yhqk.shangbaoCount,0)+isnull(kcqk.shangbaoCount,0)+isnull(jyzk.shangbaoCount,0)+isnull(lcjg.shangbaoCount,0)+isnull(lsyc.shangbaoCount,0) +isnull(pfyc.shangbaoCount,0)) as decimal)/cast((isnull(gpj.zhuceCount,0)+isnull(cll.zhuceCount,0)+isnull(xsyc.zhuceCount,0)+isnull(pfjg.zhuceCount,0) +isnull(zxjg.zhuceCount,0) +isnull(yhqk.zhuceCount,0)+isnull(kcqk.zhuceCount,0) +isnull(jyzk.zhuceCount,0)+isnull(lcjg.zhuceCount,0)+isnull(lsyc.zhuceCount,0)+isnull(pfyc.zhuceCount,0))as decimal)*100 as numeric(10,0)) as varchar(10)) + '%' end as hejiShangbaoBili,
case when isnull(gpj.shangbaoCount,0)+isnull(cll.shangbaoCount,0)+isnull(xsyc.shangbaoCount,0)+isnull(pfjg.shangbaoCount,0)+isnull(zxjg.shangbaoCount,0)+isnull(yhqk.shangbaoCount,0)+isnull(kcqk.shangbaoCount,0)+isnull(jyzk.shangbaoCount,0)+isnull(lcjg.shangbaoCount,0)+isnull(lsyc.shangbaoCount,0) +isnull(pfyc.shangbaoCount,0)=0 then '0%' else cast(cast(cast(isnull(gpj.shenheCount,0)+isnull(cll.shenheCount,0)+isnull(xsyc.shenheCount,0)+isnull(pfjg.shenheCount,0)+isnull(zxjg.shenheCount,0)+isnull(yhqk.shenheCount,0)+isnull(kcqk.shenheCount,0)+isnull(jyzk.shenheCount,0)+isnull(lcjg.shenheCount,0)+isnull(lsyc.shenheCount,0)+isnull(pfyc.shenheCount,0) as decimal)/cast(isnull(gpj.shangbaoCount,0)+isnull(cll.shangbaoCount,0)+isnull(xsyc.shangbaoCount,0)+isnull(pfjg.shangbaoCount,0)+isnull(zxjg.shangbaoCount,0)+isnull(yhqk.shangbaoCount,0)+isnull(kcqk.shangbaoCount,0)+isnull(jyzk.shangbaoCount,0)+isnull(lcjg.shangbaoCount,0)+isnull(lsyc.shangbaoCount,0) +isnull(pfyc.shangbaoCount,0) as decimal)*100 as numeric(10,0)) as varchar(10)) + '%' end as hejiShenheBilifrom Dic_Organization
left join
(
--加油站挂牌销售价格
select zhuce.Province,zhuceCount,shangbaoCount,shenheCount from
(
select Sys_GasStationInfo.Province,count(*) as zhuceCount from Rel_User_SurveyTable
left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Rel_User_SurveyTable.ObjectId
left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId
left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='01'
group by Sys_GasStationInfo.Province
)zhuce
left join
(
select Province,count(*) as shangbaoCount from Sys_ReportData_01
left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Sys_ReportData_01.ObjectId
left join Sys_User on Sys_ReportData_01.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date group by Province
)shangbao
on zhuce.Province = shangbao.Province
left join
(
select Province,count(*) as shenheCount from Sys_ReportData_01
left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Sys_ReportData_01.ObjectId
left join Sys_User on Sys_ReportData_01.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date AND RecordStatus=4 group by Province
)shenhe
on zhuce.Province = shenhe.Province
)gpj
on Dic_Organization.Id = gpj.Province
left join
(
--加油站车流量
select zhuce.Province,zhuceCount,shangbaoCount,shenheCount from
(
select Sys_GasStationInfo.Province,count(*) as zhuceCount from Rel_User_SurveyTable
left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Rel_User_SurveyTable.ObjectId
left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId
left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='02'
group by Sys_GasStationInfo.Province
)zhuce
left join
(
select Sys_GasStationInfo.Province,count(*) as shangbaoCount from Sys_ReportData_02
left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Sys_ReportData_02.ObjectId
left join Sys_User on Sys_ReportData_02.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date
group by Sys_GasStationInfo.Province
)shangbao
on zhuce.Province = shangbao.Province
left join
(
select Sys_GasStationInfo.Province,count(*) as shenheCount from Sys_ReportData_02
left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Sys_ReportData_02.ObjectId
left join Sys_User on Sys_ReportData_02.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date AND RecordStatus=4
group by Sys_GasStationInfo.Province
)shenhe
on zhuce.Province = shenhe.Province
)cll
on Dic_Organization.Id = cll.Province
left join
(
--加油站销售异常情况
select zhuce.Province,zhuceCount,shangbaoCount,shenheCount from
(
select Sys_GasStationInfo.Province,count(*) as zhuceCount from Rel_User_SurveyTable
left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Rel_User_SurveyTable.ObjectId
left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId
left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='03'
group by Sys_GasStationInfo.Province
)zhuce
left join
(
select Sys_GasStationInfo.Province,count(*) as shangbaoCount from Sys_ReportData_03
left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Sys_ReportData_03.ObjectId
left join Sys_User on Sys_ReportData_03.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date
group by Sys_GasStationInfo.Province
)shangbao
on zhuce.Province = shangbao.Province
left join
(
select Sys_GasStationInfo.Province,count(*) as shenheCount from Sys_ReportData_03
left join Sys_GasStationInfo on Sys_GasStationInfo.Id = Sys_ReportData_03.ObjectId
left join Sys_User on Sys_ReportData_03.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date AND RecordStatus=4
group by Sys_GasStationInfo.Province
)shenhe
on zhuce.Province = shenhe.Province
)xsyc
on Dic_Organization.Id = xsyc.Province
left join
(
--汽柴油批发价格
select zhuce.ProvinceID,zhuceCount,shangbaoCount,shenheCount from
(
select Sys_PrefecturalInfo.ProvinceID,count(*) as zhuceCount from Rel_User_SurveyTable
left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Rel_User_SurveyTable.ObjectId
left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId
left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='04'
group by Sys_PrefecturalInfo.ProvinceID
)zhuce
left join
(
select Sys_PrefecturalInfo.ProvinceID,count(*) as shangbaoCount from Sys_ReportData_04
left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Sys_ReportData_04.ObjectId
left join Sys_User on Sys_ReportData_04.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date
group by Sys_PrefecturalInfo.ProvinceID
)shangbao
on zhuce.ProvinceID = shangbao.ProvinceID
left join
(
select Sys_PrefecturalInfo.ProvinceID,count(*) as shenheCount from Sys_ReportData_04
left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Sys_ReportData_04.ObjectId
left join Sys_User on Sys_ReportData_04.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date AND RecordStatus=4
group by Sys_PrefecturalInfo.ProvinceID
)shenhe
on zhuce.ProvinceID = shenhe.ProvinceID
)pfjg
on Dic_Organization.Id = pfjg.ProvinceID
left join
(
--汽柴油直销价格
select zhuce.ProvinceID,zhuceCount,shangbaoCount,shenheCount from
(
select Sys_PrefecturalInfo.ProvinceID,count(*) as zhuceCount from Rel_User_SurveyTable
left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Rel_User_SurveyTable.ObjectId
left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId
left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='05'
group by Sys_PrefecturalInfo.ProvinceID
)zhuce
left join
(
select Sys_PrefecturalInfo.ProvinceID,count(*) as shangbaoCount from Sys_ReportData_05
left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Sys_ReportData_05.ObjectId
left join Sys_User on Sys_ReportData_05.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date
group by Sys_PrefecturalInfo.ProvinceID
)shangbao
on zhuce.ProvinceID = shangbao.ProvinceID
left join
(
select Sys_PrefecturalInfo.ProvinceID,count(*) as shenheCount from Sys_ReportData_05
left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Sys_ReportData_05.ObjectId
left join Sys_User on Sys_ReportData_05.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date AND RecordStatus=4
group by Sys_PrefecturalInfo.ProvinceID
)shenhe
on zhuce.ProvinceID = shenhe.ProvinceID
)zxjg
on Dic_Organization.Id = zxjg.ProvinceID
left join
(
--加油卡优惠情况
select zhuce.ProvinceID,zhuceCount,shangbaoCount,shenheCount from
(
select Sys_PrefecturalInfo.ProvinceID,count(*) as zhuceCount from Rel_User_SurveyTable
left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Rel_User_SurveyTable.ObjectId
left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId
left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='06'
group by Sys_PrefecturalInfo.ProvinceID
)zhuce
left join
(
select Sys_PrefecturalInfo.ProvinceID,count(*) as shangbaoCount from Sys_ReportData_06
left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Sys_ReportData_06.ObjectId
left join Sys_User on Sys_ReportData_06.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND UpdateMonth = (select CONVERT(varchar(6) ,cast(@date as datetime), 112 ))
group by Sys_PrefecturalInfo.ProvinceID
)shangbao
on zhuce.ProvinceID = shangbao.ProvinceID
left join
(
select Sys_PrefecturalInfo.ProvinceID,count(*) as shenheCount from Sys_ReportData_06
left join Sys_PrefecturalInfo on Sys_PrefecturalInfo.Id = Sys_ReportData_06.ObjectId
left join Sys_User on Sys_ReportData_06.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND UpdateMonth = (select CONVERT(varchar(6) ,cast(@date as datetime), 112 )) AND RecordStatus=4
group by Sys_PrefecturalInfo.ProvinceID
)shenhe
on zhuce.ProvinceID = shenhe.ProvinceID
)yhqk
on Dic_Organization.Id = yhqk.ProvinceID
left join
(
--竞争对手库存情况调查
select zhuce.Province,zhuceCount,shangbaoCount,shenheCount from
(
select Sys_OilDepotInfo.Province,count(*) as zhuceCount from Rel_User_SurveyTable
left join Sys_OilDepotInfo on Sys_OilDepotInfo.Id = Rel_User_SurveyTable.ObjectId
left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId
left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='08'
group by Sys_OilDepotInfo.Province
)zhuce
left join
(
select Sys_OilDepotInfo.Province,count(*) as shangbaoCount from Sys_ReportData_08
left join Sys_OilDepotInfo on Sys_OilDepotInfo.Id = Sys_ReportData_08.ObjectId
left join Sys_User on Sys_ReportData_08.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND (CONVERT(varchar(10),Startdate,120)+' 00:00:00')<=(@date+' 00:00:00') and (CONVERT(varchar(10),EndDate,120)+' 23:59:59')>=(@date+' 00:00:00')
group by Sys_OilDepotInfo.Province
)shangbao
on zhuce.Province = shangbao.Province
left join
(
select Sys_OilDepotInfo.Province,count(*) as shenheCount from Sys_ReportData_08
left join Sys_OilDepotInfo on Sys_OilDepotInfo.Id = Sys_ReportData_08.ObjectId
left join Sys_User on Sys_ReportData_08.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND (CONVERT(varchar(10),Startdate,120)+' 00:00:00')<=(@date+' 00:00:00') and (CONVERT(varchar(10),EndDate,120)+' 23:59:59')>=(@date+' 00:00:00') AND RecordStatus=4
group by Sys_OilDepotInfo.Province
)shenhe
on zhuce.Province = shenhe.Province
)kcqk
on Dic_Organization.Id = kcqk.Province
left join
(
--企业经营状况快速调查报告
select zhuce.ProvinceID,zhuceCount,shangbaoCount,shenheCount from
(
select Sys_User.ProvinceID,count(*) as zhuceCount from Rel_User_SurveyTable
left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId
left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='09'
group by Sys_User.ProvinceID
)zhuce
left join
(
select Sys_User.ProvinceID,count(*) as shangbaoCount from Sys_ReportData_09
left join Sys_User on Sys_ReportData_09.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND (CONVERT(varchar(10),Startdate,120)+' 00:00:00')<=(@date+' 00:00:00') and (CONVERT(varchar(10),EndDate,120)+' 23:59:59')>=(@date+' 00:00:00')
group by Sys_User.ProvinceID
)shangbao
on zhuce.ProvinceID = shangbao.ProvinceID
left join
(
select Sys_User.ProvinceID,count(*) as shenheCount from Sys_ReportData_09
left join Sys_User on Sys_ReportData_09.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND (CONVERT(varchar(10),Startdate,120)+' 00:00:00')<=(@date+' 00:00:00') and (CONVERT(varchar(10),EndDate,120)+' 23:59:59')>=(@date+' 00:00:00') AND RecordStatus=4
group by Sys_User.ProvinceID
)shenhe
on zhuce.ProvinceID = shenhe.ProvinceID
)jyzk
on Dic_Organization.Id = jyzk.ProvinceID
left join
(
--地方炼厂汽柴油销售价格
select zhuce.Province,zhuceCount,shangbaoCount,shenheCount from
(
select Sys_PotrerillosInfo.Province,count(*) as zhuceCount from Rel_User_SurveyTable
left join Sys_PotrerillosInfo on Sys_PotrerillosInfo.Id = Rel_User_SurveyTable.ObjectId
left join Sys_User on Sys_User.Id = Rel_User_SurveyTable.UserId
left join Sys_UserState on Sys_UserState.UserId = Sys_User.Id
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State=5 AND tableid='10'
group by Sys_PotrerillosInfo.Province
)zhuce
left join
(
select Sys_PotrerillosInfo.Province,count(*) as shangbaoCount from Sys_ReportData_10
left join Sys_PotrerillosInfo on Sys_PotrerillosInfo.Id = Sys_ReportData_10.ObjectId
left join Sys_User on Sys_ReportData_10.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date
group by Sys_PotrerillosInfo.Province
)shangbao
on zhuce.Province = shangbao.Province
left join
(
select Sys_PotrerillosInfo.Province,count(*) as shenheCount from Sys_ReportData_10
left join Sys_PotrerillosInfo on Sys_PotrerillosInfo.Id = Sys_ReportData_10.ObjectId
left join Sys_User on Sys_ReportData_10.UserId = Sys_User.Id
left join Sys_UserState on Sys_User.Id = Sys_UserState.UserId
where Sys_User.IsValid=1 AND Sys_UserState.CategoryId=0 AND Sys_UserState.State = 5 AND ReportDate=@date AND RecordStatus=4
group by Sys_PotrerillosInfo.Province
)shenhe
on zhuce.Province = shenhe.Province
)lcjg
on Dic_Organization.Id = lcjg.Province
你的数据量应该不是很大,因为你用了大量的left join 如果是百万级的表这样关联,几分钟想做完说明
你服务器足够高端。能够直接join的,就不要用left join
实在不行后面用update 也比一直left join要好
请问,我除了将大部分不必要的left join改成join,然后我怎么优化会好些呢