三张表的查询，在线等

Question

现有三张表，如下：
create table Student
(
 stuID int primary key,
 stuName varchar(20)
)
create table Course
(
 couID int primary key,
 couName varchar(20)
)
create table Store
(
 stuID int not null,
 couID int not null,
 store int
)insert into Student values (001,'张三')
insert into Student values (002,'李四')
insert into Student values (003,'王五')
insert into Student values (004,'刘二')insert into Course values (1001,'英语')
insert into Course values (1002,'数学')
insert into Course values (1003,'物理')
insert into Course values (1004,'语文')
insert into Course values (1005,'地理')insert into Store values (004,1001,90)
insert into Store values (004,1002,91)
insert into Store values (004,1003,93)
insert into Store values (004,1004,94)
insert into Store values (004,1005,95)要求查询后形成的报表如下：
学生姓名  英语 数学 物理 语文 地理
刘二       90   91   93   94   95
王五      89    89   89   89   89

Answer 1

/*
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分 
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb    ------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)： 
姓名 课程 分数 
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns 
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程   分数
---- ------ ------
李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33
张三 总分   250.00
------------------
*/select * from
(
 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
 union all
 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
 union all
 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb

Answer 2

create table Student 
( 
stuID int primary key, 
stuName varchar(20) 
) 
create table Course 
( 
couID int primary key, 
couName varchar(20) 
) 
create table Store 
( 
stuID int not null, 
couID int not null, 
store int 
) insert into Student values (001,'张三') 
insert into Student values (002,'李四') 
insert into Student values (003,'王五') 
insert into Student values (004,'刘二') insert into Course values (1001,'英语') 
insert into Course values (1002,'数学') 
insert into Course values (1003,'物理') 
insert into Course values (1004,'语文') 
insert into Course values (1005,'地理') insert into Store values (004,1001,90) 
insert into Store values (004,1002,91) 
insert into Store values (004,1003,93) 
insert into Store values (004,1004,94) 
insert into Store values (004,1005,95) 
--2005的方法
select * from 
(select stuName,couName,store
from Student st join Store s on st.stuID=s.stuID join Course  c on s.couID=c.couID) t
pivot (
  max(store) for  couname in (英语 ,数学, 物理, 语文, 地理 )
)p
--2000的方法
select stuName,
英语=MAX(case when  couname='英语' then Store else 0 end),
数学=MAX(case when  couname='数学' then Store else 0 end),
物理=MAX(case when  couname='物理' then Store else 0 end),
语文=MAX(case when  couname='语文' then Store else 0 end),
地理=MAX(case when  couname='地理' then Store else 0 end)
from 
(select stuName,couName,store
from Student st join Store s on st.stuID=s.stuID join Course  c on s.couID=c.couID) t
group by  stuName
/*
stuName              英语          数学          物理          语文          地理
-------------------- ----------- ----------- ----------- ----------- -----------
刘二                   90          91          93          94          95*/

Answer 3

----drop table student
create table Student 
( 
stuID int primary key, 
stuName varchar(20) 
) 
create table Course 
( 
couID int primary key, 
couName varchar(20) 
) 
create table Store 
( 
stuID int not null, 
couID int not null, 
store int 
) insert into Student values (001,'张三') 
insert into Student values (002,'李四') 
insert into Student values (003,'王五') 
insert into Student values (004,'刘二') insert into Course values (1001,'英语') 
insert into Course values (1002,'数学') 
insert into Course values (1003,'物理') 
insert into Course values (1004,'语文') 
insert into Course values (1005,'地理') insert into Store values (004,1001,90) 
insert into Store values (004,1002,91) 
insert into Store values (004,1003,93) 
insert into Store values (004,1004,94) 
insert into Store values (004,1005,95) select distinct stuname,
       英语=max(case when couname='英语' then store else 0 end),
       数学=max(case when couname='数学' then store else 0 end),
       物理=max(case when couname='物理' then store else 0 end),
       语文=max(case when couname='语文' then store else 0 end),
       地理=max(case when couname='地理' then store else 0 end)
       from (
select a.stuID ,c.couname,a.stuname,b.store from   store b  
left join  Student a on a.stuID =b.stuid 
left join course c on b.couid=c.couid )c
group by stuName