我有一个答案键,例如,数据库中的相应点
number answer points
1 A 2
2 A 3
3 B 2
4 B 3
现在,当我输入并检查学生的答案时,
number answer
1 A
2 A
3 C
4 D
我希望系统将学生的分数(5/10)退还给我。
我的问题是我不知道如何获取答案键和学生答案匹配的这些点总数。这是我到目前为止尝试的。
for ($i=1; $i<=$num; $i++) {
$answer = $_POST['answer'][($i+1)-1]; //this is the student answer
$result = mysql_query("SELECT * FROM paper WHERE sy='$sy' and sem='$sem' and test_name='$test_name' and id='$i' and subject='$subject'");
$qry = mysql_fetch_array($result);
$ans = $qry['answer']; //this is the answer key
if ($answer == $ans){
$resultA = mysql_query("SELECT SUM(points) FROM paper WHERE sy='$sy' and sem='$sem' and test_name='$test_name' and id='$i' and answer='$answer' and subject='$subject'");
$qryA = mysql_fetch_array($resultA);
$score = $qry['SUM(points)'];
您的模式不包括学生ID,因此我不考虑了这一点:
select sum(points) as Score
from answerkey k
inner join answers a on k.number = a.number
where k.answer = a.answer
SQL小提琴示例
使用学生ID,您会这样做GROUP BY
:
select a.student_id, sum(points) as Score
from answerkey k
left outer join answers a on k.number = a.number
where k.answer = a.answer
group by a.student_id
您应该真正显示您尝试的内容,并解释了您的问题...但是,无法抗拒:
SELECT
SUM(k.Points) As Score
FROM Answers a
JOIN AnswerScoreKey k
ON k.Number = a.Number AND k.Answer = a.Answer
WITH
test_answer (number, answer, points) AS (VALUES
(1, 'A', 2),
(2, 'A', 3),
(3, 'B', 2),
(4, 'B', 3)),
student_answer (number, answer) AS (VALUES
(1, 'A'),
(2, 'A'),
(3, 'C'),
(4, 'D'))
SELECT
SUM(ta.points) AS score,
(SELECT SUM(points) FROM test_answer) AS total_possible_score
FROM student_answer sa
INNER JOIN test_answer ta
ON sa.number = ta.number
AND sa.answer = ta.answer;
sql查询可以做到这一点..
SELECT SUM(k.points) AS TOTAL FROM k.key,s.answers WHERE s.answer = k.answer;
其中:K.Key是您的答案密钥,S.Answers是您的学生的考验。您可以添加其他逻辑以将其限制为一个。
我终于明白了!我只是将[($i+1)-1]
更改为[$i-1]
。