① 一條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 max(成績),min(成績),avg(成績),
(select cast(ceiling(人數*1.0/總人數*100) as varchar(3))+ '% '
from (select count(id) 人數
from table s,(select avg(成績) as 平均分 from table) b
where s.成績>=平均分) e,(select count(id) 總人數 from table) c)from table
③ 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中創建存儲過程,該存儲過程計算及格率和優秀率(平均分超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
⑤ 求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
⑥ 求各班各科及格率的sql語句
select 班級,課程,count(*)/40 as 及格率 from 表名 where 成績>60 group by 班級,課程