mysql - SQL查询使用子查询选择每个学生的出勤率不能使用父查询中的行

Question

我在 SQL 中遇到问题，我正在尝试计算我正在构建的注册系统中某个类(class)的学生出勤率，但是它不允许我在子查询中使用父列，

SELECT A.student_id, TRUNCATE((100 - ((100/B.reg_num) * C.abs_num)), 0) AS attendance FROM
students A
JOIN (
SELECT COUNT(*) AS reg_num
FROM students
JOIN seminargroup_student ON seminargroup_student.student_id = students.student_id
JOIN seminar_group ON seminar_group.seminar_group_id = seminargroup_student.seminar_group_id
JOIN modules ON modules.module_id = seminar_group.seminar_group_module_id
JOIN register_seminar ON register_seminar.seminar_id = seminar_group.seminar_group_id
JOIN registers ON registers.register_id = register_seminar.register_id
WHERE modules.module_id =1 AND students.student_id = A.student_id
) B
JOIN (
SELECT COUNT(*) AS abs_num
FROM students
JOIN seminargroup_student ON seminargroup_student.student_id = students.student_id
JOIN seminar_group ON seminar_group.seminar_group_id = seminargroup_student.seminar_group_id
JOIN modules ON modules.module_id = seminar_group.seminar_group_module_id
JOIN absence ON students.student_id = absence.student_id
WHERE modules.module_id =1 AND students.student_id = A.student_id
) C

这将返回:#1054 - “where 子句”中的未知列“A.student_id”

感谢您的帮助!

Answer 1

it won't let me use the parent column in the sub query

一般来说，您不需要在 FROM 中的子查询中引用父级。

相反，您只需将连接字段添加到子查询中的 SELECT 和 GROUP BY 子句中，然后连接

例如

SELECT students.student_id, 
       Truncate(( 100 - ( ( 100 / b.reg_num ) * c.abs_num ) ), 0) AS attendance 
FROM   students 
       JOIN (SELECT a.studentid, 
                    Count(*) AS reg_num 
             FROM   students A 
                    JOIN seminargroup_student 
                      ON seminargroup_student.student_id = A.student_id 
                    JOIN seminar_group 
                      ON seminar_group.seminar_group_id = 
                         seminargroup_student.seminar_group_id 
                    JOIN modules 
                      ON modules.module_id = 
                         seminar_group.seminar_group_module_id 
                    JOIN register_seminar 
                      ON register_seminar.seminar_id = 
                         seminar_group.seminar_group_id 
                    JOIN registers 
                      ON registers.register_id = register_seminar.register_id 
             GROUP  BY a.studentid) A 
         ON students.studentid = a.student.id 
       JOIN (SELECT a.studentid, 
                    Count(*) AS abs_num 
             FROM   students aA 
                    JOIN seminargroup_student 
                      ON seminargroup_student.student_id = a.student_id 
                    JOIN seminar_group 
                      ON seminar_group.seminar_group_id = 
                         seminargroup_student.seminar_group_id 
                    JOIN modules 
                      ON modules.module_id = 
                         seminar_group.seminar_group_module_id 
                    JOIN absence 
                      ON a.student_id = absence.student_id 
             GROUP  BY a.studentid) b 
         ON students.studentid = b.student.id 

作为旁注，如果您使用左连接并在 PK 字段上而不是在 * 上执行 DISTINCT COUNT，则不必执行两个子查询

SELECT 
    A.student_id, 
    TRUNCATE((100 - ((100/counts.reg_num) * counts.abs_num)), 0) AS attendance
FROM
students A   
JOIN 
(SELECT 
    COUNT(DISTINCT absence.absence_id) AS abs_num , --OR whatever the PK is
    COUNT(DISTINCT registers.regeister_id) as reg_num,
    students.student_id


FROM   students 
       JOIN seminargroup_student 
         ON seminargroup_student.student_id = students.student_id 
       JOIN seminar_group 
         ON seminar_group.seminar_group_id = 
            seminargroup_student.seminar_group_id 
       JOIN modules 
         ON modules.module_id = seminar_group.seminar_group_module_id 

       LEFT JOIN register_seminar 
         ON register_seminar.seminar_id = seminar_group.seminar_group_id 
       LEFT JOIN registers 
         ON registers.register_id = register_seminar.register_id   


       LEFT JOIN absence 
         ON students.student_id = absence.student_id 
GROUP BY
    students.student_id) COUNTS
ON a.student_id = coutnts.student_ID