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

laoliuya 发表于 2016-03-22 21:40

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

求大神教我

li0924 发表于 2016-03-23 15:54

1. 非常规
with tb (name,subject,result) as (
select 'A','语文',74 from dual
union all
select 'A','数学',83 from dual
union all
select 'A','物理',93 from dual
union all
select 'B','语文',74 from dual
union all
select 'B','数学',84 from dual
union all
select 'B','物理',94 from dual
) select * from tb pivot(max(result) for subject in ('语文','数学','物理'));
2.常规
with tb (name,subject,result) as (
select 'A','语文',74 from dual
union all
select 'A','数学',83 from dual
union all
select 'A','物理',93 from dual
union all
select 'B','语文',74 from dual
union all
select 'B','数学',84 from dual
union all
select 'B','物理',94 from dual
) select name,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name;

2009532140 发表于 2016-04-07 17:07

回复 2# li0924


    怎么跑这里拽SQL 了

fenyun689 发表于 2016-04-12 16:15

with a as (select name ,result from tb where subject = '语文' ),
b as (select name ,result from tb where subject = '数学' ),
c as (select name ,result from tb where subject = '物理' )
select a.name,a.result,b.result,c.result
from a, b, c where a.name = b.name and b.name =c.name ;看看这个行不？

HUB11456BOY 发表于 2016-04-15 18:36

新手，刚刚学的：SELECT NAME
,      SUM(DECODE(SUBJECT,'语文',RESULT)) 语文
,      SUM(DECODE(SUBJECT,'数学',RESULT)) 数学
，   SUM(DECODE(SUBJECT,'物理',RESULT)) 物理
FROM TB
GROUP BY NAME;

查看完整版本: 今天的一道面试题，看着简单但是不会。。。