首页 新闻 搜索 专区 学院

大哥给看看这段c#里的sql的怎么改

0
[已解决问题] 解决于 2016-06-04 22:53
   StringBuilder sbSql = new StringBuilder();
            sbSql.Append(@"EXEC('select ID,PumpingName,ProLocat,ConsUnit,CreatedYear,DesignUnits,DrainArea,IrriqWater,ConArea,Enggrade,IrrigaStrutype,InfrastruType,ConstruArea,IrrigaDesign,IrrigaStatus,DrainDesign,DrainStatus,DesignFlow,StatusFlow,StatDesign,StatStatus,SituaDesign,SituaStatus,HeadDesign,HeadStatus,LiftsDesign,LiftsStatus,TotalDesign,TotalSitua,PaddyDesign,DrainSitua,DryDesign,GlebeStatus,TotalIrriDes,TotalIrriSta,PaddyDes,IrigaSta,IrriDesign,IrriStat, (select Max(cnt) from
( 
select count(NULLIF(PumpModel,"")) as cnt  from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(NULLIF(PumpManu,"")) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(NULLIF(PumpUnits,"")) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(NULLIF(PumpTime,"")) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(NULLIF(PumpPower,"")) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
  
) t) PumpUnits,
 (select Max(cnt) from
( 
select count(EquipType) as cnt  from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(MachManuf) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(MachUnits) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(MachTime) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(PowerEquip) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
  
) t) MachUnits,EarthCumula,InitialWork,StoneWork,StoneInit,ConcrCumula,IniConSqu,Otheraccumu,Initial,SccumulaTotal,RaisedTotal,Sccumula,Sinitial,Raised,Initialself,Affiliation,Staff,Annualcost,CostSources,OperatStatus,AnnopeTime,AveragePower,IrDrID,IrDrName,Stake,DrainageID,DrainageName,IrrigationID,IrrigationName,ExistingProblems,PicCode,Remarks,Locations,ThreeCode,FMDS_Water_IrrigaDrainageStat.Longitude,FMDS_Water_IrrigaDrainageStat.Latitude,FMDS_Water_IrrigaDrainageStat.OrganiseUnitID,
FMDS_Water_IrrigaDrainageStat.OrganiseUnitName,FMDS_Water_IrrigaDrainageStat.CreatedBy,FMDS_Water_IrrigaDrainageStat.CreatedDate,FMDS_Water_IrrigaDrainageStat.ModifiedBy,FMDS_Water_IrrigaDrainageStat.ModifiedDate
                    from [FMDS_Water_IrrigaDrainageStat] 
left join SDMS_OrganiseUnit on  SDMS_OrganiseUnit.OrganiseUnitID=FMDS_Water_IrrigaDrainageStat.OrganiseUnitID'+ @Condition +' '+@SortBy)");

程序运行的时候@Condition的值是

where 1=1  and  FMDS_WATER_IRRIGADRAINAGESTAT.ISDELETE = 0  AND  FMDS_WATER_IRRIGADRAINAGESTAT.ORGANISEUNITID 
in (select OrganiseUnitID from SDMS_OrganiseUnit where OrganiseUnitCode 
like '%'+(select OrganiseUnitCode from SDMS_OrganiseUnit where OrganiseUnitID=504)+'%'
 and OrganiseUnitModel=1 and IsDelete=0)  AND  FMDS_WATER_IRRIGADRAINAGESTAT.ORGANISEUNITID = '504'  AND  FMDS_WATER_IRRIGADRAINAGESTAT.ISDELETE = 0

问题出在这里count(NULLIF(PumpModel,""))
如果我用双引号,报错:
以 ')) as cnt  from FMDS_Water_IrrigaDrainageStat where 1=1  and  FMDS_WATER_IRRIGADRAINAGESTAT.ISDELETE = 0  AND  FMDS_WATER_IRRIGA' 开头的 标识符 太长。最大长度为 128。
单引号:报错
'504' 附近有语法错误。
关键字 'AND' 附近有语法错误。

lctk的主页 lctk | 初学一级 | 园豆:69
提问于:2015-09-26 13:54
< >
分享
最佳答案
0

@Condition的值中的单引号没有转义掉,应该换成  ''

如:FMDS_WATER_IRRIGADRAINAGESTAT.ORGANISEUNITID = '504'

应该写为:FMDS_WATER_IRRIGADRAINAGESTAT.ORGANISEUNITID = ''504''

奖励园豆:5
izan | 初学一级 |园豆:184 | 2015-09-26 15:05
其他回答(2)
0

AppendFormat好像拼接字符串更直观一点

JackWang-CUMT | 园豆:2826 (老鸟四级) | 2015-09-26 15:40
0

你用StringBuider拼接好看一点,头大

如此低调的男人 | 园豆:840 (小虾三级) | 2015-09-28 20:03
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册