如何肢解该“SQL语句”,该怎么办?简单的讲,怎么样才能分析透彻???
这个Sql语句,是根据不同的查询条件,用“StringBuilder”拼接出来的,用来返回“图/报表”所需的数据。现在想分析下拼接过程,就要先把该“Sql语句”肢解了,语句如下:
select ISNULL(D.SN,'2011') SN,D.U,U.V,UX.ECV,UCX.V CUV, D.E,'' EN,'' R,'' RN,'' F,'' FN, I.B, I.BN,'' D,'' DN,'' C, '总量' CN,[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12] from (select B.buildingno as B,B.BuildingName as BN from Building B where B.buildingno in ('310000Fa001')) I left join (/*D*/select T.SN,T.U,T.E,T.B,sum(isnull([01],0)) [01],sum(isnull([02],0)) [02],sum(isnull([03],0)) [03],sum(isnull([04],0)) [04],sum(isnull([05],0)) [05],sum(isnull([06],0)) [06],sum(isnull([07],0)) [07],sum(isnull([08],0)) [08],sum(isnull([09],0)) [09],sum(isnull([10],0)) [10],sum(isnull([11],0)) [11],sum(isnull([12],0)) [12] from (select A.* from (/*AA*/select AA.* from (select m.meterno M,m.metertype as E,m.Unit as U,m.meterusetype as C,m.regionno as R,m.buildingfunction as F,m.buildingno as B,m.usedepartment as D,V.SN,V.DD,sum(V.sumqty) as sumqty from (select mn.meterno,mn.metertype,mn.Unit,mn.meterusetype,rb.regionno,b.buildingfunction,mn.buildingno,mn.usedepartment from Meter mn inner join vw_Meter4Report v on mn.meterno = v.meterno and mn.buildingno = v.buildingno inner join Building b on mn.buildingno = b.buildingno inner join RegionBuildingRef rb on b.buildingno = rb.buildingno) m inner join (select meterno,metertype,dayear as SN,damonth as DD,sumqty from EnergyDataSumByMonthN) as V on m.meterno = V.meterno and m.metertype = V.metertype group by m.meterno,m.metertype,m.Unit,m.meterusetype,m.regionno,m.buildingfunction,m.buildingno,m.usedepartment,V.SN,V.DD) as P pivot (sum(P.sumqty) for P.DD in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12] )) as AA/*AA*/ UNION ALL /*AB*/select AB.* from (select m.meterno as M,m.metertype as E,m.Unit as U,m.meterusetype as C,m.regionno as R,m.buildingfunction as F,m.buildingno as B,m.usedepartment as D,m.SN,m.DD,sum(m.sumqty) as sumqty from (select /*mn.meterno*/'' as meterno,mn.energytype as metertype,mn.unit,'' as meterusetype,mn.regionno,b.buildingfunction,mn.buildingno,'' as usedepartment,substring(mn.YearMonth,1,4) SN,substring(mn.YearMonth,5,2) DD,mn.sumqty from EnergyDataDetailManualN mn inner join Building b on mn.BuildingNo = b.BuildingNo where mn.BuildingNo != '' and mn.MeterNo = '' and LEN(mn.YearMonth) = 6 ) m group by m.meterno,m.metertype,m.Unit,m.meterusetype,m.regionno,m.buildingfunction,m.buildingno,m.usedepartment,m.SN,m.DD) as P pivot (sum(P.sumqty) for P.DD in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12] )) as AB/*AB*/ ) as A where A.SN in ('2011') and A.E = 'EM' and A.B in ('310000Fa001')) T group by T.SN,T.U,T.E,T.B/*D*/) D on I.B = D.B left join (select uc.UnitNo as U,uv.ConversionFactor as V from UnitConfig uc inner join UnitConversion uv on uc.UnitNo = uv.FromUnitNo where uv.ToUnitNo='MJ') as U on D.U = U.U left join (select EE.EnergyNo E, EE.EnergyUnitNo as U, EE.CarbonUnitNo CU, EE.Carbon as ECV from EnergyExchangeCarbon EE) as UX on UX.U = D.U and UX.E = D.E left join (select uc.UnitNo as U,uv.ConversionFactor as V from UnitConfig uc inner join UnitConversion uv on uc.UnitNo = uv.FromUnitNo where uv.ToUnitNo='TON') as UCX on UCX.U = UX.CU
要看大师的了,谢谢。。。
------解决方案--------------------------------------------------------
估计用正则都很麻烦
------解决方案--------------------------------------------------------
这个正则相当麻烦啊。。。LZ确定要这样吗。。。换个其他方法乃
------解决方案--------------------------------------------------------
字符串一连接了就是一个新字符串,你只能从自己的业务下手,不可能有通用的方法。
------解决方案--------------------------------------------------------
我看得想吐血
------解决方案--------------------------------------------------------
改写吧,这个不用存储过程说不过去了。
程序中只负责拼接Where条件。
尽量把他拆分成多个存储过程,不然维护起来要命。
------解决方案--------------------------------------------------------