Oracle SQL如何仅在前一访问权限匹配条件未命中时执行后续低优先级条件

你可以通过给不同匹配规则赋予优先级权重，按权重倒序排序后取第一条记录的方式实现需求，修改后SQL如下（兼容MySQL/PostgreSQL等支持LIMIT语法的数据库）：

SELECT * 
FROM COMPANY_ACCESS ca
WHERE 
    ca.CAN_ACCESS = true
    AND ca.COMPANY_ID = ?1
ORDER BY 
    -- 匹配优先级从高到低赋值
    CASE 
        WHEN ca.AREA_ID = ?2 AND ca.TEAM_ID = ?3 THEN 3
        WHEN ca.AREA_ID = ?2 AND ca.TEAM_ID IS NULL THEN 2
        WHEN ca.AREA_ID IS NULL AND ca.TEAM_ID IS NULL THEN 1
        ELSE 0 
    END DESC
LIMIT 1

逻辑说明 #

1. WHERE条件中保留CAN_ACCESS = true和COMPANY_ID = ?1，过滤掉无权限记录和其他公司的无关记录
2. ORDER BY子句通过CASE表达式给每条符合条件的记录计算优先级得分：三个字段全匹配得分最高为3，区域匹配、团队为空得2分，公司级权限得1分，按得分倒序排列后最高优先级的记录会排在第一位
3. 最后通过LIMIT 1仅返回排在最前的最高优先级记录

其他数据库适配 #

如果使用不支持LIMIT语法的数据库，可以替换取第一条的逻辑：

• Oracle 12c+：把末尾的LIMIT 1替换为FETCH FIRST 1 ROW ONLY
• Oracle 12c之前版本：使用子查询加ROWNUM实现：

SELECT * FROM (
    SELECT * 
    FROM COMPANY_ACCESS ca
    WHERE 
        ca.CAN_ACCESS = true
        AND ca.COMPANY_ID = ?1
    ORDER BY 
        CASE 
            WHEN ca.AREA_ID = ?2 AND ca.TEAM_ID = ?3 THEN 3
            WHEN ca.AREA_ID = ?2 AND ca.TEAM_ID IS NULL THEN 2
            WHEN ca.AREA_ID IS NULL AND ca.TEAM_ID IS NULL THEN 1
            ELSE 0 
        END DESC
) WHERE ROWNUM = 1

• SQL Server：在SELECT后加TOP 1即可

注意事项 #

如果同一优先级下存在多条匹配记录，可以在ORDER BY后追加排序规则（比如按ID DESC取最新创建的权限），避免返回结果不确定。

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