SQL的函數中並沒有if、else函數他是使用case函數來做同樣的事情一個不合群的概念,若要使用if else則會是寫程式了。

所以以下這個不可行

select last_name,job_id,SALARY

if job_id = 'it_prog' then salary = salary*1.10

if job_id = 'st_clerk' then salary = salary*1.15

if job_id = 'sa_rep' then salary = salary*1.20

else salary = salary

from EMPLOYEES


關鍵字:case、when、then、else、end(欄位名)

要這樣寫:

select last_name,job_id,SALARY,

case job_id when 'it_prog' then salary * 1.10 --job_id='it_prog'時,回傳「salary * 1.10

                                when 'st_clerk' then salary*1.15

                                when 'sa_rep' then salary*1.20

                                else salary--這一行可以省略,則不符合條件者會變成NULL值,例如

Kochhar AD_VP 17000 Null

                                end '調整後薪水'--幫這整個case取名字

from EMPLOYEES

 

last_name job_id SALARY 調整後薪水
King AD_PRES 24000 24000
Kochhar AD_VP 17000 17000
De Haan AD_VP 17000 17000
Hunold IT_PROG 9000 9900
Ernst IT_PROG 6000 6600
Lorentz IT_PROG 4200 4620
Mourgos ST_MAN 5800 5800
Rajs ST_CLERK 3500 4025
Davies ST_CLERK 3100 3565
Matos ST_CLERK 2600 2990
Vargas ST_CLERK 2500 2875
Zlotkey SA_MAN 10500 10500
Abel SA_REP 11000 13200
Taylor SA_REP 8600 10320
Grant SA_REP 7000 8400
Whalen AD_ASST 4400 4400
Hartstein MK_MAN 13000 13000
Fay MK_REP 6000 6000
Higgins AC_MGR 12000 12000
Gietz AC_ACCOUNT 8300 8300

 



排序函數:

row_nimber() over (order by 欄位排序):一欄位排序給序號

rank() over (order by 欄位排序):同上,遇到同值時排名相同,後面跳過(1、2、2、4)

dense_rank() over (order by 欄位排序)同上,後面不跳過而是接續(1、2、2、3)

 

select ROW_NUMBER() over (order by salary desc)排序,last_name,SALARY

from EMPLOYEES

排序 last_name SALARY
1 King 24000
2 Kochhar 17000
3 De Haan 17000
4 Hartstein 13000
5 Higgins 12000
6 Abel 11000
7 Zlotkey 10500
8 Hunold 9000
9 Taylor 8600
10 Gietz 8300
11 Grant 7000
12 Fay 6000
13 Ernst 6000
14 Mourgos 5800
15 Whalen 4400
16 Lorentz 4200
17 Rajs 3500
18 Davies 3100
19 Matos 2600
20 Vargas 2500

 

select Rank() over (order by salary desc)排序,last_name,SALARY

from EMPLOYEES

排序 last_name SALARY
1 King 24000
2 Kochhar 17000
2 De Haan 17000
4 Hartstein 13000
5 Higgins 12000
6 Abel 11000
7 Zlotkey 10500
8 Hunold 9000
9 Taylor 8600
10 Gietz 8300
11 Grant 7000
12 Fay 6000
12 Ernst 6000
14 Mourgos 5800
15 Whalen 4400
16 Lorentz 4200
17 Rajs 3500
18 Davies 3100
19 Matos 2600
20 Vargas 2500

select dense_Rank() over (order by salary desc)排序,last_name,SALARY

from EMPLOYEES

排序 last_name SALARY
1 King 24000
2 Kochhar 17000
2 De Haan 17000
3 Hartstein 13000
4 Higgins 12000
5 Abel 11000
6 Zlotkey 10500
7 Hunold 9000
8 Taylor 8600
9 Gietz 8300
10 Grant 7000
11 Fay 6000
11 Ernst 6000
12 Mourgos 5800
13 Whalen 4400
14 Lorentz 4200
15 Rajs 3500
16 Davies 3100
17 Matos 2600
18 Vargas 2500

 

arrow
arrow
    全站熱搜

    乙方 發表在 痞客邦 留言(0) 人氣()