mysql 取代 oracle_oracle函数mysql替代方案

=====1.日期相关===

//获取当前日期在本周的周一

select subdate(now(),date_format(now(),'%w')-1);

//获取当前日期在本周的周日

select subdate(now(),date_format(now(),'%w')-7);

//获取月的第一天

select concat(date_format(now(), '%y-%m'),'-01');

//字符串转日期

select str_to_date('2008-4-2 15:3:28','%Y-%m-%d %H:%i:%s');

//日期转字符串

select date_format(now(),'%y-%m-%d');

=====2.分析函数====

1.lag over取当前列的上一列数据信息,类似于lag over (类似行转列的形式实现)

select max(if(rank=1,username,0)) ,max(if(rank=2,username,0)) from (

select id ,username,@rank:=@rank+1 as rank

from users,(select @rank:=0) q

order by username

) rs limit 1

2.sum over

3.rank over

select id ,username,@rank:=@rank+1 as rank

from users,(select @rank:=0) q

order by username

=====3.特殊函数====

1.nvl

IFNULL(expr1,expr2) ,如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。

2.rownum

3.decode

if(expr,value1,value2) , 如果表达式expr成立则返回value1,反之返回value2

4.merge

INSERT INTO ... ON DUPLICATE KEY UPDATE:

INSERT INTO users(id,username) select * from (select 1 as id,'zs' as username) rs ON DUPLICATE KEY UPDATE username=rs.username

REPLACE INTO:

REPLACE INTO users(ID,USERNAME) select * from (select 1 as id,'zs' as username) rs

注:replace into 是采用的先删除后插入的方式,没有被replace的值会填充为默认值.二者均需要表里面有唯一键或主键