这是我的数据库模式
学生桌:
studentid (PK) |studentname
1 |A
类表:
classid (PK) |classname
1 |Math
2 |Science
教学桌:
teachid (PK) | classid | studentid
1 | 1 | 1
2 | 2 | 1
测验表:
quizid (PK) |classid | quizname
1 |1 | mathquiz
2 |2 | science quiz
quizscore表:
quizscoreid(PK) | quizid |studentid |score
1 | 1 |1 |60
2 | 2 |1 |0
这是我使用的sql查询:
SELECT
quiz.quizid,
teach.classid,
student.studentname,
student.studentid,
quizscore.score,
quiz.quizname,
class.classname
FROM teach
INNER JOIN class
ON teach.classid = class.classid
INNER JOIN student
ON teach.studentid = student.studentid
INNER JOIN quiz
ON quiz.classid = '1'
LEFT JOIN quizscore
ON teach.studentid = quizscore.studentid
WHERE
teach.classid='1' AND
teach.classid = quiz.classid AND
quiz.quizid = '1'
ORDER BY studentid ASC
其输出如下:
studentid |quizid |classid | studentname |classname | quizname | score
1 | 1 |1 | A |Math | mathquiz | 60
1 | 1 |1 | A |Science | mathquiz | 0
当我期待这样的时候:
studentid | quizid |classid | studentname |classname | quizname | score
1 | 1 |1 | A |Math | mathquiz | 60
请帮助我更正sql查询
2条答案
按热度按时间toiithl61#
演示
llycmphe2#
我发现缺少两个连接,我已经在sql中进行了更正。试试看,希望你能得到预期的结果。