oracle与mysql数据库函数对比

刚刚接手一个新交接的项目，要将oracle库改成mysql库，有很多函数需要修改，总结几个草稿版，如下：

函数对比

orcale	mysql
nvl	ifnull
sysdate	now()
trunk	去掉
decode(t.p_type,1,t.p_code,2,t2.member_code,null)	if(t.p_type = 1,t.p_code,if(p_type=2,t2.member_code,null))
rownum	limit
to_char()	date_format(date,'%Y-%m-%d')
to_date()	str_to_date(date,'%Y-%m-%d')
str_str_to_date('"+busOrder.getStartDate()+"','yyyy-mm-dd hh24:mi:ss')	str_to_date('"+busOrder.getStartDate()+"','%Y-%m-%d %H:%i:%s')
开窗函数： select *   from (SELECT company_id,                product_code,                promo_remark,                row_number() over(PARTITION BY product_code ORDER BY company_id, create_date DESC) row_number           FROM bus_promo_remark          WHERE (start_date IS NULL OR start_date < sysdate + 1)            AND (end_date IS NULL OR end_date >= sysdate)) A  WHERE row_number = 1	SELECT *   FROM (SELECT company_id, product_code, promo_remark, rank           FROM (SELECT t.company_id,                        t.product_code,                        t.promo_remark,                        @rownum := @rownum + 1,                        IF(@pdept = t.product_code,                           @rank := @rank + 1,                           @rank := 1) AS rank,                        @pdept := t.product_code                   FROM (SELECT company_id, product_code, promo_remark                           FROM bus_promo_remark                          WHERE (start_date IS NULL OR start_date < now() + 1)                            AND (end_date IS NULL OR end_date >= now())                          ORDER BY company_id, create_date DESC) t,                        (SELECT @rownum := 0, @pdept := NULL, @rank := 0) a) r) k  WHERE rank = 1