函数---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_Heute
FROM 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 , 2002

SP 数字的拼写 ---spelling out
TH 数字的序数词 
spth 使用序数词来拼写，如12--->twelve(sp方式) -->Twelfth (spth方式)

SELECT SYSDATE
,TO_CHAR(SYSDATE,'Mmspth') Month
,TO_CHAR(SYSDATE,'DDth') Day
,TO_CHAR(SYSDATE,'Yyyysp') Year
FROM 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') lowerCase
FROM dual;
UPPERCASE MIXEDCASE LOWERCASE
--------- --------- ---------
DECEMBER December december


Date Format Codes
Date Code     | Format Code Description     | Example
AD or BC        Epoch indicator              ‘YYYY AD’ = 2002 AD
A.D. or  B.C.   Epoch indicator with periods ‘YYYY A.D.’ = 2002 A.D.
AM or PM        Meridian indicator           ‘HH12AM’ = 09AM
A.M. or P.M.    Meridian indicator with periods ‘HH A.M.’= 09 A.M.
DY              Day of week abbreviated        Mon, Tue, Fri
DAY             Day of week spelled out        Monday, Tuesday, Friday
D               Day of week (1–7)             1,2,3,4,5,6,7
DD              Day of month (1–31)           1,2,3,4…31
DDD             Day of year (1–366)           1,2,3,4…366
FF              Fractional seconds             .34127
J               Julian day (days since 4712BC) 2451514,2451515,2451516
W               Week of the month (1–5)       1,2,3,4,5
WW, IW          Week of the year, ISO week of the year 1,2,3,4…53
MM              Two-digit month                01,02,03…12
MON             Month name abbreviated        Jan, Feb, Mar…Dec
MONTH           Month name spelled out        January, February…
Q               Quarter                       01-Jan-2002
RM              Roman numeral month (I–XII)  I,II,III,IV,V…XII
YYYY,YYY,       Four-digit year; last 3, 2, 1  1999, 999, 99, 9
 YY, Y          digits in the year             2000, 000, 00, 0
YEAR            Year spelled out              Two thousand two
SYYYY If BC,    year is shown as negative      -1250
RR              Used for data input with only  See description 
                two digits for the year        following table
HH, HH12        Hour of the half-day (1–12)   1,2,3…12
HH24            Hour of the day (0–23)        0,1,2…23
MI              Minutes of the hour (0–59)    0,1,2…59
SS              Seconds of the minute (0–59)  0,1,2…59
SSSSS           Seconds of the day (0–86399)  0,1,2…86399
TZD             Time zone daylight savings;    CST
                must correspond to TZR         
TZH             Time zone hour, together        07
                with TZM is time zone offset
TZM             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             display


SELECT TO_CHAR(123456,'9.99EEEE')
,TO_CHAR(123456,'9.9EEEE')
FROM dual;
TO_CHAR(12 TO_CHAR(1
---------- ---------
1.23E+05 1.2E+05


SELECT TO_CHAR(-1234.56,'C099G999D99MI','NLS_NUMERIC_CHARACTERS='',.''
NLS_CURRENCY=''DM''NLS_ISO_CURRENCY=''GERMANY''') Balance
FROM 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 this
symbol is not uniquely American, so the ISO symbol for U.S. dollars is USD.


Numeric
 Code|Format Code Description                  |Example
9     Numeric digits with leading space if      9999.9 = 1234.5
      positive and a leading – (minus) if      9999.9 = -1234.5
      negative.                                 9999.9 = .3
0     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 = $123
L     Local currency symbol.                    L999 = $123
                                               L999 = Euro123
FM    No leading or trailing blanks.            FM99.99 = .1
EEEE   Scientific notation.                     9.9EEEE = 1.2E+05
MI    Negative as a trailing minus.             999MI = 137-
PR    Negative in angle brackets (< >).         999PR = <137>
S     Negative as a leading minus.              S999 = -137
S:负数符号−放在开头,如:S999.9
RN    Uppercase Roman numeral.                  RN = XXIV
rn    Lowercase Roman numeral.                  rn = xxiv
X     Hexadecimal                               XX = FC


-------------------------------------------------------------

Oracle聚合函数RANK和dense_rank的使用
唯一区别就是如果排名重复rank则跳过，如 1，2，2，4
dense_rank则不跳过： 如1,2,2,3


TABLE：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操作。