如何用Oracle SQL查询未分配Surveyor的车辆标签列表？

先明确表结构与需求：

• CarTags：存储车辆标签、公司名、所属账号ID（字段：tag、company_name、account_id）
• Employees：存储员工信息（字段：id、name、email、account_id、role_id）
• Roles：存储员工角色映射，其中role_id=1对应Surveyor（勘测员）、2=Admin（管理员）、3=Engineer（工程师）

需求是找出所有未分配勘测员角色员工的车辆标签，包括完全没有分配任何员工的车辆。

原查询存在拼写错误：Employees.acount_id应为Employees.account_id，以下是两种可行的修改方案：

方案一：用NOT EXISTS直接排除有勘测员的车辆 #

SELECT DISTINCT
    ct.tag AS "车辆标签",
    ct.company_name AS "公司名称"
FROM
    CarTags ct
WHERE
    NOT EXISTS (
        SELECT 1
        FROM Employees e
        JOIN Roles r ON e.id = r.employee_id
        WHERE e.account_id = ct.account_id
          AND r.role_id = 1 -- 匹配勘测员角色
    );

逻辑说明 #

子查询会检查当前车辆所属账号下，是否存在任何员工被分配了勘测员角色。如果不存在，就保留这条车辆记录。DISTINCT用于避免同一车辆因关联多个非勘测员角色员工而重复显示。

方案二：用LEFT JOIN筛选无勘测员的账号 #

SELECT DISTINCT
    ct.tag AS "车辆标签",
    ct.company_name AS "公司名称"
FROM
    CarTags ct
LEFT JOIN (
    -- 先筛选出所有有勘测员角色员工的账号ID
    SELECT DISTINCT e.account_id
    FROM Employees e
    JOIN Roles r ON e.id = r.employee_id
    WHERE r.role_id = 1
) surveyor_accounts ON ct.account_id = surveyor_accounts.account_id
WHERE
    surveyor_accounts.account_id IS NULL;

逻辑说明 #

先通过子查询找出所有存在勘测员的账号，再用左连接关联车辆表。没有匹配上的车辆（即所属账号无勘测员）会返回NULL，通过IS NULL筛选出这些目标车辆。DISTINCT同样用于去重。

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