Question

如何在数据库的查询结果中返回行号？

例如：

id num type
a 12 name
c 21 nate
d 25 adfa
f 25 ssdf
g 58 name
查询出type = name
返回 0 4
wenhongl 你的这种写法已经改变了原表的排序方式了啊 查询出的结果不是 0 4

Answer 1

Oracle或SQL Server2005以上的版本
row_number排序函数

SQL2000可用,变量赋值

--> --> (Roy)生成测试数据

declare @T table([id] nvarchar(1),[num] int,[type] nvarchar(10))
Insert @T
select N'a',12,N'name' union all
select N'c',21,N'nate' union all
select N'd',25,N'adfa' union all
select N'f',25,N'ssdf' union all
select N'g',58,N'name'

declare @type nvarchar(10),@i int,@s nvarchar(200)
set @type='Name'
select @i=-1,@s=''

Select @i=@i+1,@s=@s+case when type=@type then RTRIM(@i)+' ' else '' end from @T
print @s

(5 row(s) affected)
0 4

Answer 2

select * from
(select (row_number() OVER (ORDER BY (select 1) )-1 ) as rowid,* from tb)
a where type='name'

Answer 3

select rownum-1 from table;