一表中有这样的数据ID Text
18 aaaaa
21 bbbbb
22 ccccc
25 ddddd如何得到这样的效果NewID ID Text
1 18 aaaaa
2 21 bbbbb
3 22 ccccc
4 25 ddddd
18 aaaaa
21 bbbbb
22 ccccc
25 ddddd如何得到这样的效果NewID ID Text
1 18 aaaaa
2 21 bbbbb
3 22 ccccc
4 25 ddddd
18 aaaaa 1
21 bbbbb 2
22 ccccc 3
25 ddddd 3得到这样的效果 1 22 ccccc
2 25 ddddd
go
if object_id(N'tbl') is not null
begin
drop table tbl
end
go
create table tbl(ID int,[TEXT] varchar(10))
go
insert into tbl values ('18','aaaaa')
insert into tbl values ('21','bbbbb')
insert into tbl values ('22','ccccc')
insert into tbl values ('25','ddddd')
go
select ROW_NUMBER() OVER (ORDER BY ID ASC) as [NewID],* from tbl
go
use [CSDN]
go
drop table tbl
go
/*
NewID ID TEXT
-------------------- ----------- ----------
1 18 aaaaa
2 21 bbbbb
3 22 ccccc
4 25 ddddd
(4 row(s) affected)
*/
一般利用临时表的identity列,或用count计数法得到.select newid=(select count(*) from tb where id<=a.id and kind=a.kind),id,text from tb a
where exists(select 1 from tb where kind=a.kind and id!=a.id)
2000中没有ROW_BUMBER()函数,也没有类似的.
http://topic.csdn.net/u/20080329/19/36a6c827-2076-435a-b895-c30008f479c8.html