oracle+110個(gè)常用函數(shù)

1、1. ASCII返回與指定的字符對應(yīng)的十進(jìn)制數(shù) ;SQL select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;A A ZERO SPACE65 97 48 322. CHR給出整數(shù) , 返回對應(yīng)的字符 ;SQL select chr(54740) zhao,chr(65) chr65 from dual;ZH C趙A3. CONCAT連接兩個(gè)字符串 ;SQL select concat(010-,88888888)|轉(zhuǎn) 23 高乾競電話 from dual;高乾競電轉(zhuǎn) 234. IN

2、ITCAP 返回字符串并將字符串的第一個(gè)字母變?yōu)榇髮?SQL select initcap(smith) upp from dual; UPPSmith5.INSTR(C1,C2,I,J)在一個(gè)字符串中搜索指定的字符 ,返回發(fā)現(xiàn)指定的字符的位置 C1 被搜索的字符串C2 希望搜索的字符串I 搜索的開始位置 , 默認(rèn)為 1J 出現(xiàn)的位置 , 默認(rèn)為 1SQL select instr(oracle traning,ra,1,2) instring from dual; INSTRING96. LENGTH返回字符串的長度 ;fromSQL select name,length(name),add

3、r ,length(addr),sal,length(to_char(sal) gao.nchar_tst;NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)高乾競 3 北京市海錠區(qū) 6 9999.99 77. LOWER返回字符串 , 并將所有的字符小寫SQL select lower(AaBbCcDd)AaBbCcDd from dual; AABBCCDDaabbccdd8. UPPER返回字符串 , 并將所有的字符大寫SQL select upper(AaBbCcDd) upper from dual; UPPERAA

4、BBCCDD9. RPAD 和 LPAD( 粘貼字符 )RPAD 在列的右邊粘貼字符LPAD 在列的左邊粘貼字符SQL select lpad(rpad(gao,10,*),17,*)from dual;LPAD(RPAD(GAO,1*gao*不夠字符則用 * 來填滿10. LTRIM 和 RTRIMLTRIM 刪除左邊出現(xiàn)的字符串RTRIM 刪除右邊出現(xiàn)的字符串SQL select ltrim(rtrim( gao qian jing , ), ) from dual; LTRIM(RTRIM( gao qian jing11.SUBSTR(string,start,count)取子字符串

5、,從 start 開始 ,取 count 個(gè)SQL select substr3,8) from dual; SUBSTR( 08888888 12.REPLACE(string,s1,s2)string 希望被替換的字符或變量s1 被替換的字符串s2 要替換的字符串SQL select replace(he love you,he,i) from dual;REPLACE(H i love you13.SOUNDEX 返回一個(gè)與給定的字符串讀音相同的字符串SQL create table table1(xm varchar(8);SQL insert into ta

6、ble1 values(weather);SQL insert into table1 values(wether);SQL insert into table1 values(gao);SQL select xm from table1 where soundex(xm)=soundex(weather); XM weatherwether? 14.TRIM(s from string)LEADING 剪掉前面的字符TRAILING 剪掉后面的字符 如果不指定 , 默認(rèn)為空格符15. ABS 返回指定值的絕對值SQL select abs(100),abs(-100) from dual;A

7、BS(100) ABS(-100) 100 10016. ACOS給出反余弦的值SQL select acos(-1) from dual;ACOS(-1) 3.141592717. ASIN 給出反正弦的值SQL select asin(0.5) from dual;ASIN(0.5) .5235987818. ATAN 返回一個(gè)數(shù)字的反正切值SQL select atan(1) from dual;ATAN(1).7853981619. CEIL返回大于或等于給出數(shù)字的最小整數(shù)SQL select ceil(3.1415927) from dual;CEIL(3.1415927)420.

8、COS 返回一個(gè)給定數(shù)字的余弦SQL select cos(-3.1415927) from dual;COS(-3.1415927)-121. COSH 返回一個(gè)數(shù)字反余弦值SQL select cosh(20) from dual;COSH(20) 24258259822. EXP返回一個(gè)數(shù)字 e 的 n 次方根SQL select exp(2),exp(1) from dual;EXP(2) EXP(1) 7.3890561 2.718281823. FLOOR 對給定的數(shù)字取整數(shù)SQL select floor(2345.67) from dual;FLOOR(2345.67) 234

9、524. LN 返回一個(gè)數(shù)字的對數(shù)值SQL select ln(1),ln(2),ln(2.7182818) from dual;LN(1) LN(2) LN(2.7182818) 0 .69314718 .9999999925. LOG(n1,n2) 返回一個(gè)以 n1 為底 n2 的對數(shù)SQL select log(2,1),log(2,4) from dual;LOG(2,1) LOG(2,4) 0 226. MOD(n1,n2) 返回一個(gè) n1 除以 n2 的余數(shù)SQL select mod(10,3),mod(3,3),mod(2,3) from dual; MOD(10,3) MOD

10、(3,3) MOD(2,3)1 0 227. POWER 返回 n1 的 n2 次方根SQL select power(2,10),power(3,3) from dual;POWER(2,10) POWER(3,3) 1024 2728. ROUND 和 TRUNC按照指定的精度進(jìn)行舍入SQL select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5) 56 -55 55 -5529.SIGN取數(shù)字n的符號，大于0返回1

11、,小于0返回-1,等于0返回0SQL select sign(123),sign(-100),sign(0) from dual;SIGN(123) SIGN(-100) SIGN(0) 1 -1 030.SIN返回一個(gè)數(shù)字的正弦值SQL select sin(1.57079) from dual;SIN(1.57079) 1? 31.SIGH 返回雙曲正弦的值SQL select sin(20),sinh(20) from dual;SIN(20) SINH(20) .91294525 24258259832.SQRT 返回?cái)?shù)字 n 的根SQL select sqrt(64),sqrt(10

12、) from dual;SQRT(64) SQRT(10) 8 3.162277733. TAN 返回?cái)?shù)字的正切值SQL select tan(20),tan(10) from dual;TAN(20) TAN(10) 2.2371609 .6483608334. TANH返回?cái)?shù)字 n 的雙曲正切值SQL select tanh(20),tan(20) from dual;TANH(20) TAN(20) 1 2.237160935. TRUNC 按照指定的精度截取一個(gè)數(shù)SQL select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from d

13、ual;TRUNC1 TRUNC(124.16666,2) 100 124.1636. ADD_MONTHS增加或減去月份SQL select to_char(add_months(to_date(199912,yyyymm),2),yyyymm) from dual; TO_CHA 200002SQL select to_char(add_months(to_date(199912,yyyymm),-2),yyyymm) from dual;TO_CHA 19991037. LAST_DAY返回日期的最后一天SQL select to_char(sysdate,yyyy.mm.dd),to_

14、char(sysdate)+1,yyyy.mm.dd) from dual;TO_CHAR(SY TO_CHAR(S2004.05.09 2004.05.10SQL select last_day(sysdate) from dual;LAST_DAY(S31-5 月 -0438. MONTHS_BETWEEN(date2,date1)給出 date2-date1 的月份SQL select months_between(19-12月 -1999,19-3 月 -1999) mon_between from dual;MON_BETWEEN 9SQLselectmonths_between(t

15、o_date(2000.05.20,yyyy.mm.dd),to_date(2005.05.20,yyyy. mm.dd) mon_betw from dual;MON_BETW -6039. NEW_TIME(date,this,that)給出在 this 時(shí)區(qū) =other 時(shí)區(qū)的日期和時(shí)間SQL select to_char(sysdate,yyyy.mm.dd hh24:mi:ss) bj_time,to_char(new_time2 (sysdate,PDT ,GMT),yyyy.mm.dd hh24:mi:ss) los_angles from dual; BJ_TIME LOS_

16、ANGLES 2004.05.09 11:05:32 2004.05.09 18:05:3240. NEXT_DAY(date,day) 給出日期 date 和星期 x 之后計(jì)算下一個(gè)星期的日期 SQL select next_day(18-5月-2001, 星期五 ) next_day from dual;NEXT_DAY 25-5 月 -0141.SYSDATE 用來得到系統(tǒng)的當(dāng)前日期SQL select to_char(sysdate,dd-mm-yyyy day) from dual;TO_CHAR(SYSDATE, 09-05-2004 星期日trunc(date,fmt) 按照給出

17、的要求將日期截?cái)?,如果 fmt=mi 表示保留分 , 截?cái)嗝?SQL select to_char(trunc(sysdate,hh),yyyy.mm.dd hh24:mi:ss) hh,2 to_char(trunc(sysdate,mi),yyyy.mm.dd hh24:mi:ss) hhmm from dual; HH HHMM 2004.05.09 11:00:00 2004.05.09 11:17:0042. CHARTOROWID將字符數(shù)據(jù)類型轉(zhuǎn)換為 ROWID 類型SQL select rowid,rowidtochar(rowid),ename from scott.emp;

18、ROWID ROWIDTOCHAR(ROWID) ENAMEAAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITHAAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLENAAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARDAAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES43. CONVERT(c,dset,sset)將源字符串 sset 從一個(gè)語言字符集轉(zhuǎn)換到另一個(gè)目的 dset 字符集SQL select convert(strutz,we8hp,f7dec)

19、conversion from dual; conver strutz44. HEXTORAW 將一個(gè)十六進(jìn)制構(gòu)成的字符串轉(zhuǎn)換為二進(jìn)制45. RAWTOHEXT 將一個(gè)二進(jìn)制構(gòu)成的字符串轉(zhuǎn)換為十六進(jìn)制46. ROWIDTOCHAR將 ROWID 數(shù)據(jù)類型轉(zhuǎn)換為字符類型47. TO_CHAR(date,format)SQL select to_char(sysdate,yyyy/mm/dd hh24:mi:ss) from dual;TO_CHAR(SYSDATE,YY 2004/05/09 21:14:41? 48.TO_DATE(string,format)將字符串轉(zhuǎn)化為 ORACLE 中的

20、一個(gè)日期49. TO_MULTI_BYTE 將字符串中的單字節(jié)字符轉(zhuǎn)化為多字節(jié)字符 SQL select to_multi_byte(高 ) from dual;TO50. TO_NUMBER 將給出的字符轉(zhuǎn)換為數(shù)字SQL select to_number(1999) year from dual; YEAR 199951. BFILENAME(dir ,file)指定一個(gè)外部二進(jìn)制文件SQLinsert into file_tb1 values(bfilename(lob_dir1,image1.gif);52. CONVERT(x,desc,source)將 x 字段或變量的源 sourc

21、e 轉(zhuǎn)換為 descSQL select sid,serial#,username,decode(command,2 0,none,3 2,insert,4 3,5 select,6 6,update,7 7,delete,8 8,drop,9 other) cmd from v$session where type!=background;SID SERIAL# USERNAME CMD 1 1 none2 1 none3 1 none4 1 none5 1 none6 1 none7 1275 none8 1275 none9 20 GAO select10 40 GAO none53.

22、DUMP(s,fmt,start,length)DUMP 函數(shù)以 fmt 指定的內(nèi)部數(shù)字格式返回一個(gè) VARCHAR2 類型的值SQL col global_name for a30SQL col dump_string for a50SQL set lin 200SQL select global_name,dump(global_name,1017,8,5) dump_string from global_name; GLOBAL_NAME DUMP_STRING,O,R,L,DORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W54. EM

23、PTY_BLOB() 和 EMPTY_CLOB()這兩個(gè)函數(shù)都是用來對大數(shù)據(jù)類型字段進(jìn)行初始化操作的函數(shù)55. GREATEST 返回一組表達(dá)式中的最大值 ,即比較字符的編碼大小 SQL select greatest(AA,AB,AC) from dual;GRACSQL select greatest（啊, 安 ,天） from dual;GR56. LEAST 返回一組表達(dá)式中的最小值SQL select least（ 啊,安,天） from dual; LE57. UID 返回標(biāo)識當(dāng)前用戶的唯一整數(shù)SQL show userUSER 為 GAOSQL select username,u

24、ser_id from dba_users where user_id=uid;USERNAME USER_IDGAO 2558. USER 返回當(dāng)前用戶的名字SQL select user from dual;USERGAO59. USEREVN 返回當(dāng)前用戶環(huán)境的信息 ,opt 可以是 :ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZEISDBA 查看當(dāng)前用戶是否是 DBA 如果是則返回 trueSQL select userenv(isdba) from dual; USERENFALSESQL

25、select userenv(isdba) from dual;USERENTRUESESSION 返回會話標(biāo)志SQL select userenv(sessionid) from dual; USERENV(SESSIONID) 152ENTRYID 返回會話人口標(biāo)志SQL select userenv(entryid) from dual; USERENV(ENTRYID) 0INSTANCE返回當(dāng)前 INSTANCE 的標(biāo)志SQL select userenv(instance) from dual;USERENV(INSTANCE) 1LANGUAGE 返回當(dāng)前環(huán)境變量SQL sele

26、ct userenv(language) from dual;USERENV(LANGUAGE)SIMPLIFIED CHINESE_CHINA.ZHS16GBKLANG返回當(dāng)前環(huán)境的語言的縮寫SQL select userenv(lang) from dual;USERENV(LANG)ZHSTERMINAL 返回用戶的終端或機(jī)器的標(biāo)志SQL select userenv(terminal) from dual;USERENV(TERMINAGAOVSIZE(X)返回 X 的大小 (字節(jié) )數(shù)SQL select vsize(user),user from dual;VSIZE(USER)

27、USER6 SYSTEM? 60.A VG(DISTINCT|ALL)all 表示對所有的值求平均值 ,distinct 只對不同的值求平均值 SQLWKS create table table3(xm varchar(8),sal number(7,2); 語句已處理。SQLWKS insert into table3 values(gao,1111.11);SQLWKS insert into table3 values(gao,1111.11);SQLWKS insert into table3 values(zhu,5555.55);SQLWKS commit;SQL select a

28、vg(distinct sal) from gao.table3;AVG(DISTINCTSAL)3333.33SQL select avg(all sal) from gao.table3;AVG(ALLSAL)2592.5961.MAX(DISTINCT|ALL)求最大值 ,ALL 表示對所有的值求最大值 ,DISTINCT表示對不同的值求最大值,相同的只取一次SQL select max(distinct sal) from scott.emp;MAX(DISTINCTSAL)500062.MIN(DISTINCT|ALL)求最小值 ,ALL 表示對所有的值求最小值 ,DISTINCT表

29、示對不同的值求最小值,相同的只取一次SQL select min(all sal) from gao.table3;MIN(ALLSAL)1111.1163.STDDEV(distinct|all)求標(biāo)準(zhǔn)差 ,ALL 表示對所有的值求標(biāo)準(zhǔn)差 ,DISTINCT表示只對不同的值求標(biāo)準(zhǔn)差SQL select stddev(sal) from scott.emp;STDDEV(SAL)1182.5032SQL select stddev(distinct sal) from scott.emp;STDDEV(DISTINCTSAL) 1229.95164. VARIANCE(DISTINCT|ALL

30、) 求協(xié)方差SQL select variance(sal) from scott.emp;VARIANCE(SAL) 1398313.965. GROUP BY 主要用來對一組數(shù)進(jìn)行統(tǒng)計(jì)SQL select deptno,count(*),sum(sal) from scott.emp group by deptno; DEPTNO COUNT(*) SUM(SAL)10 3 875020 5 1087530 6 940066. HAVING 對分組統(tǒng)計(jì)再加限制條件SQL select deptno,count(*),sum(sal) from scott.emp group by dept

31、no having count(*)=5; DEPTNO COUNT(*) SUM(SAL) 20 5 1087530 6 9400SQL select deptno,count(*),sum(sal) from scott.emp having count(*)=5 group by deptno ; DEPTNO COUNT(*) SUM(SAL) 20 5 1087530 6 940067.ORDER BY 用于對查詢到的結(jié)果進(jìn)行排序輸出SQL select deptno,ename,sal from scott.emp order by deptno,sal desc; DEPTNO

32、ENAME SAL 10 KING 500010 CLARK 245010 MILLER 130020 SCOTT 300020 FORD 300020 JONES 297520 ADAMS 110020 SMITH 80030 BLAKE 285030 ALLEN 160030 TURNER 150030 WARD 125030 MARTIN 125030 JAMES 95068. pl/sql 中的 case 語句select (case when DUMMY=X then 0 else 1 end) as flag from dual; case 的第 1 種用法：case col wh

33、en a then 1when b then 2else 0 end這種用法跟 decode 一樣沒什么區(qū)別case 的第 2 種用法：case when score =60 and score =70 and score expr1 為 NULL ，返回 expr2 ；不為 NULL ，返回 expr1 。注意兩者的類型要一 致NVL2 (expr1, expr2, expr3) -expr1不為 NULL ，返回 expr2 ；為 NULL ，返回 expr3 。 expr2 和expr3 類型不同的話， expr3 會轉(zhuǎn)換為 expr2 的類型NULLIF (expr1, expr2)

34、-相等返回 NULL ，不等返回 expr1? Oracle 分析函數(shù)參考手冊 作者 : xsb(url)/url發(fā)表于 :2006.03.01 12:22分類 : DW&BI出處： /post/419/33028Oracle 從 8.1.6 開始提供分析函數(shù)，分析函數(shù)用于計(jì)算基于組的某種聚合值，它和聚合函數(shù)的不同之 處是對于每個(gè)組返回多行， 而聚合函數(shù)對于每個(gè)組只返回一行。常用的分析函數(shù)如下所列 : row_number() over(partition by . order by .) rank() over(pa

35、rtition by . order by .) dense_rank() over(partition by . order by .) count() over(partition by . order by .) max() over(partition by . order by .) min() over(partition by . order by .) sum() over(partition by . order by .)avg() over(partition by . order by .) first_value() over(partition by . order

36、 by .) last_value() over(partition by . order by .) lag() over(partition by . order by .) lead() over(partition by . order by .)下面例子中使用的表來自 Oracle 自帶的 HR 用戶下的表，如果沒有安裝該用戶，可以在 SYS 用戶下 運(yùn)行$ORACLE_HOME/demo/schema/human_resources/hr_main.sql 來創(chuàng)建。 除本文內(nèi)容外，你還可參考：ROLLUP 與 CUBE url/post/419

37、/29159/url 分析函數(shù)使用例子介紹： url/post/419/44634/url 本文如果未指明，缺省是在 HR 用戶下運(yùn)行例子。開窗函數(shù)的的理解：開窗函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小， 這個(gè)數(shù)據(jù)窗口大小可能會隨著行的變化而變化， 舉例如下： over ( order by salary ) 按照 salary 排序進(jìn)行累計(jì)， order by 是個(gè)默認(rèn)的開窗函數(shù) over ( partition by deptno)按照部門分區(qū)over ( order by salary range between 50 preceding and 150

38、 following)每行對應(yīng)的數(shù)據(jù)窗口是之前行幅度值不超過 50 ，之后行幅度值不超過 150 over ( order by salary rows between 50 preceding and 150 following)每行對應(yīng)的數(shù)據(jù)窗口是之前 50 行，之后 150 行over ( order by salary rows between unbounded preceding and unbounded following)每行對應(yīng)的數(shù)據(jù)窗口是從第一行到最后一行，等效：over ( order by salary range between unbounded precedin

39、g and unbounded following)主要參考資料： expert one-on-one Tom Kyte Oracle9i SQL Reference第 6 章ohwww 2007-3-12 09:1970 。 AVG 功能描述：用于計(jì)算一個(gè)組和數(shù)據(jù)窗口內(nèi)表達(dá)式的平均值。SAMPLE ：下面的例子中列 c_mavg 計(jì)算員工表中每個(gè)員工的平均薪水報(bào)告， 該平均值由當(dāng)前員工和與之 具有相同經(jīng)理的前一個(gè)和后一個(gè)三者的平均數(shù)得來；SELECT manager_id, last_name, hire_date, salary,AVG(salary) OVER (PARTITION BY

40、 manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg FROM employees;MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG 100 Kochhar 21-SEP-89 17000 17000100 De Haan 13-JAN-93 17000 15000100 Raphaely 07-DEC-94 11000 11966.6667100 Kaufling 01-MAY-95 7900 10633.3333100 Hartstein

41、17-FEB-96 13000 9633.33333100 Weiss 18-JUL-96 8000 11666.6667100 Russell 01-OCT-96 14000 11833.333371 。 CORR 功能描述：返回一對表達(dá)式的相關(guān)系數(shù)，它是如下的縮寫： COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2) 從統(tǒng)計(jì)上講，相關(guān)性是變量之間關(guān)聯(lián)的強(qiáng)度，變量之間的關(guān)聯(lián)意味著在某種程度 上一個(gè)變量的值可由其它的值進(jìn)行預(yù)測。通過返回一個(gè) -11 之間的一個(gè)數(shù) , 相關(guān) 系數(shù)給出了關(guān)聯(lián)的強(qiáng)度， 0 表示不相關(guān)。SAMPLE ：

42、下例返回 1998 年月銷售收入和月單位銷售的關(guān)系的累積系數(shù)(本例在 SH 用戶下運(yùn)行) SELECT t.calendar_month_number ,CORR (SUM(s.amount_sold), SUM(s.quantity_sold)OVER (ORDER BY t.calendar_month_number) as CUM_CORRFROM sales s, times tWHERE s.time_id = t.time_id AND calendar_year = 1998GROUP BY t.calendar_month_number ORDER BY t.calendar_

43、month_number;CALENDAR_MONTH_NUMBER CUM_CORR 12 13 .9943093824 .8520408755 .8466522046 .8712506287 .9100298038 .9175563999 .92015435610 .8672025111 .84486476512 .90354266272 。 COVAR_POP 功能描述：返回一對表達(dá)式的總體協(xié)方差。SAMPLE ：下例 CUM_COVP 返回定價(jià)和最小產(chǎn)品價(jià)格的累積總體協(xié)方差SELECT product_id, supplier_id,COVAR_POP(list_price, min_

44、price)OVER (ORDER BY product_id, supplier_id) AS CUM_COVP,COVAR_SAMP(list_price, min_price)OVER (ORDER BY product_id, supplier_id) AS CUM_COVSFROM product_information pWHERE category_id = 29ORDER BY product_id, supplier_id;PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS 1774 103088 01775 103087 1473.25 294

45、6.5 1794 103096 1702.77778 2554.166671825 103093 1926.25 2568.333332004 103086 1591.4 1989.252005 103086 1512.5 18152416 103088 1475.97959 1721.9761973 。 COVAR_SAMP 功能描述：返回一對表達(dá)式的樣本協(xié)方差SAMPLE ：下例 CUM_COVS 返回定價(jià)和最小產(chǎn)品價(jià)格的累積樣本協(xié)方差 SELECT product_id, supplier_id,COVAR_POP(list_price, min_price)OVER (ORDER BY

46、 product_id, supplier_id) AS CUM_COVP,COVAR_SAMP(list_price, min_price)OVER (ORDER BY product_id, supplier_id) AS CUM_COVSFROM product_information pWHERE category_id = 29ORDER BY product_id, supplier_id;PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS 1774 103088 01775 103087 1473.25 2946.51794 103096 1702.

47、77778 2554.166671825 103093 1926.25 2568.333332004 103086 1591.4 1989.252005 103086 1512.5 18152416 103088 1475.97959 1721.97619? 74 。 COUNT功能描述：對一組內(nèi)發(fā)生的事情進(jìn)行累積計(jì)數(shù)，如果指定 * 或一些非空常數(shù)， count 將對所有行計(jì)數(shù)，如 果指定一個(gè)表達(dá)式， count返回表達(dá)式非空賦值的計(jì)數(shù)，當(dāng)有相同值出現(xiàn)時(shí)，這些相等的值都會被納入被計(jì)算的值；可以使用DISTINCT 來記錄去掉一組中完全 相同的數(shù)據(jù)后出現(xiàn)的行數(shù)。SAMPLE ：下面例子中計(jì)算每個(gè)

48、員工在按薪水排序中當(dāng)前行附近薪水在 n-50,n+150 之間的行數(shù)， n 表 示當(dāng)前行的薪水例如， Philtanker 的薪水 2200 ，排在他之前的行中薪水大于等于 2200-50 的有 1 行，排在他之后的行 中薪水小于等于 2200 150 的行沒有，所以 count 計(jì)數(shù)值 cnt3 為 2 (包括自己當(dāng)前行) ；cnt2 值相當(dāng)于小于等于當(dāng)前行的 SALARY 值的 所有行數(shù)SELECT last_name, salary, COUNT(*) OVER () AS cnt1,COUNT(*) OVER (ORDER BY salary) AS cnt2,COUNT(*) OVE

49、R (ORDER BY salary RANGE BETWEEN 50 PRECEDINGAND 150 FOLLOWING) AS cnt3 FROM employees; LAST_NAME SALARY CNT1 CNT2 CNT3Olson 2100 107 1 3Markle 2200 107 3 2Philtanker 2200 107 3 2Landry 2400 107 5 8Gee 2400 107 5 8Colmenares 2500 107 11 10Patel 2500 107 11 1075 。 CUME_DIST功能描述：計(jì)算一行在組中的相對位置， CUME_DIS

50、T 總是返回大于 0 、小于或等于 1 的數(shù)，該數(shù)表示該 行在 N 行中的位置。例如，在一個(gè) 3 行的組中，返回的累計(jì)分布值為 1/3 、 2/3 、3/3SAMPLE ：下例中計(jì)算每個(gè)工種的員工按薪水排序依次累積出現(xiàn)的分布百分比SELECT job_id, last_name, salary, CUME_DIST()OVER (PARTITION BY job_id ORDER BY salary) AS cume_distFROM employees WHERE job_id LIKE PU%; JOB_ID LAST_NAME SALARY CUME_DISTPU_CLERK Colme

51、nares 2500 .2PU_CLERK Himuro 2600 .4PU_CLERK Tobias 2800 .6PU_CLERK Baida 2900 .8PU_CLERK Khoo 3100 1PU_MAN Raphaely 11000 176 。 DENSE_RANK功能描述：根據(jù) ORDER BY 子句中表達(dá)式的值，從查詢返回的每一行，計(jì)算它們與其它行的相對位置。 組內(nèi)的數(shù)據(jù)按 ORDER BY 子句排序，然后給每一行賦一個(gè)號，從而形成一個(gè)序列，該序列從 1 開始，往 后累加。每次 ORDER BY 表達(dá)式的值發(fā)生變化時(shí)，該序列也隨之增加。有同樣值的行得到同樣的數(shù)字序 號(認(rèn)為 n

52、ull 時(shí)相等的)。密集的序列返回的時(shí)沒有間隔的數(shù)SAMPLE ：下例中計(jì)算每個(gè)員工按部門分區(qū)再按薪水排序， 依次出現(xiàn)的序列號 (注意與 RANK 函數(shù)的區(qū)別)SELECT d.department_id , e.last_name, e.salary, DENSE_RANK()OVER (PARTITION BY e.department_id ORDER BY e.salary) as drankFROM employees e, departments dWHERE e.department_id = d.department_idAND d.department_id IN (60,

53、90);DEPARTMENT_ID LAST_NAME SALARY DRANK 60 Lorentz 4200 160 Austin 4800 260 Pataballa 4800 260 Ernst 6000 360 Hunold 9000 490 Kochhar 17000 190 De Haan 17000 190 King 24000 277 。 FIRST功能描述：從 DENSE_RANK 返回的集合中取出排在最前面的一個(gè)值的行(可能多行，因?yàn)橹悼赡芟嗟? 因此完整的語法需要在開始處加上一個(gè)集合函數(shù)以從中取出記錄SAMPLE ：下面例子中 DENSE_RANK 按部門分區(qū)，再按傭金

54、 commission_pct 排序， FIRST 取出傭金 最低的對應(yīng)的所有行， 然后前面的 MAX 函數(shù)從這個(gè)集合中取出薪水最低的值； LAST 取出傭金最高的對應(yīng) 的所有行，然后前面的 MIN 函數(shù)從這個(gè)集合中取出薪水最高的值SELECT last_name, department_id, salary,MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)OVER (PARTITION BY department_id) Worst,MAX(salary) KEEP (DENSE_RANK LAST ORDER BY co

55、mmission_pct)OVER (PARTITION BY department_id) BestFROM employeesWHERE department_id in (20,80)ORDER BY department_id, salary;LAST_NAME DEPARTMENT_ID SALARY Worst BestFay 20 6000 6000 13000 Hartstein 20 13000 6000 13000 Kumar 80 6100 6100 14000 Banda 80 6200 6100 14000 Johnson 80 6200 6100 14000 Ande 80 6400 6100 14000 Lee 80 6800 6100 14000 Tuvault 80 7000 6100 14000 Sewall 80 7000 6100