函数---to_char,RANK()和dense_rank(),rollup,cube
TO_CHAR(<x> [,<fmt >[,<nlsparm>] ])SELECT TO_CHAR(SYSDATE,'Day Ddspth,Month YYYY','NLS_DATE_LANGUAGE=German') Today_HeuteFROM dual;SELECT TO_CHAR(SYSDATE,'"On the "Ddspth" day of "Month, YYYY')FROM dual;这里的Dd和DD意思一样，不同点是单词首字母大写，其他小写TO_CHAR(SYSDATE,'"ONTHE"DDSPTH"DAYOF"MONTH,Y--------------------------------------------On the Twenty-Seventh day of November , 2002SP 数字的拼写 ---spelling outTH 数字的序数词 spth 使用序数词来拼写，如12--->twelve(sp方式) -->Twelfth (spth方式)SELECT SYSDATE,TO_CHAR(SYSDATE,'Mmspth') Month,TO_CHAR(SYSDATE,'DDth') Day,TO_CHAR(SYSDATE,'Yyyysp') YearFROM dual;SYSDATE MONTH DAY YEAR----------- -------- ---- -------------------------------------01-DEC-1999 Twelfth 01ST One Thousand Nine Hundred Ninety-Nine其中01ST是数字的序数词，Twelfth 是数字被拼写后的序数词Yyyy就是YYYY，但是每个单词首字母大写，其他小写SELECT TO_CHAR(SYSDATE,'MONTH') upperCase,TO_CHAR(SYSDATE,'Month') mixedCase,TO_CHAR(SYSDATE,'month') lowerCaseFROM dual;UPPERCASE MIXEDCASE LOWERCASE--------- --------- ---------DECEMBER December decemberDate Format CodesDate Code | Format Code Description | ExampleAD or BC Epoch indicator ‘YYYY AD’ = 2002 ADA.D. or B.C. Epoch indicator with periods ‘YYYY A.D.’ = 2002 A.D.AM or PM Meridian indicator ‘HH12AM’ = 09AMA.M. or P.M. Meridian indicator with periods ‘HH A.M.’= 09 A.M.DY Day of week abbreviated Mon, Tue, FriDAY Day of week spelled out Monday, Tuesday, FridayD Day of week (1–7) 1,2,3,4,5,6,7DD Day of month (1–31) 1,2,3,4…31DDD Day of year (1–366) 1,2,3,4…366FF Fractional seconds .34127J Julian day (days since 4712BC) 2451514,2451515,2451516W Week of the month (1–5) 1,2,3,4,5WW, IW Week of the year, ISO week of the year 1,2,3,4…53MM Two-digit month 01,02,03…12MON Month name abbreviated Jan, Feb, Mar…DecMONTH Month name spelled out January, February…Q Quarter 01-Jan-2002RM Roman numeral month (I–XII) I,II,III,IV,V…XIIYYYY,YYY, Four-digit year; last 3, 2, 1 1999, 999, 99, 9 YY, Y digits in the year 2000, 000, 00, 0YEAR Year spelled out Two thousand twoSYYYY If BC, year is shown as negative -1250RR Used for data input with only See description two digits for the year following tableHH, HH12 Hour of the half-day (1–12) 1,2,3…12HH24 Hour of the day (0–23) 0,1,2…23MI Minutes of the hour (0–59) 0,1,2…59SS Seconds of the minute (0–59) 0,1,2…59SSSSS Seconds of the day (0–86399) 0,1,2…86399TZD Time zone daylight savings; CST must correspond to TZR TZH Time zone hour, together 07 with TZM is time zone offsetTZM Time zone minute, together 00 with TZH is time zone offset TZR Time zone region US/Central, Mexico/BajaNorte, . / - ; : Punctuation Literal display‘text’ Quoted text Literal displaySELECT TO_CHAR(123456,'9.99EEEE'),TO_CHAR(123456,'9.9EEEE')FROM dual;TO_CHAR(12 TO_CHAR(1---------- ---------1.23E+05 1.2E+05SELECT TO_CHAR(-1234.56,'C099G999D99MI','NLS_NUMERIC_CHARACTERS='',.''NLS_CURRENCY=''DM''NLS_ISO_CURRENCY=''GERMANY''') BalanceFROM dual;BALANCE--------------DEM001.234,56-其中C 代表使用ISO international currency symbol (format symbol C).For example, the NLS_CURRENCY symbol for U.S. dollars is $, but thissymbol is not uniquely American, so the ISO symbol for U.S. dollars is USD.Numeric Code|Format Code Description |Example9 Numeric digits with leading space if 9999.9 = 1234.5 positive and a leading – (minus) if 9999.9 = -1234.5 negative. 9999.9 = .30 Leading and/or trailing zeros. 0009.90 = 0012.30代表一位数字，在相应的位置上如果没有数字则出现0, Comma, for use as a group separator. 9,999.9 = 1,234.5 It cannot appear after a period or decimal code.G Local group separator, could be comma 9G999D9 = 1,234.5 (,) or period (.). 9G999D9 = 1.234,5 分组分隔符（使用本地化）. Period, for use as the decimal character. 9,999.9 = 1,234.5 It cannot appear more than once or to the left of a group separator.D Local decimal character, could be 9G999D9 = 1,234.5 comma (,) or period (.). 9G999D9 = 1.234,5 小数点（使用本地化）$ Dollar-sign currency symbol. $999 = $123L Local currency symbol. L999 = $123 L999 = Euro123FM No leading or trailing blanks. FM99.99 = .1EEEE Scientific notation. 9.9EEEE = 1.2E+05MI Negative as a trailing minus. 999MI = 137-PR Negative in angle brackets (< >). 999PR = <137>S Negative as a leading minus. S999 = -137S:负数符号?放在开头,如:S999.9RN Uppercase Roman numeral. RN = XXIVrn Lowercase Roman numeral. rn = xxivX Hexadecimal XX = FC-------------------------Oracle聚合函数RANK和dense_rank的使用唯一区别就是如果排名重复rank则跳过，如 1，2，2，4dense_rank则不跳过： 如1,2,2,3TABLE：A （科目，分数） 数学，80 语文，70 数学，90 数学，60 数学，100 语文，88 语文，65 语文，77 现在我想要的结果是：（即想要每门科目的前3名的分数）数学，100 数学，90 数学，80 语文，88 语文，77 语文，70 那么语句就这么写： select * from (select rank() over(partition by 科目 order by 分数 desc) rk,a.* from a) t where t.rk<=3;例子3： 合计功能：计算出数值(4,1)在Orade By Col1,Col2排序下的排序值，也就是col1=4,col2=1在排序以后的位置 SELECT RANK(4,3) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table; 结果如下： Rank 4 dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系是，相关等级不会跳过。rank则跳过 例如：表A B C a liu wang a jin shu a cai kai b yang du b lin ying b yao cai b yang 99 例如：当rank时为： select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m A B C LIU a cai kai 1 a jin shu 2 a liu wang 3 b lin ying 1 b yang du 2 b yang 99 2 b yao cai 4 而如果用dense_rank时为： select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m A B C LIU a cai kai 1 a jin shu 2 a liu wang 3 b lin ying 1 b yang du 2 b yang 99 2 b yao cai 3 ------------------------------rollup----如果是ROLLUP(A, B, C)的话,则先group by (a,b,c)--->group by(a,b) -->group by (a)-->group by (全表)cube---->如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。
