mysql查询多个内部连接和左连接

dphi5xsq 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(290)

这是我的数据库模式
学生桌：

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查询

mysql

来源：https://stackoverflow.com/questions/50290684/mysql-query-multiple-inner-join-and-left-join

2条答案

按热度按时间

toiithl61#

SELECT 
A.studentid, 
D.quizid, 
A.classid, 
C.studentname, 
B.classname, 
D.quizname, 
E.score
FROM
teach A 
LEFT JOIN class B ON A.classid=B.classid
LEFT JOIN student C ON A.studentid=C.studentid
LEFT JOIN quiz D ON A.classid=D.classid
LEFT JOIN quizscore E ON D.quizid=E.quizid AND E.studentid=A.studentid
WHERE 
A.studentid=1 AND 
D.quizid=1 AND  
A.classid=1;

演示

赞(0）回复(0）举报 2021-06-25

llycmphe2#

我发现缺少两个连接，我已经在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 = tech.classid
LEFT JOIN quizscore
    ON (teach.studentid = quizscore.studentid
    and quiz.quizid = quizscore.quizid)
WHERE
    teach.classid='1' AND
    teach.classid = quiz.classid AND
    quiz.quizid = '1' and
    quiz.classid = '1'
ORDER BY studentid ASC

赞(0）回复(0）举报 2021-06-25

我来回答

mysql查询多个内部连接和左连接

2条答案

相关问题

热门标签

最新问答