Question

SQL查询让一个字段自动编号

比如student 表
select * from student 如下：
name age
张三 23
李四 12
王五 25

但是我想用查询语句就变成这样，这样的语句怎写？

id name age
1 张三 23
2 李四 12
3 王五 25

Answer 1

DB2/ Oracle /SQL server 2005以上可以用这个
select row_number() over(order by name), name, age
from student

其他数据库很难实现

Answer 2

用plsql查，查完自动会增加id字段，刚好满足你的需求。
或者查完，存到excel中，用excel简单处理，几秒种就搞定。

Answer 3

有3种方法,
1.用case的方法
举例:select case when name='张三' then 1 case when '李四' then 2 case when '王五' then 3 end as id,name,age from student order by id
2.用union的方法
举例:select '1' as id,name,age from student where name='张三' union
select '2' as id,name,age from student where name='李四' union
select '3' as id,name,age from student where name='王五' order by id
3.ROW_NUMBER() over(order by 列名) 这种方法只有sql2005才行
举例:select ROW_NUMBER() over(order by name) as ID,name,age from student

Answer 4

select ROW_NUMBER() over(order by name) as ID,* from student

Answer 5

select t.rownum,t.name,t.age from table