sql 关键字的用法

本文介绍了一系列SQL技巧,包括处理NULL值、数值格式化、字符串截取及类型转换等操作,并展示了如何通过SQL语句实现数据的插入、更新及删除。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 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;

 

转载于:https://www.cnblogs.com/cwgyn/p/sqkk.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值