Create Table table_name (SERIALNO BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1 , INCREMENT BY +1 , NO CACHE ), ID BIGINT NOT NULL generated always as identity (start with 1, increment by 1))
1 取前n条记录
SQL Server:
Select top n * from xtable
Oracle:
Select * from xtable where rownum <=n
DB2:
Select * from xtable fetch first n rows only
IQ:
Select top n * from xtable
注意 sqlserver和IQ是一样都是用 top n *;
2取当前日期
SQL Server:
Select getdate()
Oracle:
Select sysdate from dual
DB2:
Select current timestamp from sysibm.sysdummy1
IQ:
Select getdate()
注意sqlserver和IQ是一样都是没有伪表, oracle伪表是dual, db2是sysibm.sysdummy1
3 连接字符串
SQL Server:
Select 'Hello'+'Toone'
Oracle:
Select 'Hello'||'Toone' from dual
DB2:
Select 'Hello'||'Toone' from sysimb.sysdummy1
IQ:
Select 'Hello'||'Toone'
4空值转换
SQL Server:
SELECT userid,username,isnull(email,'0') FROM AUTH_USER
Oracle:
SELECT userid,username,nvl(email,'0') FROM AUTH_USER
DB2:
SELECT userid,username,value(email,'0') FROM AUTH_USER
IQ:
SELECT userid,username,isnull(email,'0') FROM AUTH_USER
5类型转换
SQL Server:
SELECT convert(varchar,getdate(),20)
Oracle:
SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
DB2:
SELECT varchar(current timestamp) from sysibm.sysdummy1
IQ:
SELECT cast(字段名,varchar(50)) from table
SELECT convert(varchar, 20) from table
注1:
■SQL Server中转换日期格式改变Style参数:20 ;
■Oracle中转换日期格式改变格式化参数:yyyy-mm-dd hh24:mi:ss
‘yyyy’、’mm’、’dd’、’hh12’、’hh24’、’mi’、’ss’等;另外当前时间可以从sysdate里取.
■Db2中转换日期格式改变系统常量:current timestamp
‘current date’、’current time’等;
注2:
■SQL Server数据类型改变“数据类型参数”:int、varchar等;
■Oracle数据类型改变函数:to_char()、to_date()、to_number()等;
■DB2数据类型改变函数:varchar()、int()、date()、time()等;
6 取值判断
SQL Server:
select caseConvert =
case when g.master_type ='system'?? then '管理员'
when g.master_type ='roletype' then '特殊角色'
else '普通用户'
end
from global_code g
Oracle:
Select case g.master_type
when 'system'?? then '管理员'
when 'roletype' then '特殊角色'
else '普通用户'
end
AS caseConvert
from global_code g
DB2:
Select case g.master_type
when 'system'?? then '管理员'
when 'roletype' then '特殊角色'
else '普通用户'
end
AS caseConvert
From global_code g
IQ:
也是用case when else end
7 位置
SQL Server:
select charindex('E','ABCDEF')
select patindex('%E%','ABCDEF')
Oracle:
select instr('ABCDEF','E') from dual
DB2:
Select locate('E','ABCDEF') from sysibm.sysdummy1
替换函数:
IQ:
Select replace(‘asdf’,’a’,’’) from table
日期分解函数
DB2
Select Date(date(‘2006-11-21’) +1 month ) from table 这个是月增1。
oracle:
select add_months(sysdate,1) from dual;
2008-3-26 23:55:28
LAST_DAY(d)
--时间点d当月份最后一天
ex.
select sysdate, LAST_DAY(sysdate) LAST_DAY from dual;
NEXT_DAY(d,number)
--◎ 时间点d开始,下一个星期几的日期
--◎ 星期日 = 1 星期一 = 2 星期二 = 3
-- 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7
ex.
select sysdate, NEXT_DAY(sysdate,2) aa from dual;
SYSDATE AA
---------- ----------
21-SEP-07 24-SEP-07
MONTHS_BETWEEN(d1,d2)
--计算d1与d2相隔的月数
ex.
select trunc(MONTHS_BETWEEN(to_date('20071101','yyyymmdd'),
to_date('20070820','yyyymmdd'))) aa
from dual;
37.求日期
S:select convert(char(10),getdate(),20) value
O:select trunc(sysdate) value from dual
select to_char(sysdate,'yyyy-mm-dd') value from dual http://www.mscto.com
38.求时间
S:select convert(char(8),getdate(),108) value http://www.mscto.com
O:select to_char(sysdate,'hh24:mm:ss') value from dual
42.字符串转时间
43.求两日期某一部分的差(比如秒)
S:select datediff(ss,getdate(),getdate() 12.3) value
O:直接用两个日期相减(比如d1-d2=12.3)
SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
下面是左右连接:
A Left join B on ------------------------以左边为准,冶呙挥械牟筺ull
A Right join B on ------------------------以右边为准,左边没有的补null
A(+)=B 右连接
S:可以直接转或者select cast('2004-09-08'as datetime) value
O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;