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

How to generate auto increment field in select query(如何在选择查询中生成自增字段)

问题描述

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

For example I have a table with 2 columns, first_name and last_name with these values

Ali           Khani
Elizabette    Amini
Britney       Spears
,...

我想编写一个 select 查询来生成这样的表:

I want to write a select query that generate a table like this:

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

感谢您的帮助.

推荐答案

如果是MySql可以试试

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

对于 SQLServer

And for SQLServer

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

SQLFiddle

这篇关于如何在选择查询中生成自增字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！