今天的一道面试题，看着简单但是不会。。。

laoliuya

求大神教我

li0924

1. 1. 非常规
   
2. with tb (name,subject,result) as (
   
3. select 'A','语文',74 from dual
   
4. union all
   
5. select 'A','数学',83 from dual
   
6. union all
   
7. select 'A','物理',93 from dual
   
8. union all
   
9. select 'B','语文',74 from dual
   
10. union all
    
11. select 'B','数学',84 from dual
    
12. union all
    
13. select 'B','物理',94 from dual
    
14. ) select * from tb pivot(max(result) for subject in ('语文','数学','物理'));
    
15. 2.常规
    
16. with tb (name,subject,result) as (
    
17. select 'A','语文',74 from dual
    
18. union all
    
19. select 'A','数学',83 from dual
    
20. union all
    
21. select 'A','物理',93 from dual
    
22. union all
    
23. select 'B','语文',74 from dual
    
24. union all
    
25. select 'B','数学',84 from dual
    
26. union all
    
27. select 'B','物理',94 from dual
    
28. ) select name,
    
29. max(case subject when '语文' then result else 0 end) 语文,
    
30. max(case subject when '数学' then result else 0 end) 数学,
    
31. max(case subject when '物理' then result else 0 end) 物理
    
32. from tb
    
33. group by name;

复制代码

2009532140

回复 2# li0924


    怎么跑这里拽SQL 了

fenyun689

1. with a as (select name ,result from tb where subject = '语文' ),
   
2. b as (select name ,result from tb where subject = '数学' ),
   
3. c as (select name ,result from tb where subject = '物理' )
   
4. select a.name,a.result,b.result,c.result
   
5. from a, b, c where a.name = b.name and b.name =c.name ;

复制代码

看看这个行不？

HUB11456BOY

新手，刚刚学的：

1. SELECT NAME
   
2. ,      SUM(DECODE(SUBJECT,'语文',RESULT)) 语文
   
3. ,      SUM(DECODE(SUBJECT,'数学',RESULT)) 数学
   
4. ，     SUM(DECODE(SUBJECT,'物理',RESULT)) 物理
   
5. FROM TB
   
6. GROUP BY NAME;

复制代码