如何基于日期为SQL Server数据添加正负有序计数器？

问题描述 #

我有一个SQL Server查询返回如下数据，每个IdCx始终对应相同的FecCx：

需要针对每个IdCx，根据FecTest是否在FecCx之前/之后，添加一个按时间顺序的索引列Order：

• 若FecTest早于FecCx，行号按负数递增（越接近FecCx，数值越大）
• 若FecTest晚于FecCx，行号按正数递增（越晚的时间，数值越大）

期望输出：

解决方案 #

直接用ROW_NUMBER()窗口函数结合CASE表达式实现需求，核心逻辑是按IdCx分组，分别对FecTest在FecCx前后的行排序并生成对应序号：

SELECT 
    IdCx,
    FecCx,
    FecTest,
    Resultado,
    CASE
        -- 处理早于FecCx的行：按FecTest降序（靠近FecCx的排前面）生成行号，再取负
        WHEN FecTest < FecCx THEN -ROW_NUMBER() OVER (PARTITION BY IdCx ORDER BY FecTest DESC)
        -- 处理晚于FecCx的行：按FecTest升序生成正行号
        WHEN FecTest > FecCx THEN ROW_NUMBER() OVER (PARTITION BY IdCx ORDER BY FecTest ASC)
        -- 若存在FecTest等于FecCx的情况，可根据需求调整（这里默认设为0）
        ELSE 0
    END AS [Order]
FROM 
    你的表名;

逻辑说明 #

1. 分组计算：通过PARTITION BY IdCx确保每个IdCx单独生成序号，互不干扰
2. 早于FecCx的行：按FecTest DESC排序后，生成的行号取负，就能得到-1、-2这类结果，保证越接近FecCx的行数值越大
3. 晚于FecCx的行：按FecTest ASC排序，直接生成正序号，时间越晚序号越大
4. 相等场景：如果存在FecTest和FecCx完全相同的行，代码默认设为0，可根据实际业务需求修改

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