『壹』 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