Mysql连接四个表并显示NULL值 [英] Mysql join four tables and show NULL value

问题描述

我需要查询以获取每个学生的特定考试结果，并且我还想为未输入分数的学生显示NULL....

I need to do a query to get the result of an specific exam for each student , and I also want to display NULL for student who didn't enter his scores....

这是我的四个桌子

1. Student表
2. Scores表
3. Student
4. student_subject

1. Student table
2. Scores table
3. Student
4. student_subject

我的表格链接

• 得分表(user_id)和学生表(user_id)
• 带有成绩表(exam.id)的考试表(id)

• student_subject(user_id)和学生表(user_id)

• Score table (user_id) with Student table (user_id)
• Exam table ( id ) with scores table ( exam.id)

• student_subject ( user_id ) with student table ( user_id)

带有考试表(group_id)的学生主题(group_id)(出于某些重要原因，我的组数据库位于另一个数据库上)

student_subject (group_id) with exam table (group_id) (my group database is on another database for some important reason)

我的查询有效，但是对于没有输入分数的学生，我没有NULL值

My query works, but I don't have a NULL value for the student who don't have enter his score

SELECT 
    scores.result, students.id, exam.name, exam.id
FROM 
    scores
LEFT JOIN  
    students ON scores.user_id = students.user_id
LEFT JOIN 
    exam ON exam.id = scores.exam_id
LEFT JOIN 
    students_subjects as ss ON ss.user_id = students.id
LEFT JOIN 
    students_subjects ON students_subjects.group_id = exam.group_id
WHERE
    exam.id = 32
GROUP BY
    scores.id

输出

对于每个未通过特定考试的学生(exam.id = 32)，我如何获得空值?

How can I get a null value for each student who don't have score for a specific exam ( exam.id = 32 ) ?

为@sceaj编辑

我在查询时收到了这个信息(为了更好地进行测试，我将WHERE tests.id = 34更改为WHERE Exam.id = 36)

I got this with you're query ( I change WHERE exam.id = 34 for WHERE exam.id = 36 for better testing)

SELECT scores.result,students.id,exam.name, exam.id
FROM exam
INNER JOIN students_subjects ON students_subjects.group_id = exam.group_id
INNER JOIN students ON students_subjects.user_id = students.user_id
LEFT OUTER JOIN scores ON scores.user_id = students.user_id
WHERE exam.id = 36

推荐答案

以下内容应返回所有学生，如果存在，则返回考试32的分数.

The following should return all students, with scores from exam 32 if they exist.

SELECT scores.result,students.id,exam.name, exam.id
FROM exam
INNER JOIN scores ON exam.id = scores.exam_id
RIGHT OUTER JOIN students ON scores.user_id = students.user_id
WHERE exam.id = 32

您的select子句未使用students_subjects或聚合函数中的任何内容，因此我不确定该联接的目的是什么?也许您可以从上面开始，然后从那里开始构建.

Your select clause didn't use anything from students_subjects or aggregate functions, so I'm not sure what the join to it was for? Perhaps you can start with the above and build from there.

基于我的第一条评论的新策略.尝试找到所有学生，然后找到存在的分数，如果没有，则为null.

New strategy based on my first comment. Try and locate all students then find scores that exist, null if none.

SELECT scores.result,students.id,exam.name, exam.id
FROM exam
INNER JOIN students_subjects ON students_subjects.group_id = exam.group_id
INNER JOIN students ON students_subjects.user_id = students.user_id
LEFT OUTER JOIN scores ON scores.user_id = students.user_id
  AND scores.exam_id = exam.id
WHERE exam.id = 32

这篇关于Mysql连接四个表并显示NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！