MySQL同表条件依赖查询：按优先级获取最优Cancellation Policy

需求说明 #

现有一张包含ID、Cancellation_Policy_Type、Cancellation_Policy_Hours字段的取消政策表（假设表名为cancellation_policies），需要为每个ID按以下规则筛选最优政策：

• 优先选取Free Cancellation类型中最小的Cancellation_Policy_Hours记录
• 若没有Free Cancellation，则选取Partially Refundable类型的记录
• 前两者都不存在时，选取No Refundable类型的记录

最终要将筛选出的最优政策与包含业务字段的Production表关联，输出每个ID对应的完整业务数据+最优政策信息。

示例数据集 #

cancellation_policies表 #

Production表 #

常见错误查询示例 #

这类查询通常会出现随机选政策、未按优先级筛选的问题：

-- 错误示例：分组后随机选取政策，未遵循优先级和最小小时数规则
SELECT p.*, cp.Cancellation_Policy_Type, cp.Cancellation_Policy_Hours
FROM Production p
LEFT JOIN cancellation_policies cp ON p.ID = cp.ID
GROUP BY p.ID;

正确解决方案 #

方案1：MySQL 8.0+ 用窗口函数实现（推荐） #

利用ROW_NUMBER()窗口函数给每条记录按优先级和小时数排序，直接取每个ID的第一条记录作为最优政策：

WITH ranked_policies AS (
    SELECT 
        ID,
        Cancellation_Policy_Type,
        Cancellation_Policy_Hours,
        -- 定义优先级排序规则：Free > Partially > No Refundable；同类型取最小小时数
        ROW_NUMBER() OVER (
            PARTITION BY ID 
            ORDER BY 
                CASE Cancellation_Policy_Type 
                    WHEN 'Free Cancellation' THEN 1 
                    WHEN 'Partially Refundable' THEN 2 
                    WHEN 'No Refundable' THEN 3 
                END,
                Cancellation_Policy_Hours ASC
        ) AS rn
    FROM cancellation_policies
)
SELECT 
    p.*,
    rp.Cancellation_Policy_Type,
    rp.Cancellation_Policy_Hours
FROM Production p
LEFT JOIN ranked_policies rp 
    ON p.ID = rp.ID 
    AND rp.rn = 1; -- 取每个ID的最优政策（排名第一的记录）

方案2：MySQL 5.x 兼容版（无窗口函数） #

通过子查询和NOT EXISTS实现优先级筛选：

SELECT 
    p.*,
    cp.Cancellation_Policy_Type,
    cp.Cancellation_Policy_Hours
FROM Production p
LEFT JOIN (
    SELECT 
        cp1.ID,
        cp1.Cancellation_Policy_Type,
        cp1.Cancellation_Policy_Hours
    FROM cancellation_policies cp1
    WHERE NOT EXISTS (
        SELECT 1 
        FROM cancellation_policies cp2
        WHERE cp2.ID = cp1.ID
        AND (
            -- 存在更高优先级的政策
            CASE cp2.Cancellation_Policy_Type 
                WHEN 'Free Cancellation' THEN 1 
                WHEN 'Partially Refundable' THEN 2 
                WHEN 'No Refundable' THEN 3 
            END < CASE cp1.Cancellation_Policy_Type 
                WHEN 'Free Cancellation' THEN 1 
                WHEN 'Partially Refundable' THEN 2 
                WHEN 'No Refundable' THEN 3 
            END
            -- 同优先级但小时数更小
            OR (
                CASE cp2.Cancellation_Policy_Type 
                    WHEN 'Free Cancellation' THEN 1 
                    WHEN 'Partially Refundable' THEN 2 
                    WHEN 'No Refundable' THEN 3 
                END = CASE cp1.Cancellation_Policy_Type 
                    WHEN 'Free Cancellation' THEN 1 
                    WHEN 'Partially Refundable' THEN 2 
                    WHEN 'No Refundable' THEN 3 
                END
                AND cp2.Cancellation_Policy_Hours < cp1.Cancellation_Policy_Hours
            )
        )
    )
) cp ON p.ID = cp.ID;

期望输出示例 #

根据示例数据集，最终查询结果应为：

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