资源描述：
《常用、高效、优化的sql语句》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。
1、SQL编码规范参考SqlServer1书写格式 1.1存储过程SQL文书写格式例select c.dealerCode, round(sum(c.submitSubletAmountDLR+c.submitPartsAmountDLR+c.submitLaborAmountDLR)/count(*),2)asavg, decode(null,'x','xx','CNY') from( select a.dealerCode, a.submitSubletAmountDLR, a.submitPartsAmountDLR, a.sub
2、mitLaborAmountDLR fromSRV_TWC_Fa where(to_char(a.ORIGSUBMITTIME,'yyyy/mm/dd')>='DateRange(start)' andto_char(a.ORIGSUBMITTIME,'yyyy/mm/dd')<='DateRange(end)' andnvl(a.deleteflag,'0')<>'1') unionall select b.dealerCode, b.submitSubletAmountDLR, b.submitPartsAmountDLR,
3、b.submitLaborAmountDLR fromSRV_TWCHistory_Fb where(to_char(b.ORIGSUBMITTIME,'yyyy/mm/dd')>='DateRange(start)' andto_char(b.ORIGSUBMITTIME,'yyyy/mm/dd')<='DateRange(end)' andnvl(b.deleteflag,'0')<>'1') )c groupbyc.dealerCode orderbyavgdesc;1.2SQL字符串书写格式例strSQL="insertinto
4、Snd_FinanceHistory_Tb" +"(DEALERCODE," +"REQUESTSEQUECE," +"HANDLETIME," +"JOBFLAG," +"FRAMENO," +"INMONEY," +"REMAINMONEY," +"DELETEFLAG," +"UPDATECOUNT," +"CREUSER," +"CREDATE," +"HONORCHECKNO," +"SEQ)" +"values('"+draftInputDetail.dealerCode+"'," +"'"+draftInp
5、utDetail.requestsequece+"'," +"sysdate," +"'07'," +"'"+frameNO+"'," +requestMoney+"," +remainMoney+"," +"'0'," +"0," +"'"+draftStruct.employeeCode+"'," +"sysdate," +"'"+draftInputDetail.honorCheckNo+"'," +index+")";1).缩进 对于存储过程文件，缩进为8个空格 对于SQL字符串，不可有缩进，即每一行字符串不可以空格
6、开头 2).换行 1>.Select/From/Where/Orderby/Groupby等子句必须另其一行写 2>.Select子句内容如果只有一项，与Select同行写 3>.Select子句内容如果多于一项，每一项单独占一行，在对应Select的基础上向右缩进8个空格（C#无缩进） 4>.From子句内容如果只有一项，与From同行写 5>.From子句内容如果多于一项，每一项单独占一行，在对应From的基础上向右缩进8个空格（C#无缩进） 6>.Where子句的条件如果有多项，每一个条件占一行，以AND开头，且无缩进 7>
7、.(Update)Set子句内容每一项单独占一行，无缩进 8>.Insert子句内容每个表字段单独占一行，无缩进；values每一项单独占一行，无缩进 9>.SQL文中间不允许出现空行 10>.C#里单引号必须跟所属的SQL子句处在同一行，连接符（"+"）必须在行首 3).空格 1>.SQL内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔 2>.逗号之后必须接一个空格 3>.关键字、保留字和左括号之间必须有一个空格 2.不等于统一使用"<>"。虽然SQLServer认为"!="和"<>"是等价的，都代表不等于的意义。为了统
8、一，不等于一律使用"<>"表示3.使用表的别名数据库查询，必须使用表的别名。4.SQL文对表字段扩展的兼容性
