decode和case 的面试题

SQL> desc t Name Type Nullable Default Comments ---- ------------ -------- ------- -------- NAME VARCHAR2(10) Y TIME DATE Y VAL VARCHAR2(5) Y TEST@ORA11> select * from t;NAME TIME VAL---------- -------------- -----tom 03-4月 -08 Atom 02-4月 -08 Atom 01-4月 -08 Btom 31-3月 -08 Cjone 31-3月 -08 Ajone 01-4月 -08 Bjone 02-4月 -08 Bjone 03-4月 -08 C已选择8行。

把上面的转换为下面的

 

NAME A B C---------- ---------- ---------- ----------tom 2 1 1jone 1 2 1

 

 

1 第一种方法：

通过观察可以发现转化后的的记录就是对name属性所具有不同val值的个数的统计

 

SQL> select name,val,count(*) 2 from t1 3 group by name,val 4 order by name desc NAME VAL COUNT(*)-------------------- ----- ----------tom A 2tom B 1tom C 1jone A 1jone B 2jone C 1

 

decode的作用就是拿着val属性的所有值域然后去和'A'比较然后如果匹配那么把count(*)与'A'互换

分别对BC也做decode，这样实际就是创造出了新表的值域，至于属性名直接赋予别名就可以了

1 select name, decode(val,'A',cn) A, decode(val,'B',cn) B, 2 decode(val,'C',cn) C 3 from ( select name,count(*) cn,val 4 from t1 5 group by name,val 6 ) 7* order by name descNAME A B C-------------------- ---------- ---------- ----------tom 2tom 1tom 1jone 1jone 2jone 1

 

 

通过把tom分组 然后分别把A,B,C用聚合函数就可以实现了

 

  1 select name, max(decode(val,'A',cn)) A, max(decode(val,'B',cn)) B, 2 max(decode(val,'C',cn)) C 3 from ( select name,count(*) cn,val 4 from t1 5 group by name,val 6 ) 7 group by name 8* order by name descSQL> /NAME A B C-------------------- ---------- ---------- ----------tom 2 1 1jone 1 2 12 rows selected.

 

第二种方法：case

1 select name, 2 max(case when val = 'A' then cn end) as A, 3 max(case when val = 'B' then cn end) as B, 4 max(case when val = 'C' then cn end) as C 5 from (select name,val,count(val) cn 6 from t1 7 group by name,val 8 ) 9* group by nameSQL> /NAME A B C-------------------- ---------- ---------- ----------tom 2 1 1jone 1 2 12 rows selected.