我正在尝试使用 methodNOT IN和过滤学生的所有成绩或不成绩IN。我NOT IN在为学生选择空成绩时使用，而为IN我选择有成绩的学生。是否可以将它们全部放在一个查询而不是两个查询中？因为我想同时选择它们然后在我班上的某个地方打电话。
查询IN：
SELECT subject_mt.subject_id, student_mt.student_id,
registration_mt.firstname, registration_mt.middlename, registration_mt.lastname,
subject_mt.title,
MAX(IF(g.gradingperiod_id = 7000, g.grade, ""))AS first,
MAX(IF(g.gradingperiod_id = 7001, g.grade, "")) AS second,
MAX(IF(g.gradingperiod_id = 7002, g.grade, "")) AS third,
MAX(IF(g.gradingperiod_id = 7003, g.grade, "")) AS fourth,
g.final
FROM faculty_schedule
INNER JOIN schedule_mt ON schedule_mt.schedule_id = faculty_schedule.schedule_id
INNER JOIN section_mt ON section_mt.section_id = schedule_mt.section_id
INNER JOIN section_student ON section_student.section_id = section_mt.section_id
INNER JOIN student_mt ON student_mt.student_id = section_student.student_id
INNER JOIN registration_mt ON registration_mt.registration_id = student_mt.registration_id
INNER JOIN subject_mt ON subject_mt.subject_id = schedule_mt.subject_id
INNER JOIN student_grade AS sg ON sg.student_id = student_mt.student_id
INNER JOIN grade AS g ON g.grade_id = sg.grade_id
//WHERE CLAUSE HERE
GROUP BY
subject_mt.subject_id, student_mt.student_id,
registration_mt.firstname, registration_mt.middlename, registration_mt.lastname,
subject_mt.title,
g.final;
查询NOT IN：
INNER JOIN student_grade AS sg ON sg.student_id = student_mt.student_id
INNER JOIN grade AS g ON g.grade_id = sg.grade_id
WHERE faculty_schedule.faculty_id = pIN_facultyId
AND schedule_mt.section_id = pIN_sectionId
AND sg.student_id IN (SELECT student_id FROM student_grade)
所以我试图在我的评论旁边结束我的答案。我认为查询看起来像这样，使用LEFT JOIN您需要过滤所有有或没有成绩的数据。
SELECT subject_mt.subject_id, student_mt.student_id,
registration_mt.firstname, registration_mt.middlename, registration_mt.lastname,
subject_mt.title,
MAX(IF(g.gradingperiod_id = 7000, g.grade, ""))AS first,
MAX(IF(g.gradingperiod_id = 7001, g.grade, "")) AS second,
MAX(IF(g.gradingperiod_id = 7002, g.grade, "")) AS third,
MAX(IF(g.gradingperiod_id = 7003, g.grade, "")) AS fourth,
g.final
FROM faculty_schedule
INNER JOIN schedule_mt ON schedule_mt.schedule_id = faculty_schedule.schedule_id
INNER JOIN section_mt ON section_mt.section_id = schedule_mt.section_id
INNER JOIN section_student ON section_student.section_id = section_mt.section_id
INNER JOIN student_mt ON student_mt.student_id = section_student.student_id
INNER JOIN registration_mt ON registration_mt.registration_id = student_mt.registration_id
INNER JOIN subject_mt ON subject_mt.subject_id = schedule_mt.subject_id
LEFT JOIN student_grade AS sg ON sg.student_id = student_mt.student_id
LEFT JOIN grade AS g ON g.grade_id = sg.grade_id
WHERE faculty_schedule.faculty_id = pIN_facultyId
AND schedule_mt.section_id = pIN_sectionId
AND sg.student_id IN (SELECT student_id FROM student_grade)
GROUP BY
subject_mt.subject_id, student_mt.student_id,
registration_mt.firstname, registration_mt.middlename, registration_mt.lastname,
subject_mt.title,
g.final;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句