下面是一个sql的面试题 求解 谢谢

使用子查询，如有额外科目可任意复制中间某行并更改科目名称
select distinct 姓名,
数学=(select 成绩 from tablename a where a.姓名=tablename.姓名 and a.科目=‘数学’),
语文=(select 成绩 from tablename a where a.姓名=tablename.姓名 and a.科目=‘语文’),
英语=(select 成绩 from tablename a where a.姓名=tablename.姓名 and a.科目=‘英语’)
from tablename

select 姓名,MAX(数学) as 数学, MAX(语文) as 语文,MAX(外语) as 外语
from (
select 姓名,成绩 as 数学,0 as 语文,0 as 外语 from 表 where 科目='数学'
union
select 姓名,0 as 数学,成绩 as 语文,0 as 外语 from 表 where 科目='语文'
union
select 姓名,0 as 数学,0 as 语文,成绩 as 外语 from 表 where 科目='外语'
) a group by 姓名

可以这样

select 姓名,
sum(case when 科目 = '数学' then 成绩 end) as 数学
sum(case when 科目 = '语文' then 成绩 end) as 语文
sum(case when 科目 = '外语' then 成绩 end) as 外语
from table
group by 姓名

create table test03
as
select '小明' as "姓名",'数学' as "科目", 50 AS "成绩" FROM DUAL
UNION ALL
select '小明' as "姓名",'语文' as "科目", 60 AS "成绩" FROM DUAL
UNION ALL
select '小明' as "姓名",'外语' as "科目", 70 AS "成绩" FROM DUAL
UNION ALL
select '小黑' as "姓名",'数学' as "科目", 80 AS "成绩" FROM DUAL
UNION ALL
select '小黑' as "姓名",'语文' as "科目", 90 AS "成绩" FROM DUAL
UNION ALL
select '小黑' as "姓名",'外语' as "科目", 100 AS "成绩" FROM DUAL
;
select *
from test03
;
--
select "姓名"
,max(decode(rvl,1,"成绩") )as "数学"
,max(decode(rvl,2,"成绩") )as "语文"
,max(decode(rvl,3,"成绩") )as "外语"
from
(
select "姓名","科目","成绩",rank() over(partition by "姓名" order by "成绩") as rvl
from lyh_test03
)
group by "姓名"
;
姓名 数学 语文 外语
小明 50 60 70
小黑 80 90 100

select 姓名,
sum(decode(科目,数学,成绩,0)) as 数学,
sum(decode(科目,语文,成绩,0)) as 语文,
sum(decode(科目,外语,成绩,0)) as 外语
from table
group by 姓名
提供一种oracle数据库下的方法。希望对你有帮助。

使用pivot。
行列转换专用：-）