排版有点不对称:(这是我最后要的结果)
studentNo(int) sumAnswerCount(int) rightAnswerCount(int) errorAnswerCount(int)
(总共答了多少个题目) (答对了几题) (答错了几题)1 10 6 4
studentNo(int) sumAnswerCount(int) rightAnswerCount(int) errorAnswerCount(int)
(总共答了多少个题目) (答对了几题) (答错了几题)1 10 6 4
,sumAnswerCount=(select count(*) from questions where a.questionNo=questionNo)
,rightAnswerCount=(select count(*) from questions where a.questionNo=questionNo and answer=a.content)
,errorAnswerCount=(select count(*) from questions where a.questionNo=questionNo and answer!=a.content)from student a
sumAnswerCount = count(a.questionNo),
rightAnswerCount = sum( case when b.answer = '对' then 1 else 0 end),
errorAnswerCount = sum( case when b.answer = '对' then 0 else 1 end)
from students a
left jion questions b on a.questionNo = b.questionNogroup by a.studentNo
如果是客觀題就比較簡單了
--客觀題的話
select studentNo,
count(*) as sumAnswerCount,
sum(case when content=answer then 1 else 0 end) as rightAnswerCount,
sum(case when coutent=answer then 0 else 1 end) as errorAnswerCount
from
(
select A.studentNo, A.questionNo,A.content, B.answer
from students A
inner join questions B
on A.questionNo=B.questionNo
) T
group by studentNo
---小梁,这儿不对吧,不是自己的题目也统计呀
sumAnswerCount = count(a.questionNo),
rightAnswerCount = sum( case when b.answer = a.content then 1 else 0 end),
errorAnswerCount = sum( case when b.answer = a.content then 0 else 1 end)
from students a
left jion questions b on a.questionNo = b.questionNogroup by a.studentNo对,如乳沟兄所说,content 要与answer ,只能客观
(
select t.studentNo,
sumAnswerCount = (select count(*) from students where studentNo = t.studentNo),
rightAnswerCount = (select count(*) from questions , students where students.studentNo = t.studentNo and questions.questionNo = students.questionNo and students.content = questions.answer)
from students t
) m
服务器: 消息 207,级别 16,状态 3,行 1;列名 'questionNo' 无效。playwarcraft前辈显示的错误是:
服务器: 消息 207,级别 16,状态 3,行 1
列名 'questionNo' 无效。
服务器: 消息 207,级别 16,状态 1,行 1
列名 'coutent' 无效。
本人sql 很白痴,望各位前辈 多多赐教。。