SQL 函数计算

create table tb(id int, subjectid int, content varchar(10) , isright int)

 insert into tb values(1 , 101 , '题目内容', 0 )

 insert into tb values(2 , 101 , '题目内容', 0 )

 insert into tb values(3 , 101 , '题目内容', 1 )

 insert into tb values(4 , 101 , '题目内容', 1 )

 insert into tb values(5 , 102 , '题目内容', 0 )

 insert into tb values(6 , 102 , '题目内容', 1 )

 insert into tb values(7 , 103 , '题目内容', 0 )

 insert into tb values(8 , 103 , '题目内容', 1 )

 insert into tb values(9 , 103 , '题目内容', 1 )

go

select subjectid, sum(case isright when 0 then 1 else 0 end)'错误数]', count(1) '总题目数',

 cast(sum(case isright when 0 then 1 else 0 end ) * 100.0 / (select count(1) from tb where subjectid = t.subjectid)

as decimal(18,2)) '错误率'

 from tb t group by subjectid order by subjectid