首页 > 代码库 > 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 关键字的用法
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。