如何生成在选择查询自动增量场(How to generate auto increment fiel

例如，我有2列，一个表first_name和last_name这些值

Ali           Khani
Elizabette    Amini
Britney       Spears
,...

我想写一个select产生这样的表查询：

1     Ali           Khani
2     Elizabette    Amini
3     Britney       Spears
,...

谢谢你的帮助。

Answer 1:

如果它是MySQL中，你可以试试

SELECT @n := @n + 1 n,
       first_name, 
       last_name
  FROM table1, (SELECT @n := 0) m
 ORDER BY first_name, last_name

SQLFiddle

而对于SQLServer的

SELECT row_number() OVER (ORDER BY first_name, last_name) n,
       first_name, 
       last_name 
  FROM table1 

SQLFiddle

Answer 2:

这里是为SQL server, Oracle, PostgreSQL支持窗口的功能。

SELECT  ROW_NUMBER() OVER (ORDER BY first_name, last_name)  Sequence_no,
        first_name,
        last_name
FROM    tableName

• SQLFiddle演示

Answer 3:

在这种情况下，你有没有自然分区值，只是想一个有序的数字，无论你可以做一个ROW_NUMBER在一个恒定的分区，在下面的例子中，我只是用“X”。 希望这可以帮助别人

select 
    ROW_NUMBER() OVER(PARTITION BY num ORDER BY col1) as aliascol1, 
    period_next_id, period_name_long
from 
(
  select distinct col1, period_name_long, 'X' as num
  from {TABLE} 
) as x

Answer 4:

DECLARE @id INT 
SET @id = 0 
UPDATE cartemp
SET @id = CarmasterID = @id + 1 
GO

文章来源: How to generate auto increment field in select query