SQL查询需求：同复合主键其他列相同时优先选c1=v2的行

针对表t1以c1+c2+…+cn为复合主键、c1仅取值v1或v2，需在c2至cn取值相同时优先选c1=v2行（无则选c1=v1行）的场景，以下是两种比row_number()窗口函数更高效的方案，可避免分区过多导致的性能瓶颈：

方案一：UNION ALL + EXISTS 精准筛选 #

先直接取出所有c1=v2的行，再补充无对应v2行的v1行，执行逻辑轻量化：

-- 先取所有c1=v2的行
SELECT t.*
FROM t1 t
WHERE t.c1 = 'v2'

UNION ALL

-- 仅取没有对应v2行的c1=v1行
SELECT t.*
FROM t1 t
WHERE t.c1 = 'v1'
  AND NOT EXISTS (
      SELECT 1
      FROM t1 t2
      -- 匹配所有除c1外的主键列
      WHERE t2.c2 = t.c2
        AND t2.c3 = t.c3
        -- 依次补充c4到cn的匹配条件
        AND t2.cn = t.cn
        AND t2.c1 = 'v2'
  );

优势 #

• 无需全表分区排序，大数据量下性能更优；
• 若c1=v2的行占比较高，第一个查询直接返回大部分结果，第二个查询仅处理小部分数据。

方案二：GROUP BY 确定优先值后关联 #

通过分组获取每组（c2至cn相同）的优先c1值，再关联原表取出完整行：

WITH preferred_rows AS (
    SELECT 
        c2, c3, ..., cn,
        -- 利用MAX优先选取v2（字符串场景下'v2'排序高于'v1'）
        MAX(c1) AS target_c1
    FROM t1
    GROUP BY c2, c3, ..., cn
)
SELECT t.*
FROM t1 t
JOIN preferred_rows pr
    ON t.c2 = pr.c2
    AND t.c3 = pr.c3
    -- 依次补充c4到cn的关联条件
    AND t.cn = pr.cn
    AND t.c1 = pr.target_c1;

适配特殊排序场景 #

如果v2的自然排序优先级不如v1，可通过CASE转换为权重值确定优先：

MAX(CASE c1 WHEN 'v2' THEN 2 ELSE 1 END) AS priority,

后续关联时再匹配对应的c1值即可。

关键优化建议 #

建议给表创建复合索引(c2, c3, ..., cn, c1)，查询时可直接通过索引定位数据，避免全表扫描，进一步提升性能。

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