常用、高效、优化的SQL语句VIP免费

SQL 编码规范 参考Sql Server 1 书写格式 1.1 存储过程SQL 文书写格式例 select c.dealerCode, round(sum(c.submitSubletAmountDLR + c.submitPartsAmountDLR + c.submitLaborAmountDLR) / count(*), 2) as avg, decode(null, 'x', 'xx', 'CNY') from ( select a.dealerCode, a.submitSubletAmountDLR, a.submitPartsAmountDLR, a.submitLaborAmountDLR from SRV_TWC_F a where (to_char(a.ORIGSUBMITTIME, 'yyyy/mm/dd') >= 'Date Range(start)' and to_char(a.ORIGSUBMITTIME, 'yyyy/mm/dd') <= 'Date Range(end)' and nvl(a.deleteflag, '0') <> '1') union all select b.dealerCode, b.submitSubletAmountDLR, b.submitPartsAmountDLR, b.submitLaborAmountDLR from SRV_TWCHistory_F b where (to_char(b.ORIGSUBMITTIME, 'yyyy/mm/dd') >= 'Date Range(start)' and to_char(b.ORIGSUBMITTIME,'yyyy/mm/dd') <= 'Date Range(end)' and nvl(b.deleteflag,'0') <> '1') ) c group by c.dealerCode order by avg desc; 1.2 SQL 字符串书写格式例 strSQL = "insert into Snd_FinanceHistory_Tb " + "(DEALERCODE, " + "REQUESTSEQUECE, " + "HANDLETIME, " + "JOBFLAG, " + "FRAMENO, " + "INMONEY, " + "REMAINMONEY, " + "DELETEFLAG, " + "UPDATECOUNT, " + "CREUSER, " + "CREDATE, " + "HONORCHECKNO, " + "SEQ) " + "v alu es ('" + draftInpu tDetail.dealerCode + "', " + "'" + draftInpu tDetail.requ estsequ ece + "', " + "sy sdate, " + "'07', " + "'" + frameNO + "', " + requ estMoney + ", " + remainMoney + ", " + "'0', " + "0, " + "'" + draftStru ct.employ eeCode + "', " + "sy sdate, " + "'" + draftInpu tDetail.honorCheckNo + "', " + index + ")"; 1).缩进 对于存储过程文件，缩进为8 个空格 对于SQL 字符串，不可有缩进，即每一行字符串不可以空格开头 2).换行 1>.Select/From/Where/Order by /Grou p by 等子句必须另其一行写 2>.Select 子句内容如果只有一项，与 Select 同行写 3>.Select 子句内容如果多于一项，每一项单独占一行，在对应 Select 的基础上向右缩...