orcale函数对应mysql中的函数

初学者恩

于 2022-06-19 12:23:07 发布

阅读量839

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/m0_47883844/article/details/125356502

版权

左边是orcale函数，右边是mysql函数

nvl 对应  ifnull
to_date 对应 str_to_date
to_char 对应 date_format
decode 对应 if
rownum 对应 limit

除了decode函数有一点小变化，其他函数都是直接替换

orcale decode函数示例

select ifnull(t.BROADBAND,0) BROADBAND,ifnull(t.SENDORDERS,0) 
SENDORDERS,ifnull(decode(artificial,0,0 
(round(sendOrders/artificial,4) * 100) ),0)
as SENDORDERSRATE from (select sum(case when 1=1 
then 1 else 0 end) BROADBAND,sum(case when  
(DISPATCH_TYPE !='10' or DISPATCH_TYPE is null)  
then 1 else 0 end) ARTIFICIAL,sum(case when  
(DISPATCH_TYPE !='10' or DISPATCH_TYPE is null) 
and (order_state>=220000 or order_state='210001') 
 then 1 else 0 end)SENDORDERS from 
 BUSINESS_OPPORTUNITY_ENTERING where order_time between 
 str_to_date('${beginTime}' || 
 '000000','yyyy-mm-dd hh24:mi:ss') 
 and str_to_date('${endTime}' || 
 '235959','yyyy-mm-dd hh24:mi:ss') ) t

转成mysql

select ifnull(t.BROADBAND,0) BROADBAND,ifnull(t.SENDORDERS,0) 
SENDORDERS,
ifnull(if(artificial=0,0,round(sendOrders/artificial,4) * 100),0)
as SENDORDERSRATE from (select sum(case when 1=1 then 1 else 0 end) 

这里只把需要改动的地方写了