oracle的sql改成mysql版本

Question

oracle语句改写成mysql

 select nvl(sum(is_this_month),0)                        as total,
               nvl((sum(is_this_month) - sum(is_last_month)),0) as
                                                            month_count,
               nvl((sum(is_today) - sum(is_last_day)),0)        as day_count
        from (select case
                         when to_char(TRUNC(SYSDATE - 1), 'dd') = to_char(VALID_DATE, 'dd') then 1
                         else 0 end as
                         is_last_day,
                     case
                         when to_char(sysdate, 'dd') = to_char(VALID_DATE, 'dd') then 1
                         else 0 end as
                         is_today,
                     case
                         when to_char(sysdate, 'MM') = to_char(VALID_DATE, 'MM') then 1
                         else 0 end as
                         is_this_month,
                     case
                         when to_char(TRUNC(add_months(trunc(sysdate), -1), 'MM'), 'MM') = to_char(VALID_DATE, 'MM')
                             then 1
                         else 0 end as
                         is_last_month
              from (SELECT *
                    FROM API_SERVICE
                    WHERE VALID_DATE is not null
                      and VALID_DATE > TRUNC(ADD_MONTHS(sysdate, -1), 'mm'))) b

如何改写成mysql版本

Answer 1

select sum(ifnull(is_this_month,0)) total,
             sum(ifnull(is_this_month,0)) - sum(ifnull(is_last_month,0)) month_count,
             sum(ifnull(is_today,0)) - sum(ifnull(is_last_day,0)) day_count
from (select
  case when Day(now()) - 1 = Day(VALID_DATE) then 1 else 0 end is_last_day,
    case when Day(now()) = Day(VALID_DATE) then 1 else 0 end     is_today,
    case when Month(now()) == Month(VALID_DATE) then 1 else 0 end is_this_month,
    case when Month(now() - 1) = Day(VALID_DATE) then 1 else 0 end is_last_month
from (SELECT *
    FROM API_SERVICE
    WHERE VALID_DATE != null
        and VALID_DATE >  Month(now()) - 1)) b

尽力了，你试试