Sql Server 行转列学习 根据学生表、课程表、学生成绩表统计每个学生的各科成绩和他的总成绩、平均成绩

-------*************************************************************
-------学习、测试行转列     
------- Sourse 课程表;Student学生表;Student_Score学生课程成绩表
-------- 获得学生的成绩表(汇总包括总分和平均分)
-------*************************************************************
--**** (1)Create Table 'Course'****
if object_id ('Course','u') is not null
    drop table Course
create table Course
(
    Course_Id int identity(1,1) not null PRIMARY KEY,
    Course_Name varchar(20),
)
--add Course select * from Course
set IDENTITY_INSERT  Course on
insert Course(Course_Id,Course_Name)
    select 1,'Chinese' union all
    select 2,'Math' union all
    select 3,'Physical' union all
    select 4,'English' 
set identity_insert Course off

--**** (2)Create Table 'Student' ****
if object_id ('Student','u') is not null
    drop table Course
create table Student
(
    Student_Id int identity(1,1) not null primary key,
    Student_Name varchar(20)
)
--add Student
set identity_insert Student on
insert Student(Student_Id,Student_Name)
    select 1,'name1' union all
    select 2,'name2' union all
    select 3,'name3' union all
    select 4,'name4'
set identity_insert Student off

--**** (3) Create table 'Student_Score' ****
if object_id ('Student_Score','u') is not null
    drop table Student_Score
create table Student_Score
(
    Score_Id int identity(1,1) not null primary key,
    Student_Id int not null,
    Course_Id int not null,
    Score NUMERIC (8,2)
)
--add Course
insert Student_Score(Student_id,Course_id,Score)
    select 1,1,80 union all
    select 1,2,81 union all
    select 1,3,82 union all
    select 1,4,83 union all
    select 2,1,70 union all
    select 2,2,71 union all
    select 2,3,72 union all
    select 2,4,73 union all
    select 3,1,90 union all
    select 3,2,91 


--****** 1.method 1 Get student Scores ***************
declare @Sql varchar(8000)
set @Sql = 'Select a.student_id,a.student_name'
select @sql= @Sql+  ',sum(case c.course_name when   '''+course_name+''' then b.Score end )as  '+course_name
    from course
Set @sql = @Sql + ',sum(b.Score) as  TotalScore, avg(b.Score) as AvgScore '
Set @sql = @Sql + ' from Student as a left join student_score as b on a.student_id = b.student_id
                    left join course as c on b.course_id = c.courSe_id
                group by a.student_id,a.student_name'
--print @sql
exec(@Sql)
/**//*
1    name1    80.00    81.00    82.00    83.00    326.00    81.500000
2    name2    70.00    71.00    72.00    73.00    286.00    71.500000
3    name3    90.00    91.00    NULL    NULL    181.00    90.500000
4    name4    NULL    NULL    NULL    NULL    NULL    NULL
*/

--*** 2. Mothod 2 : user View 
--(1)Get student sourse score by View *********
if object_id('V_StudentScores','v') is not null
    drop view v_studentScores
go
create view  V_StudentScores
 as 

select a.student_id,a.student_name,b.course_id,c.course_Name,b.score
    from Student as a left join student_score as b on a.student_id = b.student_id
    left join course as c on b.course_id = c.courSe_id

go 
/**//*RESULT
1    name1    1    Chinese    80.00
1    name1    2    Math    81.00
1    name1    3    Physical    82.00
1    name1    4    English    83.00
2    name2    1    Chinese    70.00
2    name2    2    Math    71.00
2    name2    3    Physical    72.00
2    name2    4    English    73.00
3    name3    1    Chinese    90.00
3    name3    2    Math    91.00
4    name4    NULL    NULL    NULL
*/
-- (2) Get Data 
declare @Sql varchar(8000)
set @Sql = 'Select  Student_Id,Student_Name'
select @sql= @Sql+  ',sum(case course_name when   '''+course_name+''' then Score end )as  '+course_name
    from course
Set @sql = @Sql + ',sum(Score) as TotalScore, avg(Score) as AvgScore'
Set @sql = @Sql + ' from V_StudentScores  group by Student_Id,Student_Name'
--print @sql
exec(@Sql)

/**//*
select a.student_id,a.student_name,
sum(case c.course_name when   'Chinese' then b.score end )as  Chinese,
sum(case c.course_name when   'Math' then b.score end ) as  Math,
sum(case c.course_name when   'Physical' then b.score end  )as  Physical,
sum(case c.course_name when   'English' then b.score end ) as  English,
sum(b.Score) as TotalScore,avg(b.Score) as AvgScore
    from Student as a left join student_score as b on a.student_id = b.student_id
    left join course as c on b.course_id = c.courSe_id
group by a.student_id,a.student_name

Select  Student_Id,Student_Name,
sum(case course_name when   'Chinese' then Score end )as  Chinese,
sum(case course_name when   'Math' then Score end ) as  Math,
sum(case course_name when   'Physical' then Score end  )as  Physical,
sum(case course_name when   'English' then Score end ) as  English,
sum(Score) as TotalScore ,avg(score) as AvgScore
 from V_StudentScores  group by Student_Id,Student_Name
*/

 
思路：获得学生成绩View ，然后对View进行统计。各科的成绩也是通过SUM统计出来的，不过这各科的统计SQL要通过课程表来组合才可以的。

------------------------------------------------
--***********    adandelion 总结       *******************
------------------------------------------------
--最终结果 获得每个学生的各科成绩、总成绩、平均值
declare @Sql varchar(4000)
set @Sql = 'select student_id,student_name'  --学生姓名和编号，它来自学生和学生成绩表的组成的View--v。v的作用是把学生姓名和学生的成绩组合在一起，然后统计这个表即可。
select @Sql = @Sql +'
,sum(case  course_name   when  ''' +course_Name + ''' then  score  else  0  end ) as  ' + course_name
from course  --此上三行通过读课程表来来组合每个学生的课程成绩,这里是关键。最终的运行上看可以得知如果学生有这门的成绩，那就取出成绩,因为每个学生的某一课程的成绩只有一个，所以可以通过SUM获得。

select @Sql = @Sql +',sum(score) as Total,avg(score) as Average' --总成绩、和平均成绩
select @Sql = @Sql +' 
from
(
select a.student_id,a.student_name,c.course_id,c.course_name,b.score
from student as a left join 
student_score as b inner join course as c on b.course_id = c.course_id
 on  a.student_id =b.student_id
) as v
group by student_id,student_name
'
print @Sql
print len(@Sql)
exec(@Sql)
-----总结结束***********************************************************