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

2025-03-25 21:25:11

推荐回答（6个）

回答1：

使用子查询，如有额外科目可任意复制中间某行并更改科目名称
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

回答2：

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 姓名

回答3：

可以这样

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

回答4：

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

回答5：

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

回答6：

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

最新问答

圆柱体真空玻璃的总体积辐射系数为多少w⼀m3

要去塘厦沃尔玛，从大坪坐4路车到哪个站下车？

12*25的简便运算怎么写

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

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