SQL LEFT JOIN空值处理：无权限用户继承同部门用户权限咨询

这个需求我熟！核心就是优先用用户自身权限，没有的话 fallback 到同部门其他用户的权限，我给你两种实用的实现方案，适配不同场景：

首先先明确下假设的表结构（你可以根据实际表名/字段调整）：

-- 用户表：存储用户和所属部门
CREATE TABLE users (
    user_id VARCHAR(50) PRIMARY KEY,
    dept_id INT NOT NULL
);

-- 权限表：存储用户的权限关联
CREATE TABLE user_permissions (
    user_id VARCHAR(50),
    permission VARCHAR(50),
    PRIMARY KEY (user_id, permission),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

方案1：基础版（适合部门仅需复用指定用户权限的场景） #

如果你明确知道同部门里的参考用户是A，或者只需要取同部门任意一个有用户的权限，用COALESCE结合子查询就能快速实现：

SELECT
    u.user_id,
    -- 优先取自身权限，自身为空则取同部门其他用户的权限
    COALESCE(
        up.permission,
        (
            SELECT permission 
            FROM user_permissions up2
            JOIN users u2 ON up2.user_id = u2.user_id
            WHERE u2.dept_id = u.dept_id 
              AND u2.user_id != u.user_id -- 排除自身
            LIMIT 1 -- 如果部门多个用户有权限，取第一条
        )
    ) AS permission
FROM users u
LEFT JOIN user_permissions up ON u.user_id = up.user_id
WHERE u.user_id = 'B'; -- 替换为你要查询的用户ID

说明： #

• 如果用户B有自己的权限，COALESCE会直接返回自身权限，不会执行后面的子查询；
• 如果用户B没有权限，就会去拉取同部门其他用户的权限；
• 子查询里的LIMIT 1是避免部门多个用户有权限时返回多条，你可以根据需求调整（比如去掉LIMIT返回所有同部门权限，或者加条件指定用户A）。

方案2：进阶版（适合多用户部门的灵活场景） #

如果部门里有多个用户，需要复用所有同部门用户的权限，用窗口函数会更灵活，还能处理更复杂的逻辑：

WITH user_dept_perms AS (
    SELECT
        u.user_id,
        u.dept_id,
        up.permission AS own_permission,
        -- 标记用户是否有自身权限
        CASE WHEN up.permission IS NOT NULL THEN 1 ELSE 0 END AS has_own_perm,
        -- 收集同部门所有用户的权限（去重）
        ARRAY_AGG(DISTINCT up2.permission) OVER (PARTITION BY u.dept_id) AS dept_all_perms
    FROM users u
    LEFT JOIN user_permissions up ON u.user_id = up.user_id
    -- 关联同部门所有用户的权限
    LEFT JOIN user_permissions up2 
        ON up2.user_id IN (SELECT user_id FROM users WHERE dept_id = u.dept_id)
)
SELECT
    user_id,
    CASE
        WHEN has_own_perm = 1 THEN own_permission
        ELSE UNNEST(dept_all_perms) -- 将权限数组拆分成单行
    END AS permission
FROM user_dept_perms
WHERE user_id = 'B'
-- 去重避免重复权限
GROUP BY user_id, permission;

说明： #

• 这个方案用CTE先把用户自身权限、同部门所有权限都收集好；
• 如果用户自身有权限，直接返回自身的；没有的话就把同部门所有权限展开成多行；
• 注意：ARRAY_AGG和UNNEST是PostgreSQL的语法，如果你用MySQL，可以换成GROUP_CONCAT和SUBSTRING_INDEX来处理，或者用临时表拆分。

边界情况处理 #

如果部门里没有任何用户有权限，上面的查询还是会返回NULL，你可以再加一层COALESCE设置默认权限：

COALESCE(
    up.permission,
    (SELECT permission FROM ...),
    'default_access' -- 设置你的默认权限值
)

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