求一SQL语句

Question

我有一张表结构如下：
成绩表
学号           课程号        成绩    学分
s006151101   class001     80     2
s006151101   class002     86     2
s006151101   class003     78     1
s006151102   class001     84     2
s006151102   class002     87     2
s006151102   class003     98     1课程表
课程号       课程名
class001    课程1
class002    课程2
class003    课程3
我通过查询获得如下结构的表
学号          课程1     课程2     课程3   总分  平均分
s006151101   80        86       78     244   81.33
s006151102   84        87       98     269   89.67请问SQL语句该如何写呢？

Answer 1

课程一月记录数固定的话,case when
不固定用动态语句,行转列.

Answer 2

SELECT 学号，'课程1'=SUM（CASE 课程号 when ' class001' then  成绩 else 0 end),'课程2'=SUM（CASE 课程号 when ' class002' then  成绩 else 0 end),
'课程3'=SUM（CASE 课程号 when ' class003' then  成绩 else 0 end),
'总分'=sum(成绩),'平均分'=avg(成绩) from 成绩表 
 group by 学号

Answer 3

create table 成绩表(学号 varchar(50), 课程号 varchar(50),成绩 int,学分 int)
insert into 成绩表 select 's006151101','class001',80,2 
insert into 成绩表 select 's006151101','class002',86,2
insert into 成绩表 select 's006151101','class003',78,1
insert into 成绩表 select 's006151102','class001',84,2
insert into 成绩表 select 's006151102','class002',87,2
insert into 成绩表 select 's006151102','class003',98,1create table 课程表( 课程号 varchar(50),课程名 varchar(50))
insert into 课程表 select 'class001','课程1'
insert into 课程表 select 'class002','课程2'
insert into 课程表 select 'class003','课程3'
借用临时表：
declare @sql varchar(8000)
select * into #temp from (select 学号,[课程名]=(select 课程名 from 课程表 where 课程表.课程号=成绩表.课程号),成绩,学分 from 成绩表)a
set @sql='select 学号'
select @sql=@sql+', ['+课程名+']=max(case 课程名 when '''+ 课程名 + ''' then 成绩 else 0 end)' from #temp group by 课程名
set @sql=@sql+' from #temp group by 学号'
set @sql='select 学号,课程1,课程2,课程3,sum(课程1+课程2+课程3)[总分],cast(sum(课程1+课程2+课程3)*1.0/3 as numeric(5,2)) [平均分] from ('+ @sql + ')a group by 学号,课程1,课程2,课程3'
exec(@sql)结果：
s006151101 80 86 78 244 81.33
s006151102 84 87 98 269 89.67

Answer 4

select a.学号,
  max(case b.课程名 when '课程1' then a.成绩 else 0 end) '课程1',
  max(case b.课程名 when '课程2' then a.成绩 else 0 end) '课程2',
  max(case b.课程名 when '课程3' then a.成绩 else 0 end) '课程3',
  sum(a.成绩) 总分,
  avg(a.成绩) 平均分
from 成绩表 a,课程表 b
where a.课程号 = b.课程号
group by a.学号
order by a.学号

Answer 5

declare @sql varchar(8000)
set @sql='select 学号'
select @sql=@sql+', max(case 课程名 when '''+ 课程名 + ''' then 成绩 else 0 end)['+课程名+']' from #temp group by 课程名
set @sql=@sql+' from #temp group by 学号'
set @sql='select 学号,课程1,课程2,课程3,课程1+课程2+课程3 as [总分],cast((课程1+课程2+课程3)*1.0/3 as numeric(5,2)) [平均分] from ('+ @sql + ')a group by 学号,课程1,课程2,课程3'
exec(@sql)

Answer 6

create table student(学号 varchar(50), 课程号 varchar(50),成绩 int,学分 int)
insert into student select 's006151101','class001',80,2 
insert into student select 's006151101','class002',86,2
insert into student select 's006151101','class003',78,1
insert into student select 's006151102','class001',84,2
insert into student select 's006151102','class002',87,2
insert into student select 's006151102','class003',98,1create table course( 课程号 varchar(50),课程名 varchar(50))
insert into course select 'class001','课程1'
insert into course select 'class002','课程2'
insert into course select 'class003','课程3'--静态的
select 学号, max(case when 课程名='课程1' then 成绩 else 0 end ) as 课程1, 
max(case when 课程名='课程2' then 成绩 else 0 end ) as 课程2, 
max(case when 课程名='课程3' then 成绩 else 0 end ) as 课程3 , 
sum(成绩) as 总分 ,avg(成绩) as 平均分  
from  student a,course b 
where a.课程号=b.课程号 group by 学号--动态的
declare @sql varchar(4000)
set @sql='select 学号'
select @sql=@sql+', max(case when 课程名='''+课程名+''' then 成绩 else 0 end ) as '+课程名
from (select distinct 课程名 from course) a
set @sql=@sql+' , sum(成绩) as 总分 ,avg(成绩) as 平均分  from  student a,course b where a.课程号=b.课程号 group by 学号'
print @sql
exec(@sql) /*
学号                                                 课程1         课程2         课程3         总分          平均分
-------------------------------------------------- ----------- ----------- ----------- ----------- -----------
s006151101                                         80          86          78          244         81
s006151102                                         84          87          98          269         89(2 行受影响)
*/

Answer 7

楼上的动态更厉害。不过有个地方要改下

declare @sql varchar(4000)
set @sql='select 学号'
select @sql=@sql+', max(case when 课程名='''+课程名+''' then 成绩 else 0 end ) as '+课程名
from (select distinct 课程名 from 课程表) a
set @sql=@sql+' , sum(成绩) as 总分 ,cast(sum(成绩)*1.0/(select count(1) from 课程表) as numeric(5,2)) as 平均分  from  成绩表 a,课程表 b where a.课程号=b.课程号 group by 学号'
print @sql
exec(@sql) 

Answer 8

贴上来后，文本总是容易变形。重新贴一次
declare @sql varchar(4000) 
set @sql= 'select 学号 ' 
select @sql=@sql+ ', max(case when 课程名= '''+课程名+ ''' then 成绩 else 0 end ) as  '+课程名 
from (select distinct 课程名 from 课程表) a 
set @sql=@sql+ ' , sum(成绩) as 总分 ,cast(sum(成绩)*1.0/(select count(1) from 课程表) as numeric(5,2)) as 平均分  from  成绩表 a,课程表 b where a.课程号=b.课程号 group by 学号 ' 
print @sql 
exec(@sql)