drop table ar1
drop table ar2
/*建表 ar1*/
CREATE TABLE [dbo].[ar1] (
[name1] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[id1] [int]
) ON [PRIMARY]
GO
/*插入数据*/
INSERT INTO ar1(name1,id1) VALUES('ccc',0)
INSERT INTO ar1(name1,id1) VALUES('aaa',0)
INSERT INTO ar1(name1,id1) VALUES('fff',0)
INSERT INTO ar1(name1,id1) VALUES('aaa',0)
INSERT INTO ar1(name1,id1) VALUES('aaa',0)
INSERT INTO ar1(name1,id1) VALUES('bbb',0)
INSERT INTO ar1(name1,id1) VALUES('eee',0)
INSERT INTO ar1(name1,id1) VALUES('fff',0)
INSERT INTO ar1(name1,id1) VALUES('ccc',0)
INSERT INTO ar1(name1,id1) VALUES('bbb',0)
INSERT INTO ar1(name1,id1) VALUES('ccc',0)
INSERT INTO ar1(name1,id1) VALUES('aaa',0)
/*select * from ar1*/
/*排序输出*/
select name1,id1 into ar2 from ar1 order by name1
/*select * from ar2*/
/*声明变量*/
declare @nnnn1 varchar(32),@nnnn2 varchar(32),@ok1 int,@ok2 int
/*声明游标*/
declare rencursor for select name1,id1 from ar2
/*打开游标*/
open ren
/*提取第一行的数据*/
fetch next from ren
into @nnnn1,@ok1
update ar2 set id1=1 where current of ren
set @ok1=1
print @nnnn1
print @ok1
while @@fetch_status=0
begin
fetch next from ren
into @nnnn2,@ok2
if @@fetch_status=0
begin
if @nnnn1=@nnnn2
begin
set @ok1=@ok1+1
update ar2 set id1=@ok1 where current of ren
end
else
begin
update ar2 set id1=1 where current of ren
set @ok1=1
set @nnnn1=@nnnn2
end
end
end
/*关闭释放游标*/
close ren
deallocate ren
用游标实现功能
能否有其他不用游标好方法! |
