一条很难的SQL语句 高手进
我有一个考试表,我的功能是想做出这个表中的错误率统计
表的结构如下：

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 
 
[解决办法]
我就知道这题肯定会被老D无情的秒杀。

不过还是本着学习锻炼的精神把脚本写了。

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,sum(case when isright=0 then 1 else 0 end) /cast(count(subjectid) as decimal(18,2))*100 as 错误率from @tgroup by subjectidorder by 错误率