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

  • 问题

  •    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' 附近有语法错误。


    please verify my account

    2015年9月26日 5:56

答案