多表关联查询：按优先级获取指定Code_Prix的价格数据

场景与需求 #

有两张通过Produit_ID关联的表：

• Products表（约15k行）：存储产品基础信息
• Prices表（约120k行）：存储产品的多价格条目，需优先选取Code_Prix=658的行，若无则选取Code_Prix=222的行
  要求单次查询获取约500条产品+对应优先价格的全字段数据，性能优先级最高。

表结构示例 #

-- Products表
Produit_ID | Name
1          | Some product
2          | Some other product

-- Prices表
Price_ID   | Produit_ID | Code_Prix | Validity_From | Validity_To
1          | 1          | 222       | 2024-01-01    | 2100-01-01
2          | 1          | 658       | 2024-01-01    | 2100-01-01
3          | 2          | 222       | 2024-01-01    | 2100-01-01

核心解决方案：窗口函数ROW_NUMBER() #

这是大数据量下性能最优的单次查询方案，利用窗口函数按优先级排序后取最优行：

SELECT 
    p.*, 
    pr.Price_ID, pr.Code_Prix, pr.Validity_From, pr.Validity_To
FROM 
    Products p
LEFT JOIN (
    SELECT 
        *,
        -- 按Code_Prix优先级排序：658排第1，222排第2，其他排最后
        ROW_NUMBER() OVER (
            PARTITION BY Produit_ID 
            ORDER BY CASE Code_Prix WHEN 658 THEN 1 WHEN 222 THEN 2 ELSE 3 END
        ) AS price_rank
    FROM Prices
    WHERE Code_Prix IN (658, 222) -- 过滤无关价格，减少计算量
) pr ON p.Produit_ID = pr.Produit_ID AND pr.price_rank = 1
-- 按需添加WHERE条件过滤目标500个产品，例如：WHERE p.Produit_ID IN (...)

逻辑说明 #

• 子查询仅保留符合优先级的价格行，缩小窗口函数的计算范围
• PARTITION BY Produit_ID按产品分组，每组内按指定优先级排序
• 关联时仅取每组排序第一的行，即该产品的最优价格

性能优化要点 #

• 添加复合索引：给Prices表创建(Produit_ID, Code_Prix)复合索引，窗口函数的分组、排序可直接利用索引，避免全表扫描；Products表的Produit_ID需确保有主键/唯一索引（通常默认已配置）
• 精准过滤数据：在主查询和子查询中都添加目标产品的过滤条件（如指定500个Produit_ID），避免处理全表数据
• 避免冗余字段：若业务允许，不要用SELECT *，明确列出需要的字段，减少数据传输量

备选兼容方案：LEFT JOIN 优先级匹配 #

若数据库不支持窗口函数（极少场景），可使用两次LEFT JOIN优先匹配：

SELECT 
    p.*,
    COALESCE(pr658.Price_ID, pr222.Price_ID) AS Price_ID,
    COALESCE(pr658.Code_Prix, pr222.Code_Prix) AS Code_Prix,
    COALESCE(pr658.Validity_From, pr222.Validity_From) AS Validity_From,
    COALESCE(pr658.Validity_To, pr222.Validity_To) AS Validity_To
FROM Products p
LEFT JOIN Prices pr658 ON p.Produit_ID = pr658.Produit_ID AND pr658.Code_Prix = 658
LEFT JOIN Prices pr222 ON p.Produit_ID = pr222.Produit_ID AND pr222.Code_Prix = 222
-- 添加目标产品过滤条件

注意：若同一产品存在多个同Code_Prix的价格，此方案会返回多行，需额外去重，性能弱于窗口函数方案。

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