Oracle与MySQL语法转换:日期处理、分组合并、空值排序、分支条件判断、字符串合并

前言

Oracle与MySQL语法转换：日期处理、分组合并、空值排序、分支条件判断、字符串合并
场景：系统改造，需要由Oracle切换为MySQL，因而要对代码中的Oracle语法的sql调整为MySQL语法
博客地址：芒果橙的个人博客 【http://mangocheng.com】

sysdate–当前日期

Oracle

使用sysdate

select sysdate from dual;

MySQL

使用sysdate()、now()

select sysdate();
select now();

---

格式化日期

Oracle

使用to_char(sysdate,‘yyyy-MM-dd’)
to_char(sysdate,‘d’)：一周的第几天，从星期日开始1-7

select to_char(sysdate,'yyyy-MM-dd') as 日期 from dual;
select to_char(sysdate,'d') as 星期,sysdate as 日期 from dual;

MySQL

使用date_format(sysdate(),‘%Y-%m-%d’)
dayofweek(sysdate())：一周的星期，星期日开始，1-7

select date_format(sysdate(),'%Y-%m-%d');
select dayofweek(sysdate()) as 星期,sysdate() as 日期 from dual;

---

日期加减

Oracle

日期相加/减，直接使用加/减号字符 +/-

-- 日期相减，注意1是星期日
select sysdate - (to_char(sysdate-1,'d')) as 前天,to_char(sysdate - (to_char(sysdate-1,'d')),'d') as 前天星期,to_char(sysdate-1,'d') as 昨天星期,to_char(sysdate,'d') as 今天星期 from dual; 

MySQL

date_add(sysdate(), interval 1 day)：加一天
date_sub(sysdate(), interval 1 day)：减一天

select date_sub(sysdate(),interval 1 day) as 昨天;
select date_add(sysdate(),interval 1 day) as 明天;
-- 日期相减，注意1是星期日
select date_sub( sysdate(), interval (dayofweek(sysdate()) -1 ) day) as 前天,
	dayofweek(date_sub( sysdate(), interval (dayofweek(sysdate()) -1 ) day)) as 前天星期,
    dayofweek(sysdate()) as 星期;

---

月份的加减

Oracle

add_months：月份加/减

-- 月份加减
select add_months(sysdate,-1) as 上一个月日期,
       add_months(sysdate,1) as 下一个月日期 from dual;

MySQL

adddate(now(),interval 1 month)：月份加/减

select adddate(now(),interval -1 month) as 上个月日期;
select adddate(now(),interval 1 month) as 下个月日期;

---

临时表使用别名

Oracle

可以不需要别名：对于子查询不需要额外定义别名

select * from (
   select sysdate from dual
);

MySQL

必须定义别名：对于子查询需要额外定义别名，否则会报错：Error Code: 1248. Every derived table must have its own alias

定义别名t则查询正常

---

wm_concat–多列分组合并

Oracle

wm_concat：分组合并

select wm_concat(login_name) from user_account group by creator;

MySQL

group_concat

select group_concat(login_name) from user_account group by creator;

---

nlssort–排序

Oracle

nlssort(字段名,‘NLS_SORT=SCHINESE_PINYIN_M’)：按照拼音排序

select * from org_user order by nlssort(user_name,'NLS_SORT=SCHINESE_PINYIN_M');

MySQL

convert(字段名 using gbk) asc

select * from org_user order by convert(user_name using gbk) asc;

---

nvl、nvl2–空值取值

Oracle

nvl(字段a,‘名称1’)：当字段a为空时，取值为"名称1"
nvl2(字段a,‘名称1’,‘名称2’)：当字段a为空时，取值为"名称2"，否则取值为"名称1"

select nvl(leader_names,'名称1'),leader_names from org_user;

select nvl2(leader_names,'名称1','名称2'),leader_names from org_user;

MySQL

ifnull(字段a,‘名称1’)：字段a为空，取值为"名称1"
If( isnull(字段a),‘名称2’,‘名称1’)：字段a为空，取值为"名称2"，非空则取值为"名称1"，注意参数的顺序

select ifnull(leader_names,'名称1'),leader_names from org_user;

select if(isnull(leader_names),'名称2','名称1'),leader_names from org_user;

---

decode–分支条件判断

Oracle

decode(条件字段,值1,返回值1,值2,返回值2,[值n,返回值n]缺省值)：条件字段值为值1时，取值为"返回值1"，条件字段值为值2时，取值为"返回值2"，都不满足时取值为"缺省值"

select t.work_date,to_char (t.work_date, 'd'),
          decode (
            to_char (t.work_date, 'd'),  -- 取值1周的第几天，从星期日开始
            1, '星期日',
            2, '星期一',
            '缺省值'
          ) as week
        from
          uf_oa_work_diary t

MySQL

case 条件字段 when 条件1 then 返回值1 when 条件2 then 返回值2 else 缺省值 end：当条件1满足则取值为"返回值1"，当条件2满足则取值为"返回值2"，都不满足则取值为"缺省值"

select t.work_date,date_format(t.work_date, '%w'), -- 周的天 （0=星期日, 6=星期六）
          (case date_format (t.work_date, '%w')
            when 0 then '星期日'
            when 1 then '星期一'
            when 2 then '星期二'
            else '缺省值'
		   end
          ) as week
        from
          mydb.work_diary t;

---

nulls first、null last–空值排序

Oracle

nulls first、null last：空值排在最前面；空值排最后面

select t.leader_names as 排序字段,t.* from org_user t order by leader_names nulls first;

MySQL

if(isnull(排序字段),0,1)：空值排在最前面
if(isnull(排序字段),1,0)：空值排在最后面

select t.leader_names as 排序字段,t.* from org_user t order by if(isnull(t.leader_names),0,1);

---

||–合并字符

Oracle

字符a || 字符b：字符a和字符b合并

select 'a' || 'b' from dual;
select 'a' || ',' || 'b' from dual;
select concat('a','b') from dual;

MySQL

concat(字符a,字符b)：字符a和字符b合并
concat(连接符,字符a,字符b)：指定连接的符号，字符a和字符b合并

select concat('a','b');
select concat_ws(',','a','b');