MySQL中如何根据后缀筛选最新订单？

要获取每个订单的最新版本，核心思路是先拆分订单号的基础前缀和后缀数字，再针对每个基础前缀筛选出后缀最大的记录（无后缀的订单视为后缀为0，优先级低于带后缀的新版本）。以下是几种通用的实现方式：

方法一：使用窗口函数（推荐，兼容多数现代数据库） #

通过CTE拆分订单前缀和后缀，再用ROW_NUMBER()窗口函数给每个基础订单组的记录按后缀降序编号，取每组的第一条即为最新订单：

WITH OrderCTE AS (
    SELECT 
        OrderNumber,
        -- 提取基础订单前缀
        CASE 
            WHEN CHARINDEX('-', OrderNumber) > 0 THEN LEFT(OrderNumber, CHARINDEX('-', OrderNumber) - 1)
            ELSE OrderNumber
        END AS BaseOrder,
        -- 提取后缀数字，无后缀则设为0
        CASE 
            WHEN CHARINDEX('-', OrderNumber) > 0 THEN CAST(RIGHT(OrderNumber, LEN(OrderNumber) - CHARINDEX('-', OrderNumber)) AS INT)
            ELSE 0
        END AS Suffix
    FROM Transactions
)
SELECT OrderNumber
FROM OrderCTE
WHERE ROW_NUMBER() OVER (PARTITION BY BaseOrder ORDER BY Suffix DESC) = 1
ORDER BY OrderNumber;

方法二：分组聚合关联 #

先分组统计每个基础订单的最大后缀，再关联原表匹配对应记录：

WITH BaseOrders AS (
    SELECT 
        CASE 
            WHEN CHARINDEX('-', OrderNumber) > 0 THEN LEFT(OrderNumber, CHARINDEX('-', OrderNumber) - 1)
            ELSE OrderNumber
        END AS BaseOrder,
        MAX(
            CASE 
                WHEN CHARINDEX('-', OrderNumber) > 0 THEN CAST(RIGHT(OrderNumber, LEN(OrderNumber) - CHARINDEX('-', OrderNumber)) AS INT)
                ELSE 0
            END
        ) AS MaxSuffix
    FROM Transactions
    GROUP BY 
        CASE 
            WHEN CHARINDEX('-', OrderNumber) > 0 THEN LEFT(OrderNumber, CHARINDEX('-', OrderNumber) - 1)
            ELSE OrderNumber
        END
)
SELECT t.OrderNumber
FROM Transactions t
JOIN BaseOrders b ON 
    (CHARINDEX('-', t.OrderNumber) > 0 AND LEFT(t.OrderNumber, CHARINDEX('-', t.OrderNumber) - 1) = b.BaseOrder 
     AND CAST(RIGHT(t.OrderNumber, LEN(t.OrderNumber) - CHARINDEX('-', t.OrderNumber)) AS INT) = b.MaxSuffix)
    OR (CHARINDEX('-', t.OrderNumber) = 0 AND t.OrderNumber = b.BaseOrder AND b.MaxSuffix = 0)
ORDER BY t.OrderNumber;

适配MySQL的版本 #

如果使用MySQL，需替换部分字符串函数（LOCATE替代CHARINDEX，LENGTH替代LEN）：

WITH OrderCTE AS (
    SELECT 
        OrderNumber,
        CASE 
            WHEN LOCATE('-', OrderNumber) > 0 THEN LEFT(OrderNumber, LOCATE('-', OrderNumber) - 1)
            ELSE OrderNumber
        END AS BaseOrder,
        CASE 
            WHEN LOCATE('-', OrderNumber) > 0 THEN CAST(RIGHT(OrderNumber, LENGTH(OrderNumber) - LOCATE('-', OrderNumber)) AS UNSIGNED)
            ELSE 0
        END AS Suffix
    FROM Transactions
)
SELECT OrderNumber
FROM OrderCTE
WHERE ROW_NUMBER() OVER (PARTITION BY BaseOrder ORDER BY Suffix DESC) = 1
ORDER BY OrderNumber;

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