sqlserver里，case when和cast函数一起使用

- 日理万妓 2023-05-22 06:33 71阅读 0赞

sqlserver里，查询order_id,amount，如果Transaction_Amount< Order_Amount 就返回 Order_Amount，否则就返回Transaction_Amount，并且除以一百保留两位小数的sql

  1. select order_id, 
  2. case when  Transaction_Amount < Order_Amount 
  3. then cast((Order_Amount/100.0) as decimal(12,2))
  4. else cast((Transaction_Amount/100.0) as decimal(12,2))
  5. end as  amount
  6. from xxxx
  7. where  Transaction_Amount != Order_Amount
  8. order by Transaction_Time desc
  9. offset 0 rows fetch next 50 rows only

在上述的问题上在加上sum计算

  1. select isNull(cast(SUM((case when (Order_Amount > Transaction_Amount) then Order_Amount
  2.             else Transaction_Amount end -  Refund_Amount))/100.0  as decimal(12,2)),0) as amount
  3. from xxxx
  4. where Insert_Time > '2020-04-16 00:00' and Insert_Time < '2020-04-16 12:00'

发表评论

表情：
评论列表 （有 0 条评论，71人围观）

还没有评论，来说两句吧...

相关阅读