SQL Server查询时仅将结果首行ID改为0的简化实现方法

原始表结构与数据 #

示例表的创建和数据插入语句如下：

create table employee 
(
    id int primary key,
    name varchar(50),
    department varchar(10)
)

insert into employee 
values (2, 'John', 'HR'),
       (5, 'Michael', 'IT'),
       (28, 'Rakesh', 'TP'),
       (49, 'Xi', 'IT')

需求说明 #

查询该表中所有部门为'IT'的员工时，需将结果首行的ID值改为0，预期输出如下：

id   name      department
-------------------------
0    Michael   IT
49   Xi        IT

此前通过临时表实现的代码如下：

update #temp 
set id = 0 
where id = (select min(id) from #temp)

现需仅通过SELECT语句实现该需求，无需使用临时表。

解决方案 #

可以借助窗口函数ROW_NUMBER()标记结果行的顺序，再通过CASE语句修改首行的ID值：

SELECT
    CASE WHEN rn = 1 THEN 0 ELSE id END AS id,
    name,
    department
FROM (
    SELECT
        id,
        name,
        department,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM employee
    WHERE department = 'IT'
) t

说明 #

• 内层子查询通过ROW_NUMBER()按id排序，为每行标记行号rn，首行的行号为1
• 外层查询利用CASE判断，当行号为1时将id替换为0，其余行保留原id值
• 若需要按其他逻辑确定首行，只需修改OVER (ORDER BY id)中的排序字段即可

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