如何编写SQL WHERE语句实现权限列为空时返回全部数据的需求

实现方案 #

你原来的写法问题在于子查询返回多列/多行结果，不能直接作为WHERE的判断条件，可参考以下两种方案实现需求：

方案1：单权限值场景（每个用户每个维度仅对应1条权限规则）

先通过CTE将当前用户的各维度权限转成单行字段，再做匹配判断：

WITH user_permissions AS (
    SELECT
        MAX(CASE WHEN type = '国家' THEN restriction END) AS country_perm,
        MAX(CASE WHEN type = '区域' THEN restriction END) AS region_perm,
        MAX(CASE WHEN type = '品牌' THEN restriction END) AS brand_perm
    FROM t2
    WHERE user_id = {{current_userId()}}
)
SELECT t1.*
FROM t1, user_permissions
WHERE
    (country_perm IS NULL OR t1.国家 = country_perm)
    AND (region_perm IS NULL OR t1.区域 = region_perm)
    AND (brand_perm IS NULL OR t1.品牌 = brand_perm)

• 逻辑说明：每个维度的判断都遵循「权限为空则跳过过滤，否则仅匹配权限值」的规则，完全符合你提到的业务要求，比如用户hvfg23的权限为国家空、区域=欧洲、品牌空时，就会返回所有国家、欧洲区域、所有品牌的全量数据。

方案2：多权限值场景（每个用户每个维度可对应多条权限规则）

如果单个用户同一维度可能有多个权限值（比如同时拥有中国、日本两个国家的访问权限），可使用EXISTS写法：

SELECT t1.*
FROM t1
WHERE
    EXISTS (
        SELECT 1 FROM t2 
        WHERE user_id = {{current_userId()}} 
        AND type = '国家' 
        AND (restriction IS NULL OR t1.国家 = restriction)
    )
    AND EXISTS (
        SELECT 1 FROM t2 
        WHERE user_id = {{current_userId()}} 
        AND type = '区域' 
        AND (restriction IS NULL OR t1.区域 = restriction)
    )
    AND EXISTS (
        SELECT 1 FROM t2 
        WHERE user_id = {{current_userId()}} 
        AND type = '品牌' 
        AND (restriction IS NULL OR t1.品牌 = restriction)
    )

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