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就会出现多行显示的情况,结果是一样的,在此不再累述.