SQL Server实现name列变化时trig列交替输出0和1方案咨询

解决方案 #

你可以直接通过SQL窗口函数实现需求，无需额外的业务逻辑开发，SQL Server 2015完全支持以下语法：

场景1：临时查询生成（仅导出Excel用，无需修改表结构，推荐）

直接在查询时计算trig值，不需要对原表做任何修改：

SELECT
    (DENSE_RANK() OVER(ORDER BY name) - 1) % 2 AS trig,
    name -- 可补充你需要查询的其他字段
FROM 替换为你的实际表名;

逻辑说明：

• DENSE_RANK() OVER(ORDER BY name) 会给相同的name值分配相同的排名，排名从1开始递增
• 排名减1后对2取模，就可以实现name变化时trig在0和1之间交替，同name的所有行trig值完全一致的效果

如果你需要按照name在表中首次出现的顺序来交替trig值，而非按照name本身的排序，可以使用以下写法（需你的表中有自增主键/创建时间这类可标识记录顺序的字段，以下以自增主键id为例）：

SELECT
    (DENSE_RANK() OVER(ORDER BY first_id) - 1) % 2 AS trig,
    name -- 可补充你需要查询的其他字段
FROM (
    SELECT
        *,
        MIN(id) OVER(PARTITION BY name) AS first_id
    FROM 替换为你的实际表名
) t

场景2：持久化新增trig列到表中

如果你需要将trig字段固化到原表中，可执行以下操作：

1. 新增trig字段：

ALTER TABLE 替换为你的实际表名 ADD trig BIT;

2. 批量更新所有历史数据的trig值：

WITH trig_calc AS (
    SELECT 
        *,
        (DENSE_RANK() OVER(ORDER BY name) - 1) % 2 AS calc_val
    FROM 替换为你的实际表名
)
UPDATE trig_calc SET trig = calc_val;

注意：如果选择持久化存储，后续新增数据、修改name字段值时需要同步更新trig值，建议配合触发器实现，否则会出现数据不一致问题，非必要场景更推荐使用临时查询生成的方案。

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