为表添加排序字段 （动态SQL、游标的使用）

背景：一个表中的数据有几万条，现要按类型（某一个字段的有限值，如东、南、西、北四个区域等）增加一个排序字段。

         假设表有一个主键标识列，可以初始化一个排序列为idx，可以这样做

         update 表名 set idx=id    --这样可以一步到位，按标识列初始化排序列。

 

要求：现在要求是这样的，将数据type='东'|'南'|'西'|'北'四个区域分别排序，按1,2,3,4,...的顺序排下去？

 

场景：现在有这样的一张表 eric

create table eric ( id int not null primary key identity(1,1), name varchar(50), sex varchar(50), age int, birthday datetime default getdate() );

 

数据是这样的：

 

insert into eric(name,sex,age) values('张三','男',18); insert into eric(name,sex,age) values('李四','女',16); insert into eric(name,sex,age) values('张三','男',18); insert into eric(name,sex,age) values('李四','女',16); insert into eric(name,sex,age) values('李民','女',15); insert into eric(name,sex,age) values('张三','男',18); insert into eric(name,sex,age) values('李四','女',16); insert into eric(name,sex,age) values('张三','男',18); insert into eric(name,sex,age) values('李四','女',16); insert into eric(name,sex,age) values('张三','男',18); insert into eric(name,sex,age) values('李民','男',22); insert into eric(name,sex,age) values('李四','女',16); insert into eric(name,sex,age) values('李四','女',16); insert into eric(name,sex,age) values('王五','男',20); insert into eric(name,sex,age) values('小红','女',19); insert into eric(name,sex,age) values('张强','男',22); insert into eric(name,sex,age) values('李民','男',28); insert into eric(name,sex,age) values('王瑶','女',22);

 

那么要求姓名分类添加排序字段怎么初始化呢？

 

我们可以先添加排序字段：

alter table eric add idx int

 

再定义一个通用的存储过程完成这件事：

 

/******************************************************* 作用：根据游标在查询返回的记录集中移动，为返回的记录添加索引 相当于oracle中查询语句返回的rownum 要求：表中的主键是id，增加的索引列名为idx 参数： @param : sql sql语句如果是简单的查询语句，如select * from table 则表示为该表加索引从1开始，依次增加 如果是带条件的，则为指定的记录加索引 @param : table_name *******************************************************/ create procedure pro_test @sql varchar(5000),@table_name varchar(30) as declare @id int,@idx int,@cursor_declare varchar(5000) set @cursor_declare = 'declare cur_test cursor for '+@sql set @idx=1 exec(@cursor_declare) print '游标声明：'+@cursor_declare print '表名传参：'+@table_name open cur_test fetch next from cur_test into @id while @@fetch_status=0 begin exec('update '+@table_name+' set idx='+@idx+' where id='+@id) fetch next from cur_test into @id set @idx = @idx+1 end deallocate cur_test go

 

最后调用存储过程：

 

exec pro_test 'select id from eric where name=''王五''','eric' --为所有王五编号 exec pro_test 'select id from eric where name=''张三''','eric' --为所有张三编号 exec pro_test 'select id from eric where name=''李四''','eric' --为所有李四编号

 

最后我们可以查询看看：

select * from eric where name='张三' select * from eric where name='李四' select * from eric where name='王五' select * from eric