‘壹’ sql中创建存储过程,该存储过程计算及格率和优秀率(平均分超80分）
select convert(varchar(10),sum(case when (html+sql)/2>=60 and (html+sql)/2<80 then 1 else 0 end)*100.0/
stucount)+'%' as 及格率,
convert(varchar(10),sum(case when (html+sql)/2>=80 then 1 else 0 end)*100.0/stucount)+'%' as 优秀率
from exam e,(select count(1) as stucount from stuinfo) a
‘贰’ 怎样用查询语句在数据库表中求及格率
没有考虑成绩表中一个人有多条课程的成绩记录的情况,应在之前查询方法上加上约束,如某班某课程中及格人数占总人数的百分比,即select (select COUNT(*) from Score where Cno=144502 and Cono=011 and Grade>=60)/(select COUNT(*) from Score where Cno=144502 and Cono=011)
通常成绩表中的数据项为学号,课程号,成绩,班级号 ,当一个人选了多门课有及格的也有不及格的时,仅仅靠楼上的回答不足以完成正确的查询,查询时要注意约束条件
‘叁’ 一条SQL语句查询出三年级所有老师所带学生的及格率 :老师名称 老师所带学生数目 及格学生数目
select C_TEACH_NAME,(select count(*) as count0 from c,e,f where c.C_TEACH_CODE=e. C_TEACH_CODE and e.C_CLASS_CODE=f.C_CLASS_CODE group by C_TEACH_NAME),(select count(*) as count1,round(count1/count0,2) from c,e,f,g where c.C_TEACH_CODE=e. C_TEACH_CODE and e.C_CLASS_CODE=f.C_CLASS_CODE and f.C_STUDENT_CODE=g.C_STUDENT_CODE and N_STUDENT_SCORE>=60 group by C_TEACH_NAME) from c group by C_TEACH_NAME;
‘肆’ sql select语句求及格率（带百分号）
select cast( cast ( ( select count(1) as jige from 表 where 课程名称 = 'C语言' and 分数 >= 60 ) as numeric(10,4) ) / cast ( ( select count(1) as jige from 表 where 课程名称 = 'C语言' ) as numeric(10,4) ) * 100 as varchar(20) ) + '%'
‘伍’ 求sql 分组计算合格率问题
select 厂家,型号,不合格数量,合格数量,合格数量/((不合格数量+合格数量)/1.00) 合格率, 不合格数量/((不合格数量+合格数量)/1.00) 不良率 from (
select 厂家,型号,sum((case when 状态=7 then 数量 else 0 end))不合格数量,sum((case when 状态<>7 then 数量 else 0 end)) 合格数量 from (
select 厂家,型号,状态,SUM(数量)数量 from tab
group by 厂家,型号,状态,SUM(数量)
) t
group by 厂家,型号
) t2