如何在 SQL Server 中使用 row_number()

发布于 2024-11-02 05:31:03 字数 166 浏览 2 评论 0原文

我想更新列 (p_id) 的 row_number 为 1 的行数据..但此语法提供错误：

update app1 
set p_id = 1 
where Row_Number() = 1 over(p_id)

原文

I want to update row data where the row_number of the column (p_id) is 1.. but this syntax is providing error:

update app1 
set p_id = 1 
where Row_Number() = 1 over(p_id)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

因为看清所以看轻 2024-11-09 05:31:03

您不能直接使用 ROW_NUMBER() - 您需要使用 CTE（通用表表达式）：

;WITH DataToUpdate AS
(
    SELECT
       SomeID,
       p_id,
       ROW_NUMBER() OVER(ORDER BY .......) AS 'RowNum'
    FROM
       dbo.app1
)
UPDATE DataToUpdate
SET p_id = 1
WHERE 
   RowNum = 1

为了使用 ROW_NUMBER 函数，您还需要至少有一个 ORDER BY 子句来定义行的排序顺序。

从您的问题来看，您不太清楚您想要按照什么标准（列）进行排序来确定您的 ROW_NUMBER()，也不清楚有什么类型的列来唯一标识一行（所以可以应用UPDATE）

You can't use ROW_NUMBER() directly - you need to e.g. use a CTE (Common Table Expression) for that:

;WITH DataToUpdate AS
(
    SELECT
       SomeID,
       p_id,
       ROW_NUMBER() OVER(ORDER BY .......) AS 'RowNum'
    FROM
       dbo.app1
)
UPDATE DataToUpdate
SET p_id = 1
WHERE 
   RowNum = 1

In order to use the ROW_NUMBER function, you also need at least an ORDER BY clause to define an order by which the rows are ordered.

From your question, it's not very clear what criteria (column) you want to order by to determine your ROW_NUMBER(), and it's also not clear what kind of column there is to uniquely identify a row (so that the UPDATE can be applied)

回复收藏 0 原文

追我者格杀勿论 2024-11-09 05:31:03

这只会更新该年龄的第一位员工。可用作彩票类型逻辑

create table emp(name varchar(3),Age int, Salary int, IncentiveFlag bit)

insert into emp values('aaa',23,90000,0);
insert into emp values('bbb',22,50000,0);
insert into emp values('ccc',63,60000,0);
insert into emp values('ddd',53,50000,0);
insert into emp values('eee',23,80000,0);
insert into emp values('fff',53,50000,0);
insert into emp values('ggg',53,50000,0);

update A
set IncentiveFlag=1
from 
(
        Select row_number() over (partition by Age order by age ) AS SrNo,* from emp
)A
where A.SrNo=1

This will update only the first employee of that age. May be used as a lottery type logic

create table emp(name varchar(3),Age int, Salary int, IncentiveFlag bit)

insert into emp values('aaa',23,90000,0);
insert into emp values('bbb',22,50000,0);
insert into emp values('ccc',63,60000,0);
insert into emp values('ddd',53,50000,0);
insert into emp values('eee',23,80000,0);
insert into emp values('fff',53,50000,0);
insert into emp values('ggg',53,50000,0);

update A
set IncentiveFlag=1
from 
(
        Select row_number() over (partition by Age order by age ) AS SrNo,* from emp
)A
where A.SrNo=1

回复收藏 0 原文