今天的一道面試題，看著簡單但是不會。。。

laoliuya

求大神教我

li0924

1. 1. 非常規(guī)
   
2. with tb (name,subject,result) as (
   
3. select 'A','語文',74 from dual
   
4. union all
   
5. select 'A','數(shù)學(xué)',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','數(shù)學(xué)',84 from dual
    
12. union all
    
13. select 'B','物理',94 from dual
    
14. ) select * from tb pivot(max(result) for subject in ('語文','數(shù)學(xué)','物理'));
    
15. 2.常規(guī)
    
16. with tb (name,subject,result) as (
    
17. select 'A','語文',74 from dual
    
18. union all
    
19. select 'A','數(shù)學(xué)',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','數(shù)學(xué)',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 '數(shù)學(xué)' then result else 0 end) 數(shù)學(xué),
    
31. max(case subject when '物理' then result else 0 end) 物理
    
32. from tb
    
33. group by name;

復(fù)制代碼

2009532140

回復(fù) 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 = '數(shù)學(xué)' ),
   
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 ;

復(fù)制代碼

看看這個行不？

HUB11456BOY

新手，剛剛學(xué)的：

1. SELECT NAME
   
2. ,      SUM(DECODE(SUBJECT,'語文',RESULT)) 語文
   
3. ,      SUM(DECODE(SUBJECT,'數(shù)學(xué)',RESULT)) 數(shù)學(xué)
   
4. ，     SUM(DECODE(SUBJECT,'物理',RESULT)) 物理
   
5. FROM TB
   
6. GROUP BY NAME;

復(fù)制代碼