我有一个考试表,我的功能是想做出这个表中的错误率统计
表的结构如下：
id subjectid(外键,这里指向题库表) content(考试题目内容) isright(是否正确,0错误,1正确)
1 101 题目内容 0
2 101 题目内容 0
3 101 题目内容 1
4 101 题目内容 1
5 102 题目内容 0
6 102 题目内容 1
7 103 题目内容 0
8 103 题目内容 1
9 103 题目内容 1
我的思路是：先根据 subjectid group by 一下 进行分组,然后得出每个题目错误的个数，还要得出 每个题目出现的总数，（等上例子就是得出101的总数为4 错误的为2个，这样显示结果出来）
然后排序一下，错的多的排
在前面，等于就是order by 一下错误的个数 ， 小弟想了很久也没写出这个SQL来 感激不尽
------解决方案--------------------
- SQL code
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 )goselect subjectid, 错误率 = cast(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)) as varchar) + '%'from tb tgroup by subjectiddrop table tb/*subjectid 错误率 ----------- ------------------------------- 101 50.00%102 50.00%103 33.33%（所影响的行数为 3 行）*/
------解决方案--------------------
- SQL code
select subjectid,content,count(*) as 题目数量, sum(case when isright=0 then 1 else 0 end) as 错误数量， cast(sum(case when isright=0 then 1 else 0 end)/count(*)*100 as char(2))+'%' as 错误率 from tb group by subjectid,content order by sum(case when isright=0 then 1 else 0 end) desc
------解决方案--------------------
- SQL code
declare @t table(id int,subjectid int,[content] varchar(10), isright bit)insert @t select 1,101,'题目内容',0insert @t select 2,101,'题目内容',0insert @t select 3,101,'题目内容',1insert @t select 4,101,'题目内容',1insert @t select 5,102,'题目内容',0insert @t select 6,102,'题目内容',1insert @t select 7,103,'题目内容',0insert @t select 8,103,'题目内容',1insert @t select 9,103,'题目内容',1select subjectid,[content],count(1) 题目出现总数,sum(1-isright) 每题出错个数from @tgroup by subjectid,contentorder by subjectidsubjectid content 题目出现总数 每题出错个数----------- ---------- ----------- -----------101 题目内容 4 2102 题目内容 2 1103 题目内容 3 1(3 行受影响)
------解决方案--------------------
- SQL code
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 )goselect 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