最后求一个简单一点的统计表了

Question

对如下三个表做查询
sc (stuid int,subjectid int,score float)
stu (stuid int,stuname varchar(10),classid int)
subject (subjectid int,subjectname varchar(10))现在只需对某个学生的情况进行统计了(即根据存储过程的参数得到一个学生的学号来查此学生的一些情况)即得到一个人成绩明细表
需要查询出的报表样式为:(这次的查询只对科目是动态的了)
统计项          语文   数学   英语   总分
个人原始分      98     89      86    273
班级平均分      94     90      90    274
年级平均分      90     90      90    270
班级名次        16     15      16    14
年级名次        89     85      70    79
班级最高分      100    100     100   298
年级最高分      100    100     100   299
班级最低分      60     60      60    200
年级最低分      60     60      58    197
谢谢阿

Answer 1

create table sc (stuid int,subjectid int,score float)
insert sc select 1,1,98
insert sc select 1,2,89
insert sc select 1,3,86
create table stu (stuid int,stuname varchar(10),classid int)create table subject (subjectid int,subjectname varchar(10))
insert subject select 1,N'语文'
insert subject select 2,N'数学'
insert subject select 3,N'英语'declare @scoreChinese int,
@scoreMath int,
@scoreEnglish int,
@classid int
select @scoreChinese=score
from sc where subjectid=1 and stuid=學生的id假設為1
select @scoreMath=score
from sc where subjectid=1 and stuid=學生的id假設為1
select @scoreEnglish=score
from sc where subjectid=1 and stuid=學生的id假設為1
select @classid=classid
from stu where stuid=學生的id假設為1--班级平均分
select  N'个人原始分' [统计项],
@scoreChinese [语文],@scoreMath [数学],@scoreEnglish [英语]
from sc
--班级平均分
union all select N'班级平均分',
avg(case when subjectid=1 then score else 0 end),
avg(case when subjectid=2 then score else 0 end),
avg(case when subjectid=3 then score else 0 end)
from
(
select stu.subjectid,stu.score from stu  
join sc on stu.stuid=sc.stuid
where stu.classid=@classid
)
--年级平均分
union all select --班级名次
select N'班级名次',a.chinese,b.Math,c.English
from 
(
select count(1) [chinese] from stu  
join sc on stu.stuid=sc.stuid
where stu.classid=@classid and sc.subjectid=1 and sc.score>=@scoreChinese
) a,
(
select count(1) [Math] from stu  
join sc on stu.stuid=sc.stuid
where stu.classid=@classid and sc.subjectid=2 and sc.score>=@scoreMath
) b,
from
(
select count(1) [English] from stu  
join sc on stu.stuid=sc.stuid
where stu.classid=@classid and sc.subjectid=3 and sc.score>=@scoreEnglish
) c--年级名次--班级最高分
select N'班级最高分',a.chinese,b.Math,c.English
from 
(
select max(sc.score) [chinese] from stu  
join sc on stu.stuid=sc.stuid
where stu.classid=@classid and sc.subjectid=1
) a,
(
select max(sc.score) [Math] from stu  
join sc on stu.stuid=sc.stuid
where stu.classid=@classid and sc.subjectid=2 
) b,
from
(
select max(sc.score) [English] from stu  
join sc on stu.stuid=sc.stuid
where stu.classid=@classid and sc.subjectid=3 
) c
--年级最高分  --班级最低分   
 select N'班级最高分',a.chinese,b.Math,c.English
from 
(
select min(sc.score) [chinese] from stu  
join sc on stu.stuid=sc.stuid
where stu.classid=@classid and sc.subjectid=1
) a,
(
select min(sc.score) [Math] from stu  
join sc on stu.stuid=sc.stuid
where stu.classid=@classid and sc.subjectid=2 
) b,
from
(
select min(sc.score) [English] from stu  
join sc on stu.stuid=sc.stuid
where stu.classid=@classid and sc.subjectid=3 
) c
--年级最低分    drop table sc
drop table stu
drop table subject