联合子查询中的 MySQL 外部查询引用不起作用

Question

谁能帮我重建以下 MySQL 查询，使其正常工作:

SELECT c.id, COUNT(DISTINCT d.student_id) FROM contract c,  (
    SELECT student_id FROM ilc_course ic, contract_seat cs2
        WHERE c.id = cs2.contract
        AND cs2.id = ic.contract_seat
        AND ic.valid = 1 AND cs2.valid = 1
        AND (ic.finished IS NULL OR ic.finished > CURRENT_TIMESTAMP)
    UNION
    SELECT student_id FROM glc_to_user_profile_student gtups, glc_course gc, contract_seat cs2
        WHERE c.id = cs2.contract
        AND gtups.contract_seat = cs2.id AND gtups.glc_course = gc.id
        AND gtups.valid = 1 AND gc.valid = 1 AND cs2.valid = 1
        AND (gtups.left_date IS NULL OR gtups.left_date > CURRENT_TIMESTAMP)
        AND (gc.end_date IS NULL OR gc.end_date > CURRENT_TIMESTAMP)
) d GROUP BY c.id;

该查询应该计算来自两个来源的所有不同学生的数量，并按契约(Contract)对他们进行分组。

问题是子查询对 c.id 的引用。显然，子查询对契约表一无所知。

我非常感谢帮助使其发挥作用。

谢谢!

Answer 1

您需要在子查询中选择cs2.contract，然后在外部查询中将其与c.id连接。

SELECT c.id, COUNT(d.student_id) 
FROM contract c
JOIN (
    SELECT cs2.contract, student_id 
    FROM ilc_course ic
    JOIN contract_seat cs2 ON cs2.id = ic.contract_seat
    WHERE ic.valid = 1 AND cs2.valid = 1
        AND (ic.finished IS NULL OR ic.finished > CURRENT_TIMESTAMP)
    UNION
    SELECT cs2.contract, student_id 
    FROM glc_to_user_profile_student gtups
    JOIN glc_course gc ON gtups.glc_course = gc.id
    JOIN contract_seat cs2 ON gtups.contract_seat = cs2.id 
    WHERE gtups.valid = 1 AND gc.valid = 1 AND cs2.valid = 1
        AND (gtups.left_date IS NULL OR gtups.left_date > CURRENT_TIMESTAMP)
        AND (gc.end_date IS NULL OR gc.end_date > CURRENT_TIMESTAMP)
) d ON c.id = d.contract
GROUP BY c.id;

此外，由于 UNION 会从子查询中删除重复项，因此您无需在 COUNT() 中使用 DISTINCT。