刚刚接手一个新交接的项目，要将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 |