如何在选择查询中生成自动增量字段

例如，我有一个具有两列的表，first_name和 last_name具有这些值

Ali           Khani
Elizabette    Amini
Britney       Spears
,...

我想编写一个 select查询，生成如下表:

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

谢谢你的帮助。

328941 次浏览

小开

最佳答案

If it is MySql you can try

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

SQLFiddle

And for SQLServer

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

SQLFiddle

小开

here's for SQL server, Oracle, PostgreSQL which support window functions.

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

SQLFiddle Demo

小开

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

小开

In the case you have no natural partition value and just want an ordered number regardless of the partition you can just do a row_number over a constant, in the following example i've just used 'X'. Hope this helps someone

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