select ClassCode,stuName,sum(case when Grade>=60 then 1 else 0 end ) as pass
, round(sum(case when Grade>=60 then 1 else 0 end ))*100/sum(1) as varchar) +'%'
as 通过率
from Grade_Tablem a ,stu_table b ,class_table c
where a.stuCode=b.stuCode and b.ClassCode=c.ClassCode
, round(sum(case when Grade>=60 then 1 else 0 end ))*100/sum(1) as varchar) +'%'
as 通过率
from Grade_Tablem a ,stu_table b ,class_table c
where a.stuCode=b.stuCode and b.ClassCode=c.ClassCode
declare @SubID int=1select a.ClassCode,a.stuName,sum(case when Grade>=60 then 1 else 0 end ) as pass
, sum(case when Grade>=60 then 1 else 0 end )*100/sum(1) as varchar) +'%'
as 通过率
from Grade_Tablem a ,stu_table b ,class_table c
where a.stuCode=b.stuCode and b.ClassCode=c.ClassCode and SubID=@SubID
group by a.ClassCode,a.stuName
set nocount on
--成绩表Grade_Table
create table #grade_table(stuCode varchar(4),stuName varchar(20), SubID int, Grade int)
go
insert into #grade_table values('A001','name1', 1,90)
insert into #grade_table values('A002','name2', 1, 80)
insert into #grade_table values('A001','name3', 2, 60)
insert into #grade_table values('A004','name4', 1, 30)
insert into #grade_table values('B001','name5', 1, 20)
insert into #grade_table values('B002','name6', 1, 80)
go
--学生表stu_table
create table #stu_table(StuCode varchar(4),stuName varchar(20), ClassCode char(2))
insert into #stu_table values('A001','name1','A')
insert into #stu_table values('A002','name2','A')
insert into #stu_table values('B001','name5','B')
insert into #stu_table values('B002','name6','B')
go
--班级表class_table
create table #class_table(classcode char(2),stu_number int)
insert into #class_table values('A', 5)
insert into #class_table values('B ', 4)
go
/*要查询每个班每门课程的通过率 如下,
SubID=1 (作为参数)
ClassCode stuName pass(>60) 通过率
A 5 2 40%
B 4 1 25%
*/declare @SubID int
select @subid=1
select b.classcode ,c.stu_number,sum(case when a.grade>60 then 1 else 0 end) as pass,
convert(varchar(10),sum(case when a.grade>60 then 1 else 0 end)*100/c.stu_number)+'%' as 合格率
from #grade_table a inner join #stu_table b on a.stucode=b.stucode
inner join #class_table c on b.classcode=c.classcode
group by b.classcode,c.stu_number--select a.ClassCode,a.stu_number,sum(case when c.Grade>=60 then 1 else 0 end ) as pass
--, sum(case when c.Grade>=60 then 1 else 0 end )*100/sum(1) as 通过率
--from #Grade_Table c ,#stu_table b ,#class_table a
--where a.stuCode=b.stuCode and b.ClassCode=c.ClassCode and SubID=@SubID
--group by a.ClassCodego
drop table #grade_table
drop table #stu_table
drop table #class_table