MySQL多表关联查询：展示所有用户与活动的报名状态

我有以下三张数据表：

user (id, name)
activity (id, name)
enrolment (id, user_id, activity_id)

需求是返回所有用户的列表，同时展示每个用户对应的所有活动，以及该用户是否报名了该活动。假设有3个活动，预期返回结果如下：

user1     activity1     "not enrolled"
user1     activity2     "enrolled"
user1     activity3     "not enrolled"

user2     activity1     "enrolled"
user2     activity2     "enrolled"
user2     activity3     "not enrolled"
...

我尝试了多种JOIN组合但没成功，求解决建议。

编辑1：
Barmar针对简化场景给出了解决方案：

SELECT u.name AS user, a.name AS activity, IF(e.id IS NULL, 'not enrolled', 'enrolled') AS enrolled
FROM user AS u
CROSS JOIN activity AS a
LEFT JOIN enrolment AS e ON u.id = e.user_id AND a.id = e.activity_id
ORDER BY u.name, a.name

但实际场景更复杂，我之前简化了表结构——enrolment表中并没有activity_id字段，报名与活动的关联是通过单独的enrolment_activity表实现的，实际表结构如下：

user (id, name)
activity (id, name)
enrolment (id, user_id)
enrolment_activity (id, activity_id, enrolment_id)

我尝试了以下查询语句：

SELECT u.name AS user, a.name AS activity, IF(e.id IS NULL, 'not enrolled', 'enrolled') AS enrolled
FROM user AS u
CROSS JOIN activity AS a
LEFT JOIN enrolment AS e ON u.id = e.user_id
LEFT JOIN enrolment_activity AS ea ON (ea.activity_id = a.id  AND ea.enrolment_id = e.id)
ORDER BY u.name, a.name

但没得到正确结果，存在大量重复数据且报名状态列不准确。

编辑2：
我通过子查询实现了需求，但不确定这是不是最优方案，可行的查询语句如下：

SELECT u.name AS user, a.name AS activity, IF(e.id IS NULL, 'not enrolled', 'enrolled') AS enrolled
FROM user AS u
CROSS JOIN activity AS a
LEFT JOIN enrolment AS e ON u.id = e.user_id AND a.id = (SELECT activity_id FROM enrolment_activity WHERE enrolment_id = e.id)
ORDER BY u.name, a.name

你的子查询方案可以正常工作，但可以通过调整JOIN逻辑避免子查询，提升查询效率同时解决重复数据问题：

方案1：先聚合报名与活动的关联关系 #

SELECT 
    u.name AS user, 
    a.name AS activity, 
    CASE WHEN ua.activity_id IS NOT NULL THEN 'enrolled' ELSE 'not enrolled' END AS enrolled
FROM user u
CROSS JOIN activity a
LEFT JOIN (
    -- 先获取每个用户对应的所有活动ID
    SELECT e.user_id, ea.activity_id
    FROM enrolment e
    INNER JOIN enrolment_activity ea ON e.id = ea.enrolment_id
) ua ON u.id = ua.user_id AND a.id = ua.activity_id
ORDER BY u.name, a.name;

方案2：调整左连接的关联条件并去重 #

SELECT 
    u.name AS user, 
    a.name AS activity, 
    IF(ea.id IS NOT NULL, 'enrolled', 'not enrolled') AS enrolled
FROM user u
CROSS JOIN activity a
LEFT JOIN enrolment e ON u.id = e.user_id
LEFT JOIN enrolment_activity ea ON e.id = ea.enrolment_id AND a.id = ea.activity_id
-- 按用户和活动分组去重
GROUP BY u.id, a.id, u.name, a.name
ORDER BY u.name, a.name;

问题分析 #

你之前的查询出错原因在于：

• 左连接enrolment时，会拉取用户的所有报名记录，再连接enrolment_activity会导致同一个用户-活动组合出现多条重复数据。
• 用e.id IS NULL判断报名状态是错误的——只要用户有任何报名记录，e.id就不为空，会错误地把用户未报名的活动也标记为enrolled。

正确的判断逻辑应该是检查当前用户-活动组合是否存在于enrolment_activity关联表中，而非单纯判断用户是否有报名记录。

内容的提问来源于stack exchange，提问作者Hubert