面试题(SQL)
有一数据库存储学生表S(学号/姓名)、课程表C(课程编号/课程名称)、成绩表SC(学号/课程编号/学期/分数),部分数据分别如下所示:
===================================学生表S
S# SName
1 张三
2 李四
===================================课程表C
C# CName
1 语文
2 数学
===================================成绩表SC
S# C# Semester Grade
11180
12160
21189
22172
11270
12260
21286
22262
第一题:
要得到以下格式的表,问SQL怎写?
姓名 第一学期总分 第二学期总分
张三 146 130
......
第二题:
要得到以下格式的表,问SQL怎写?
姓名 语文平均分 数学平均分
张三 75 60
......
[解决办法]
create table S(S# INT,SName VARCHAR(50))
create table C(C# INT,CName VARCHAR(50))
create table SC(S# INT,C# INT ,Semester INT,Grade INT)
GO
INSERT S SELECT 1 , '张三 ' UNION ALL
SELECT 2 , '李四 '
INSERT C SELECT 1 , '语文 ' UNION ALL
SELECT 2 , '数学 '
INSERT SC SELECT 1,1,1,80 UNION ALL
SELECT 1,2,1,60 UNION ALL
SELECT 2,1,1,89 UNION ALL
SELECT 2,2,1,72 UNION ALL
SELECT 1,1,2,70 UNION ALL
SELECT 1,2,2,60 UNION ALL
SELECT 2,1,2,86 UNION ALL
SELECT 2,2,2,62
GO
SELECT * FROM SC
GO
DROP TABLE S,C,SC
GO
SELECT S.SName ,SUM(CASE WHEN Semester=1 THEN Grade ELSE 0 END) AS '第一学期 ',
SUM(CASE WHEN Semester=2 THEN Grade ELSE 0 END) AS '第二学期 ' FROM SC,S WHERE SC.S#=S.S#
GROUP BY S.SName,SC.S#
GO
GO
这是第一问.第二问也差不多。`.`
[解决办法]
create table 学生表(s# int,sname varchar(20))
insert into 学生表
select 1, '张三 '
union all select 2, '李四 '
create table 课程表(c# int,cname varchar(20))
insert into 课程表
select 1, '语文 '
union all select 2, '数学 '
create table 成绩表 (s# int,c# int,semester int,grade int)
insert into 成绩表
select 1,1,1,80
union all select 1,2,1,60
union all select 2,1,1,89
union all select 2,2,1,72
union all select 1,1,2,70
union all select 1,2,2,60
union all select 2,1,2,86
union all select 2,2,2,62
--1.
select 学生表.sname,
sum(case semester when 1 then grade else 0 end) as '第一学期总分 ',
sum(case semester when 2 then grade else 0 end) as '第二学期总分 '
from 成绩表
inner join 学生表 on 学生表.s#=成绩表.s#
group by 学生表.sname
--2.
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',avg(case when c#= '+rtrim(c#)+ ' then grade end) as ' ' '+cname+ '平均分 ' ' '
from 课程表
select @sql= 'select 学生表.sname '+@sql+
' from 成绩表 '+
' inner join 学生表 on 学生表.s#=成绩表.s# '+
' group by 学生表.sname '
exec(@sql)
