基于优先级与通配符获取同表中DDD不同值的两条Top记录优化方案

可以利用**窗口函数ROW_NUMBER()**实现仅扫描一次表，同时获取DDD='true'和DDD='false'各自的Top1记录，避免两次重复查询的冗余：

SELECT AAA, BBB, CCC, DDD
FROM (
    SELECT 
        AAA, BBB, CCC, DDD,
        ROW_NUMBER() OVER (
            PARTITION BY DDD 
            ORDER BY DECODE(AAA, '*', 0, 4) + DECODE(BBB, '*', 0, 2) + DECODE(CCC, '*', 0, 1) DESC
        ) AS rn
    FROM TABLE_A
    WHERE 
        (AAA='a1' OR AAA='*')
        AND (BBB='b1' OR BBB='*')
        AND (CCC='c1' OR CCC='*')
        AND DDD IN ('true', 'false') -- 同时过滤两种DDD值
)
WHERE rn = 1; -- 取每个DDD分组下的第一条高优先级记录

方案优势 #

• 仅对TABLE_A执行一次全表/索引扫描，比UNION ALL两次扫描的性能更优，数据量越大优势越明显
• 逻辑集中，无需重复编写筛选和排序规则，后续维护更方便
• 天然保证每个DDD分组仅返回一条最高优先级的记录，完全匹配需求

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