首页 > 代码库 > sql 关键字的用法

sql 关键字的用法

 coalesce( T.GoodsCode,‘0‘) 若 T.GoodsCode 为NULL 这 用0替换

 round(S.SaleEarning,2) 保留两位小数

 SUBSTRING(zb.accTime,9,2) 截取字符  从第9个开始截取2个

cast(SUBSTRING(‘111111111111‘,9,2) as int) 类似 Convert 强制转化

 INSERT INTO "$tableHead$".tb$tableTime$_OperatorMSum(NodeCode ,CounterCode ,OccurDate ,SellOperatorCode)      SELECT distinct B.DeptCode ,coalesce(B.CounterCode,‘‘) CounterCode ,substring(B.accTime,1,8) accTime,coalesce(S.SellOperatorCode,‘‘) SellOperatorCode      FROM "$tableHead$".tb$tableTime$_GiftGrantBill  B      left join "$tableHead$".tb$tableTime$_SaleBill S on B.DeptCode=S.DeptCode and B.CounterCode=S.CounterCode      and B.SaleBillNumber=S.BillNumber      WHERE B.BillNumber = $billNumber$and B.DeptCode  =$nodeCode$      EXCEPT SELECT NodeCode ,CounterCode ,OccurDate ,SellOperatorCode  FROM "$tableHead$".tb$tableTime$_OperatorMSum;      UPDATE  "$tableHead$".tb$tableTime$_OperatorMSum      SET      SaleMoney= case when $IsCancel$=0 then Round(C.SaleMoney + T.AddMoney,2) else Round(C.SaleMoney - 0,2) end,      SaleCount  =case when  $IsCancel$=0 then C.SaleCount + 1 else C.SaleCount end,      SaleBackMoney=case when $IsCancel$=0 then Round(C.SaleBackMoney,2) else Round(C.SaleBackMoney-T.AddMoney,2) end,      SaleBackCount=case when  $IsCancel$=0 then C.SaleBackCount else C.SaleBackCount+1 end,      SellOperatorCode = T.SellOperatorCode ,      SellOperatorName = T.SellOperatorName      from "$tableHead$".tb$tableTime$_OperatorMSum   C ,      (      SELECT      sum(B.AddMoney )   AddMoney ,      substring(B.accTime,1,8)  AccDate ,B.DeptCode DeptCode ,      coalesce(B.CounterCode,‘‘) CounterCode  ,S.SellOperatorCode  ,S.SellOperatorName,B.IsCancel      FROM "$tableHead$".tb$tableTime$_GiftGrantBill B      left join "$tableHead$".tb$tableTime$_SaleBill S on B.DeptCode=S.DeptCode and B.CounterCode=S.CounterCode      and B.SaleBillNumber=S.BillNumber      WHERE B.BillNumber = $billNumber$ and B.DeptCode  =$nodeCode$      Group By  substring(B.accTime,1,8)  ,B.DeptCode, B.CounterCode ,S.SellOperatorCode ,S.SellOperatorName,B.IsCancel      ) T      where   C.NodeCode = T.DeptCode  AND  C.CounterCode =  T.CounterCode      AND   C.OccurDate  =  T.AccDate   AND  C.SellOperatorCode =  T.SellOperatorCode;

 

sql 关键字的用法