左边是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)
这里只把需要改动的地方写了
还有补充的欢迎评论