Oracle 与 SQL server 与mysql 语法和函数的不同

1. 放在内存中的表 在写别名是 as
sql: select * from (select * from base_org) as bs -- 可以用as

orcl: select * from (select * from base_org) bs --不用as

2. 是否为空的函数

sql: isnull(a.STORE_TYPE, '')
orcl: nvl (a.STORE_TYPE, '')

3. 在列上定义别名

sql: select storageState=

CASE WHEN storageWeek<2 THEN '低,补货' WHEN storageWeek >6
THEN '过大' end
or
CASE WHEN storageWeek<2 THEN '低,补货' WHEN storageWeek >6
THEN '过大' end as storageState

orcl: CASE WHEN storageWeek<2 THEN '低,补货' WHEN storageWeek >6
THEN '过大' end as storageState

4.转换时间为字符样式的函数



sql: SELECT convert(VARCHAR(10),(SELECT start_date FROM v_calendar),120)

orcl:

(1)日期到字符操作

select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual

select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual

select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual

参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)



(2) 字符到日期操作

select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual

5. 连接符
sql: +
orcl: ||

6. 取当前日期

sql: getdate()
orcl: sysdate
7. 分页
sql: top 20
orcl: rownum 虚拟列,是自增的整数

8. oracle
to_char 和 to_date的例子
http://gaolixu.iteye.com/blog/544967


9.mysql
日期型转成格式化的字符串
DATE_FORMAT(rm.receive_datetime,'%Y-%m-%d')

输出样式： 2013-03-28