select count(*) as '总人数'
from Score
where LessonID='3' and StudentID in(select distinct studentID from student where ClassID='2班')select count (*) as '不及格人数'
from Score
where LessonID='3' and StudentID in(select distinct studentID from student where ClassID='2班') and ResultCredit='0')
在数据库里这样是查的出数据的,写在页面里的时候是
string sql="select count(*) as 'num1' from Score where LessonID='3' and StudentID in(select distinct studentID from student where ClassID='2班') ";
string sql1="select count (*) as 'num2' from Score where LessonID='3' and StudentID in(select distinct studentID from student where ClassID='2') and ResultCredit='0'";
ResultCredit='0'学分等于0的意思。
我现在想求不及格的人数比例。
可是用sql/sql1的话就只是两条语句相比,怎么能取出它的值来呢
from Score
where LessonID='3' and StudentID in(select distinct studentID from student where ClassID='2班')select count (*) as '不及格人数'
from Score
where LessonID='3' and StudentID in(select distinct studentID from student where ClassID='2班') and ResultCredit='0')
在数据库里这样是查的出数据的,写在页面里的时候是
string sql="select count(*) as 'num1' from Score where LessonID='3' and StudentID in(select distinct studentID from student where ClassID='2班') ";
string sql1="select count (*) as 'num2' from Score where LessonID='3' and StudentID in(select distinct studentID from student where ClassID='2') and ResultCredit='0'";
ResultCredit='0'学分等于0的意思。
我现在想求不及格的人数比例。
可是用sql/sql1的话就只是两条语句相比,怎么能取出它的值来呢
SELECT ( select count (*)
from Score
where LessonID='3' and StudentID in(select distinct studentID from student where ClassID='2班') and ResultCredit='0'
) / (select count(*)
from Score
where LessonID='3' and StudentID in(select distinct studentID from student where ClassID='2班')
) * 1.00也就是这样的格式:SELECT (查询不及格的总数) / (查询总数) * 1.00或者在页面中直接将两条语句的结果相除就是结果了。
Access可以用iifSQL SERVER 如下:
select sum(case ResultCredit='0' then 1 else 0 end)/count(*)
from Score
where LessonID='3' and StudentID in(select distinct studentID from student where ClassID='2班')
ACCESSselect sum(IIF( ResultCredit='0' , 1 , 0 ))/count(*)
from Score
where LessonID='3' and StudentID in(select distinct studentID from student where ClassID='2班')