如何设置列值等于行号? [英] how to set column value equal to row no?

问题描述

如何将在更改表后添加的列的值设置为等于 sql server 2008 中的行号.那是我希望该列的值等于 no.行.我还希望此字段允许 NULL 值.所以它就像自动增量但允许空值，这就是为什么不想使用带有自动增量的标识或主键列.那么如何设置成行号呢?任何帮助将不胜感激.

How can i set value of column that has been added after altering table equal to row no in sql server 2008. That is i want value of the column equal to no. of row. I also want this field to allow NULL values. So it is like auto increment but allowing null values that's why don't want to use identity or primary key column with auto increment. So how can it be set to row no? Any help will be appreciated.

推荐答案

如果您尝试直接使用 ROW_NUMBER() 更新列，您将得到...

If you try to UPDATE a column directly using ROW_NUMBER() you'll get...

窗口函数只能出现在 SELECT 或 ORDER BY 子句中.

Windowed functions can only appear in the SELECT or ORDER BY clauses.

...所以改为 INNER JOIN 表本身...

...so instead INNER JOIN the table to itself...

UPDATE
    [test123]
SET
    [row_number] = [x].[rn]
FROM
    [test123]
INNER JOIN
    (
        SELECT
            [test_id],
            ROW_NUMBER() OVER (ORDER BY [test_id]) AS rn
        FROM
            [test123]
    ) AS x
ON 
    [test123].[test_id] = [x].[test_id]

这篇关于如何设置列值等于行号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！