mysql - Mysql如何对三张多列表进行联合查询，只获取一个id

Question

我有三个这样的表:

学生

stuNum | stuName
------------------
2012   | jack 
2013   | tom

测验

quizNum | quizName
------------------
1       | chapter 1
2       | chapter 2
3       | chapter 3

学生评估

stuNum | quizNum | assessmentMark
-----------------------------------
2012   | 1       | 10
2012   | 2       | 8
2012   | 3       | 10
2013   | 1       | 5

我想要得到这样的结果

stuNum | stuName | Quiz Num | assessmentMark
--------------------------------------------
2012   | jack    | 1        | 10
2012   | jack    | 2        | 8
2012   | jack    | 3        | 10

描述:所有三个表都已连接..我想获取所有stuNum都完成的stuNum=2012、quizNum。

我尝试了几种组合来获取结果，但无法工作。这是我尝试过的示例组合:

$sql = "select a.stuNum,a.quizNum,a.assessmentMark from studentassessment a inner join student b on a.stuNum=b.stuNum inner join quiz c on a.quizNum=c.quizNum where b.stuNum='2012'"

Answer 1

查询

SELECT a.stuNum,
b.stuName,
a.quizNum,
c.quizName,
a.assessmentMark
FROM studentAssessment a
JOIN student b
ON a.stuNum=b.stuNum
JOIN quiz c
ON a.quizNum=c.quizNum
WHERE a.stuNum=2012;

输出

+--------+---------+---------+-----------+----------------+
| STUNUM | STUNAME | QUIZNUM |  QUIZNAME | ASSESSMENTMARK |
+--------+---------+---------+-----------+----------------+
|   2012 |    Jack |       1 | chapter 1 |             10 |
|   2012 |    Jack |       2 | chapter 2 |              8 |
|   2012 |    Jack |       3 | chapter 3 |             10 |
+--------+---------+---------+-----------+----------------+

<强> Fiddle demo