多表查询结果异常：筛选指定站点可分配任务的可用用户

原查询的问题分析 #

1. 表别名错误：LEFT JOIN时将site_task_assignments别名为ci，但后续条件中却使用sta，这会导致语法错误或逻辑异常。
2. 条件冗余且逻辑混乱：重复判断用户是否在任务表中，同时多条件组合时没有精准过滤task_id=2的任务状态，导致错误包含未完成指定任务的用户。
3. 重复行问题：LEFT JOIN会让有多个任务记录的用户生成多行结果，未做去重处理。

正确的查询语句 #

方法一：子查询方式（逻辑直观） #

SELECT u.id, u.name, u.site_id
FROM users u
WHERE u.site_id = 4
AND (
    -- 条件1：无任何任务分配
    u.id NOT IN (SELECT user_id FROM site_task_assignments)
    OR
    -- 条件2：未被分配指定任务task_id=2
    u.id NOT IN (SELECT user_id FROM site_task_assignments WHERE task_id = 2)
    OR
    -- 条件3：已分配指定任务且已完成
    EXISTS (
        SELECT 1 FROM site_task_assignments sta
        WHERE sta.user_id = u.id AND sta.task_id = 2 AND sta.complete = 1
    )
);

方法二：LEFT JOIN方式（性能更优） #

SELECT DISTINCT u.id, u.name, u.site_id
FROM users u
LEFT JOIN site_task_assignments sta
    ON u.id = sta.user_id AND sta.task_id = 2
WHERE u.site_id = 4
AND (
    -- 无指定任务记录（覆盖条件1、2） 或 指定任务已完成（条件3）
    sta.user_id IS NULL
    OR sta.complete = 1
);

逻辑说明 #

两种方法均先筛选出站点ID为4的用户，再匹配三个可用条件：

• 方法一通过三个独立子查询分别对应三个筛选规则，逻辑清晰易懂。
• 方法二通过LEFT JOIN仅关联用户的task_id=2任务记录，用DISTINCT避免重复行，同时通过sta.user_id IS NULL覆盖“无任何任务”和“未分配指定任务”两种情况，再搭配“指定任务已完成”的条件，精准匹配需求。

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