日期:2014-05-17  浏览次数:20647 次

数据库公司一个面试题,求助。。
1.现有一张表
姓名 学科 成绩
张三 语文 80
张三 数学 60
张三 英语 70
李四 语文 40
李四 数学 80
李四 英语 60
王五 语文 64
王五 数学 79
王五 英语 69





要求:写一个存储过程
输出以下结果:
张三 语文(80),数学(60),英语(70)
李四 语文(40),数学(80),英语(60)
王五 语文(64),数学(79),英语(69)


------解决方案--------------------
SQL code

if OBJECT_ID('tb')<>'' drop table tb
create table tb (姓名 nvarchar(20), 学科 nvarchar(10) ,成绩 int)
insert into tb 
select '张三','语文',80
union all
select '张三','数学',60
union all
select '张三','英语',70
union all
select '李四','语文',40
union all
select '李四','数学',80
union all
select '李四','英语',60
union all
select '王五','语文',64
union all
select '王五','数学',79
union all
select '王五','英语',69
go
create procedure ptemp

as
begin
  set nocount on ;
    select 姓名, '语文('+cast(sum(case 学科 when '语文' then 成绩 else 0 end) as nvarchar(10))+')' as 语文,
                
                 '数学('+cast(sum(case 学科 when '数学' then 成绩 else 0 end) as nvarchar(10))+')' as 数学 ,
                 
                 '数学('+cast(sum(case 学科 when '英语' then 成绩 else 0 end) as nvarchar(10))+')'  as 英语
        from tb group by 姓名

end
go
exec ptemp
go
drop procedure ptemp
drop table tb 
go

--(9 行受影响)
--姓名                   语文               数学               英语
---------------------- ---------------- ---------------- ----------------
--李四                   语文(40)           数学(80)           数学(60)
--王五                   语文(64)           数学(79)           数学(69)
--张三                   语文(80)           数学(60)           数学(7

------解决方案--------------------
SQL code
create table tb (姓名 nvarchar(20), 学科 nvarchar(10) ,成绩 int)
insert into tb 
select N'张三',N'语文',80
union all
select N'张三',N'数学',60
union all
select N'张三',N'英语',70
union all
select N'李四',N'语文',40
union all
select N'李四',N'数学',80
union all
select N'李四',N'英语',60
union all
select N'王五',N'语文',64
union all
select N'王五',N'数学',79
union all
select N'王五',N'英语',69
go

select 姓名,信息=stuff((select ','+学科+'('+rtrim(成绩)+')' 
        from tb where 姓名=a.姓名 for xml path('')),1,1,'')
from tb a group by 姓名        

/*
姓名                   信息
-------------------- ----------------------------
张三                   语文(80),数学(60),英语(70)
李四                   语文(40),数学(80),英语(60)
王五                   语文(64),数学(79),英语(69)