发表于 2009-1-5 19:48
| 
Oracle鍜孲QL Server鐨勫父鐢ㄥ嚱鏁板姣
鍙戝竷: 2008-12-04 19:22 | 浣滆: 缃戠粶杞浇 | 鏉ユ簮: 缃戠粶杞浇 | 鏌ョ湅: 20娆
---------鏁板鍑芥暟
1.缁濆鍊
S:select abs(-1) value
O:select abs(-1) value from dual
2.鍙栨暣(澶)
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
3.鍙栨暣锛堝皬锛
S:select floor(-1.001) value
O:select floor(-1.001) value from dual
4.鍙栨暣锛堟埅鍙栵級
S:select cast(-1.002 as int) value
O:select trunc(-1.002) value from dual
5.鍥涜垗浜斿叆
S:select round(1.23456,4) value 1.23460
O:select round(1.23456,4) value from dual 1.2346
6.e涓哄簳鐨勫箓
S:select Exp(1) value 2.7182818284590451
O:select Exp(1) value from dual 2.71828182
7.鍙杄涓哄簳鐨勫鏁
S:select log(2.7182818284590451) value 1
O:select ln(2.7182818284590451) value from dual; 1
8.鍙10涓哄簳瀵规暟
S:select log10(10) value 1
O:select log(10,10) value from dual; 1
9.鍙栧钩鏂
S:select SQUARE(4) value 16
O:select power(4,2) value from dual 16
10.鍙栧钩鏂规牴
S:select SQRT(4) value 2
O:select SQRT(4) value from dual 2
11.姹備换鎰忔暟涓哄簳鐨勫箓
S:select power(3,4) value 81
O:select power(3,4) value from dual 81
12.鍙栭殢鏈烘暟
S:select rand() value
O:select sys.dbms_random.value(0,1) value from dual;
13.鍙栫鍙
S:select sign(-8) value -1
O:select sign(-8) value from dual -1
----------涓夎鍑芥暟鐩稿叧
14.鍦嗗懆鐜
S:SELECT PI() value 3.1415926535897931
O:涓嶇煡閬
15.sin,cos,tan 鍙傛暟閮戒互寮у害涓哄崟浣
渚嬪锛歴elect sin(PI()/2) value 寰楀埌1锛圫QLServer锛
16.Asin,Acos,Atan,Atan2 杩斿洖寮у害
17.寮у害瑙掑害浜掓崲(SQLServer锛孫racle涓嶇煡閬)
DEGREES锛氬姬搴-銆夎搴
RADIANS锛氳搴-銆夊姬搴
---------鏁板奸棿姣旇緝
18. 姹傞泦鍚堟渶澶у
S:select max(value) value from
(select 1 value
union
select -2 value
union
select 4 value
union
select 3 value)a
O:select greatest(1,-2,4,3) value from dual
19. 姹傞泦鍚堟渶灏忓
S:select min(value) value from
(select 1 value
union
select -2 value
union
select 4 value
union
select 3 value)a
O:select least(1,-2,4,3) value from dual
20.濡備綍澶勭悊null鍊(F2涓殑null浠10浠ｆ浛)
S:select F1,IsNull(F2,10) value from Tbl
O:select F1,nvl(F2,10) value from Tbl
--------瀛楃涓插嚱鏁
21.姹傚瓧绗﹀簭鍙
S:select ascii('a') value
O:select ascii('a') value from dual
22.浠庡簭鍙锋眰瀛楃
S:select char(97) value
O:select chr(97) value from dual
23.杩炴帴
S:select '11'+'22'+'33' value
O:select CONCAT('11','22')||33 value from dual
23.瀛愪覆浣嶇疆 --杩斿洖3
S:select CHARINDEX('s','sdsq',2) value
O:select INSTR('sdsq','s',2) value from dual
23.妯＄硦瀛愪覆鐨勪綅缃 --杩斿洖2,鍙傛暟鍘绘帀涓棿%鍒欒繑鍥7
S:select patindex('%d%q%','sdsfasdqe') value
O:oracle娌″彂鐜帮紝浣嗘槸instr鍙互閫氳繃绗洓涓弬鏁版帶鍒跺嚭鐜版鏁
select INSTR('sdsfasdqe','sd',1,2) value from dual 杩斿洖6
24.姹傚瓙涓
S:select substring('abcd',2,2) value
O:select substr('abcd',2,2) value from dual
25.瀛愪覆浠ｆ浛 杩斿洖aijklmnef
S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual
26.瀛愪覆鍏ㄩ儴鏇挎崲
S:Replace
O:select Translate('fasdbfasegas','fa','鎴' ) value from dual
27.闀垮害
S:len,datalength
O:length
28.澶у皬鍐欒浆鎹 lower,upper
29.鍗曡瘝棣栧瓧姣嶅ぇ鍐
S:娌″彂鐜
O:select INITCAP('abcd dsaf df') value from dual
30.宸﹁ˉ绌烘牸锛圠PAD鐨勭涓涓弬鏁颁负绌烘牸鍒欏悓space鍑芥暟锛
S:select space(10)+'abcd' value
O:select LPAD('abcd',14) value from dual
31.鍙宠ˉ绌烘牸锛圧PAD鐨勭涓涓弬鏁颁负绌烘牸鍒欏悓space鍑芥暟锛
S:select 'abcd'+space(10) value
O:select RPAD('abcd',14) value from dual
32.鍒犻櫎绌烘牸
S:ltrim,rtrim
O:ltrim,rtrim,trim
33. 閲嶅瀛楃涓
S:select REPLICATE('abcd',2) value
O:娌″彂鐜
34.鍙戦煶鐩镐技鎬ф瘮杈(杩欎袱涓崟璇嶈繑鍥炲间竴鏍凤紝鍙戦煶鐩稿悓)
S:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')
O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual
SQLServer涓敤SELECT DIFFERENCE('Smithers', 'Smythers') 姣旇緝soundex鐨勫樊
杩斿洖0-4锛4涓哄悓闊筹紝1鏈楂
--------------鏃ユ湡鍑芥暟
35.绯荤粺鏃堕棿
S:select getdate() value
O:select sysdate value from dual
36.鍓嶅悗鍑犳棩
鐩存帴涓庢暣鏁扮浉鍔犲噺
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
38.姹傛椂闂
S:select convert(char(8),getdate(),108) value
O:select to_char(sysdate,'hh24:mm:ss') value from dual
39.鍙栨棩鏈熸椂闂寸殑鍏朵粬閮ㄥ垎
S:DATEPART 鍜 DATENAME 鍑芥暟 锛堢涓涓弬鏁板喅瀹氾級
O:to_char鍑芥暟 绗簩涓弬鏁板喅瀹
鍙傛暟---------------------------------涓嬭〃闇瑕佽ˉ鍏
year yy, yyyy
quarter qq, q (瀛ｅ害)
month mm, m (m O鏃犳晥)
dayofyear dy, y (O琛ㄦ槦鏈)
day dd, d (d O鏃犳晥)
week wk, ww (wk O鏃犳晥)
weekday dw (O涓嶆竻妤)
Hour hh,hh12,hh24 (hh12,hh24 S鏃犳晥)
minute mi, n (n O鏃犳晥)
second ss, s (s O鏃犳晥)
millisecond ms (O鏃犳晥)
----------------------------------------------
40.褰撴湀鏈鍚庝竴澶
S:涓嶇煡閬
O:select LAST_DAY(sysdate) value from dual
41.鏈槦鏈熺殑鏌愪竴澶╋紙姣斿鏄熸湡鏃ワ級
S:涓嶇煡閬
O:SELECT Next_day(sysdate,7) vaule FROM DUAL;
42.瀛楃涓茶浆鏃堕棿
S:鍙互鐩存帴杞垨鑰卻elect 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;
43.姹備袱鏃ユ湡鏌愪竴閮ㄥ垎鐨勫樊锛堟瘮濡傜锛
S:select datediff(ss,getdate(),getdate()+12.3) value
O:鐩存帴鐢ㄤ袱涓棩鏈熺浉鍑忥紙姣斿d1-d2=12.3锛
SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
44.鏍规嵁宸兼眰鏂扮殑鏃ユ湡锛堟瘮濡傚垎閽燂級
S:select dateadd(mi,8,getdate()) value
O:SELECT sysdate+8/60/24 vaule FROM DUAL;
45.姹備笉鍚屾椂鍖烘椂闂
S:涓嶇煡閬
O:SELECT New_time(sysdate,'ydt','gmt' ) vaule FROM DUAL;
-----鏃跺尯鍙傛暟,鍖椾含鍦ㄤ笢8鍖哄簲璇ユ槸Ydt-------
AST ADT 澶цタ娲嬫爣鍑嗘椂闂
BST BDT 鐧戒护娴锋爣鍑嗘椂闂
CST CDT 涓儴鏍囧噯鏃堕棿
EST EDT 涓滈儴鏍囧噯鏃堕棿
GMT 鏍兼灄灏兼不鏍囧噯鏃堕棿
HST HDT 闃挎媺鏂姞鈥斿濞佸し鏍囧噯鏃堕棿
MST MDT 灞卞尯鏍囧噯鏃堕棿
NST 绾借姮鍏版爣鍑嗘椂闂
PST PDT 澶钩娲嬫爣鍑嗘椂闂
YST YDT YUKON鏍囧噯鏃堕棿
Oracle鏀寔鐨勫瓧绗﹀嚱鏁板拰瀹冧滑鐨凪icrosoft SQL Server绛変环鍑芥暟:
鍑芥暟 Oracle Microsoft SQL銆Server
鎶婂瓧绗﹁浆鎹负ASCII ASCII ASCII
瀛椾覆杩炴帴 CONCAT (expression + expression)
鎶夾SCII杞崲涓哄瓧绗 CHR CHAR
杩斿洖瀛楃涓蹭腑鐨勫紑濮嬪瓧绗︼紙宸﹁捣锛 INSTR CHARINDEX
鎶婂瓧绗﹁浆鎹负灏忓啓 LOWER LOWER
鎶婂瓧绗﹁浆鎹负澶у啓 UPPER UPPER
濉厖瀛楃涓茬殑宸﹁竟 LPAD N/A
娓呴櫎寮濮嬬殑绌虹櫧 LTRIM LTRIM
娓呴櫎灏鹃儴鐨勭┖鐧 RTRIM RTRIM
瀛楃涓蹭腑鐨勮捣濮嬫ā寮忥紙pattern锛 INSTR PATINDEX
澶氭閲嶅瀛楃涓 RPAD REPLICATE
瀛楃涓茬殑璇煶琛ㄧず SOUNDEX SOUNDEX
閲嶅绌烘牸鐨勫瓧涓 RPAD SPACE
浠庢暟瀛楁暟鎹浆鎹负瀛楃鏁版嵁 TO_CHAR STR
瀛愪覆 SUBSTR SUBSTRING
鏇挎崲瀛楃 REPLACE STUFF
灏嗗瓧涓蹭腑鐨勬瘡涓瘝棣栧瓧姣嶅ぇ鍐 INITCAP N/A
缈昏瘧瀛楃涓 TRANSLATE N/A
瀛楃涓查暱搴 LENGTH DATELENGTH or LEN
鍒楄〃涓渶澶х殑瀛楃涓 GREATEST N/A
鍒楄〃涓渶灏忕殑瀛楃涓 LEAST N/A
濡傛灉涓篘ULL鍒欒浆鎹㈠瓧涓 NVL ISNULL
鏃ユ湡鍑芥暟
鍑芥暟 Oracle Microsoft SQL銆Server
鏃ユ湡鐩稿姞 (date column +/- value) or
ADD_MONTHS DATEADD
涓や釜鏃ユ湡鐨勫樊 (date column +/- value) or
MONTHS_BETWEEN DATEDIFF
褰撳墠鏃ユ湡鍜屾椂闂 SYSDATE GETDATE()
涓涓湀鐨勬渶鍚庝竴澶 LAST_DAY N/A
鏃跺尯杞崲 NEW_TIME N/A
鏃ユ湡鍚庣殑绗竴涓懆鏃 NEXT_DAY N/A
浠ｈ〃鏃ユ湡鐨勫瓧绗︿覆 TO_CHAR DATENAME
浠ｈ〃鏃ユ湡鐨勬暣鏁 TO_NUMBER
(TO_CHAR)) DATEPART
鏃ユ湡鑸嶅叆 ROUND CONVERT
鏃ユ湡鎴柇 TRUNC CONVERT
瀛楃涓茶浆鎹负鏃ユ湡 TO_DATE CONVERT
濡傛灉涓篘ULL鍒欒浆鎹㈡棩鏈 NVL ISNULL
杞崲鍑芥暟
鍑芥暟 Oracle Microsoft SQL銆Server
鏁板瓧杞崲涓哄瓧绗 TO_CHAR CONVERT
瀛楃杞崲涓烘暟瀛 TO_NUMBER CONVERT
鏃ユ湡杞崲涓哄瓧绗 TO_CHAR CONVERT
瀛楃杞崲涓烘棩鏈 TO_DATE CONVERT
16杩涘埗杞崲涓2杩涘埗 HEX_TO_RAW CONVERT
2杩涘埗杞崲涓16杩涘埗 RAW_TO_HEX CONVERT
鍏跺畠琛岀骇鍒殑鍑芥暟
鍑芥暟 Oracle Microsoft SQL銆Server
杩斿洖绗竴涓潪绌鸿〃杈惧紡 DECODE COALESCE
褰撳墠搴忓垪鍊 CURRVAL N/A
涓嬩竴涓簭鍒楀 NEXTVAL N/A
濡傛灉exp1 = exp2, 杩斿洖null DECODE NULLIF
鐢ㄦ埛鐧诲綍璐﹀彿ID鏁板瓧 UID SUSER_ID
鐢ㄦ埛鐧诲綍鍚 USER SUSER_NAME
鐢ㄦ埛鏁版嵁搴揑D鏁板瓧 UID USER_ID
鐢ㄦ埛鏁版嵁搴撳悕 USER USER_NAME
褰撳墠鐢ㄦ埛 CURRENT_USER CURRENT_USER
鐢ㄦ埛鐜(audit trail) USERENV N/A
鍦–ONNECT BY瀛愬彞涓殑绾у埆 LEVEL N/A
鍚堣鍑芥暟
鍑芥暟 Oracle Microsoft SQL銆Server
Average AVG AVG
Count COUNT COUNT
Maximum MAX MAX
Minimum MIN MIN
Standard deviation STDDEV STDEV or STDEVP
Summation SUM SUM
Variance VARIANCE VAR or VARP
Oracle杩樻湁涓涓湁鐢ㄧ殑鍑芥暟EXTRACT,鎻愬彇骞朵笖杩斿洖鏃ユ湡鏃堕棿鎴栨椂闂撮棿闅旇〃杈惧紡涓壒瀹氱殑鏃堕棿鍩:
EXTRACT(YEAR FROM 鏃ユ湡)
