CASE WHEN與MAX的聯合使用

CASE WHEN...THEN與DECODE用了這長時間,一直以為case when只能單獨使用或與sum一起使用,在測試一個將縱行資料合併到模行時,去發現該行資料並非是數實值型別.

在網上搜尋了很多卻沒有發現與非數值型聯合使用的情況,但這並非難事,只要與MAX一起使用即可.

下面舉二種類型的例子:

例1: DECODE與MAX的聯合使用

SELECT DISTINCT EMP_NO,MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'01',CARD_FREQUENCY)) D01,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'02',CARD_FREQUENCY)) D02,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'03',CARD_FREQUENCY)) D03,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'04',CARD_FREQUENCY)) D04,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'05',CARD_FREQUENCY)) D05,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'06',CARD_FREQUENCY)) D06,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'07',CARD_FREQUENCY)) D07,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'08',CARD_FREQUENCY)) D08,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'09',CARD_FREQUENCY)) D09,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'10',CARD_FREQUENCY)) D10,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'11',CARD_FREQUENCY)) D11,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'12',CARD_FREQUENCY)) D12,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'13',CARD_FREQUENCY)) D13,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'14',CARD_FREQUENCY)) D14,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'15',CARD_FREQUENCY)) D15,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'16',CARD_FREQUENCY)) D16
FROM HRA_READ_CARD_MONTH
WHERE EMP_NO='090641' AND TO_CHAR(WORK_DATE,'YYYY/MM')='2009/03'
GROUP BY EMP_NO

 

例2:CASE WHEN與MAX的聯合使用

select emp_no,max(case work_date when to_date('2009-03-01','yyyy-mm-dd') then card_frequency end) AS D01,
max(case work_date when to_date('2009-03-02','yyyy-mm-dd') then card_frequency end) AS D02,
max(case work_date when to_date('2009-03-03','yyyy-mm-dd') then card_frequency end) AS D03,
max(case work_date when to_date('2009-03-04','yyyy-mm-dd') then card_frequency end) AS D04,
max(case work_date when to_date('2009-03-05','yyyy-mm-dd') then card_frequency end) AS D05,
max(case work_date when to_date('2009-03-06','yyyy-mm-dd') then card_frequency end) AS D06,
max(case work_date when to_date('2009-03-07','yyyy-mm-dd') then card_frequency end) AS D07,
max(case work_date when to_date('2009-03-08','yyyy-mm-dd') then card_frequency end) AS D08,
max(case work_date when to_date('2009-03-09','yyyy-mm-dd') then card_frequency end) AS D09,
max(case work_date when to_date('2009-03-10','yyyy-mm-dd') then card_frequency end) AS D10,
max(case work_date when to_date('2009-03-11','yyyy-mm-dd') then card_frequency end) AS D11,
max(case work_date when to_date('2009-03-12','yyyy-mm-dd') then card_frequency end) AS D12,
max(case work_date when to_date('2009-03-13','yyyy-mm-dd') then card_frequency end) AS D13,
max(case work_date when to_date('2009-03-14','yyyy-mm-dd') then card_frequency end) AS D14,
max(case work_date when to_date('2009-03-15','yyyy-mm-dd') then card_frequency end) AS D15,
max(case work_date when to_date('2009-03-16','yyyy-mm-dd') then card_frequency end) AS D16
 from HRA_READ_CARD_MONTH where emp_no='090461'
 group by emp_no;

其執行個體1和例2不同之處在於DECODE與CASE WHEN文法的運用,再者就是日期置放位置的不同.兩者如果去掉MAX就會出現多行顯示的情況,結果是一樣的,在此不再累述.