SQL中按月份对ID生成序数（仅INV>0时生效）

这个需求可以通过窗口函数生成序数加上自定义后缀逻辑来实现，我给你拆解成两步，再给出完整的SQL代码：

核心思路 #

1. 先按ID分组，对每个组内的记录按Dates排序，用ROW_NUMBER()生成基础序数——注意这个序数只对INV>0的行生效，INV=0的行返回Null。
2. 把生成的数字序数转换成带英文后缀的字符串（比如1→1st、2→2nd），这里要处理特殊情况：11/12/13不管末尾数字都是th，其他数字根据最后一位判断后缀。

通用SQL实现（适用于MySQL、SQL Server、PostgreSQL等） #

SELECT 
    ID,
    INV,
    Dates,
    CASE 
        WHEN INV <= 0 THEN NULL
        ELSE CONCAT(
            OrdinalNumber,
            CASE 
                -- 处理11、12、13的特殊情况
                WHEN OrdinalNumber % 100 IN (11, 12, 13) THEN 'th'
                WHEN OrdinalNumber % 10 = 1 THEN 'st'
                WHEN OrdinalNumber % 10 = 2 THEN 'nd'
                WHEN OrdinalNumber % 10 = 3 THEN 'rd'
                -- 其他数字统一用th
                ELSE 'th'
            END
        ) AS ExpectedResult
FROM (
    -- 子查询：按ID分组、日期排序生成基础序数
    SELECT 
        ID,
        INV,
        Dates,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Dates) AS OrdinalNumber
    FROM abc
) AS sub_query;

代码解释 #

• 子查询sub_query：用ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Dates)给每个ID组内的记录按日期顺序生成递增的序号，不管INV是否大于0都生成（后续会过滤）。
• 外层CASE判断：如果INV<=0直接返回Null；如果INV>0，就把序数和对应的后缀拼接起来。
• 后缀逻辑：优先处理11/12/13的特殊情况（避免出现11st这种错误），再根据数字最后一位匹配对应的后缀。

针对Oracle的调整 #

如果是Oracle数据库，拼接字符串用||代替CONCAT，取模用MOD函数，代码如下：

SELECT 
    ID,
    INV,
    Dates,
    CASE 
        WHEN INV <= 0 THEN NULL
        ELSE OrdinalNumber ||
            CASE 
                WHEN MOD(OrdinalNumber, 100) IN (11, 12, 13) THEN 'th'
                WHEN MOD(OrdinalNumber, 10) = 1 THEN 'st'
                WHEN MOD(OrdinalNumber, 10) = 2 THEN 'nd'
                WHEN MOD(OrdinalNumber, 10) = 3 THEN 'rd'
                ELSE 'th'
            END
        END AS ExpectedResult
FROM (
    SELECT 
        ID,
        INV,
        Dates,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Dates) AS OrdinalNumber
    FROM abc
);

用你提供的示例数据测试，运行上面的SQL就能得到完全符合预期的结果啦。

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