基于自定义排序规则为产品包数据生成正确排名的SQL问题

假设你的表名为product_packages，可以通过以下SQL实现符合要求的排名：

WITH product_type_dates AS (
    SELECT 
        `Product No`,
        MAX(CASE WHEN `Package Type` LIKE '%UCC%' THEN `Input Date` END) AS latest_ucc_date,
        MAX(CASE WHEN `Package Type` LIKE '%UPC%' THEN `Input Date` END) AS latest_upc_date
    FROM product_packages
    GROUP BY `Product No`
)
SELECT 
    p.`Product No`,
    p.`Package Type`,
    p.Length,
    p.`Input Date`,
    ROW_NUMBER() OVER (
        PARTITION BY p.`Product No`
        ORDER BY 
            CASE
                WHEN `Package Type` LIKE '%UCC%' AND `Input Date` = latest_ucc_date THEN 1
                WHEN `Package Type` LIKE '%UPC%' AND `Input Date` = latest_upc_date THEN 2
                WHEN `Package Type` LIKE '%UCC%' THEN 3
                WHEN `Package Type` LIKE '%UPC%' THEN 4
                ELSE 5
            END ASC,
            `Input Date` DESC
    ) AS priority_rank
FROM product_packages p
JOIN product_type_dates d ON p.`Product No` = d.`Product No`
ORDER BY `Product No`, priority_rank;

逻辑说明 #

• 预计算最新日期：通过CTEproduct_type_dates按Product No分组，分别算出每个产品下UCC、UPC类型的最新Input Date，用来判定记录是否属于「最新批次」。
• 定义优先级等级：用CASE语句严格匹配你的排序规则：
  • 等级1：最新批次的UCC（UCC类型且日期等于该产品UCC最新日期）
  • 等级2：最新批次的UPC（UPC类型且日期等于该产品UPC最新日期）
  • 等级3：旧批次的UCC（UCC类型但日期早于最新UCC日期）
  • 等级4：旧批次的UPC（UPC类型但日期早于最新UPC日期）
• 生成排名：在每个Product No分区内，先按优先级等级升序（等级1最靠前），同等级内再按Input Date降序（确保同批次内最新记录排前），通过ROW_NUMBER()生成排名。

错误原因分析 #

你之前的SQL应该是直接按Package Type（UCC在前）+Input Date降序排序，这种逻辑会把所有UCC类型记录（哪怕是旧批次）排在UPC类型前面，导致旧UCC出现在最新UPC之前，完全不符合「最新UPC>旧UCC」的优先级要求。

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