对于重复的编号重新编号的方法

对于重复的编号重新编号的方法

有下面一批数据

4006620B-AE28-4673-B001-51878A062F2E HRH1080701058 F48FBE75-F7A1-41C3-A770-8828414E0484 HRH1080701058 9F251086-18EC-4453-A186-D751931609BD HRH1080701058 A4E44D78-63D1-4563-9D06-34C8D88B9C37 HRH1080701062 86633D0F-B844-453A-8D5B-8E45F4FC9077 HRH1080701062 25EA0FE7-A0B5-46F4-9EEF-9B4CFDB96EEF HRH1080701062 479D0F73-EAD9-4221-9892-C6CE694382F4 HRH1080701062 0A3F1AB9-1203-496D-B5FF-37881ADEB0FD HRH1080704943 83288CF3-207F-4B37-83F9-747C9CB4FB8A HRH1080704943 69724699-4B78-4688-9454-5C3E2C822439 HRH1080704945 48F189F1-17B4-4BE3-AC3C-ABE7545D52A5 HRH1080704945 90A2C4B4-8EBA-4464-914D-9E5198BAC73E HRH1080704950 4C1DA519-2C89-4592-8D46-E5B5808EC7DC HRH1080704950 FD1B5827-9795-4036-BCB4-49CC0FD6D116 HRH1080705010 A101A4F3-820A-4C6E-AAA9-A951BE4C0D5C HRH1080705010 4A59D938-3315-4FCE-BDFF-DF7C3EC20662 HRH1090701467 460CA8A4-D988-4208-93B6-FB4E6EBAE8D8 HRH1090701467

第一列是主键，第二列是一个编号，但这个编号重复了，下面写了一个存储过程，把重复的编号给重新编号

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go       -- ============================================= -- Author:  qijianzhou -- Create date: 2010-01-26 -- Description: 修改重复 reg_no 号 -- ============================================= ALTER PROCEDURE [CENTURY21CN\qijianzhou].[updateSale_normal_Reg_no]     AS BEGIN  --1 创建临时表  SELECT  reg_no  into #t FROM u_sale_normal WHERE [deleted] = 0 AND reg_no <> ''  GROUP BY reg_no HAVING COUNT(1) > 1 order by reg_no     -- 临时表，保存对应的关系     create table #u_sale_normal  (   id UNIQUEIDENTIFIER,         old_reg_no        varchar(13),         new_reg_no        varchar(13),         house_type        varchar(30),         input_Date        datetime  )  --2 定义变量  declare @sale_house_id UNIQUEIDENTIFIER  declare @reg_no     varchar(13)  declare @tmp_reg_no varchar(13)  declare @i_count    int -- 用于计数  declare @i_reg_no_count int -- 生成 reg_no用       declare @newReg_no varchar(13)     declare @c_reg_no_count varchar(13)     set @tmp_reg_no = '';     select sale_house_id,#t.reg_no into #t2 from #t,u_sale_normal     where #t.reg_no = u_sale_normal.reg_no order by #t.reg_no  --3 游标循环临时表  declare index_cursor cursor for  select * from #t2 order by reg_no  open index_cursor     set @i_reg_no_count = 0  fetch next from index_cursor into @sale_house_id,@reg_no  while @@fetch_status=0  begin    --print convert(varchar(100),@sale_house_id) + '   '+@reg_no    if (@reg_no = @tmp_reg_no)       begin         set @i_reg_no_count=@i_reg_no_count+1         set @c_reg_no_count=convert(varchar(13),@i_reg_no_count)         set @newReg_no = substring(@reg_no,1,6)+'89'+replicate('0',5-len(@c_reg_no_count))+@c_reg_no_count         insert #u_sale_normal(id,old_reg_no,new_reg_no,input_Date,house_type)         values(@sale_house_id,@reg_no,@newreg_no,getdate(),'u_sale_normal')       end else       begin                 set @tmp_reg_no = @reg_no       end    fetch next from index_cursor into @sale_house_id,@reg_no  end  close index_cursor  deallocate index_cursor     --update #u_sale_normal set #u_sale_normal.house_addr = dbo.f_crypt_decrypt_str(a.house_addr,1) from u_sale_normal as a     --where a.sale_house_id=#u_sale_normal.sale_house_id     delete u_Reg_no_replace where house_type='u_sale_normal'  insert u_Reg_no_replace select * from #u_sale_normal     -- 更新实际表     -- update u_sale_normal set reg_no= #u_sale_normal.new_reg_no from #u_sale_normal     -- where #u_sale_normal.sale_house_id = u_sale_normal.sale_house_id     --drop table u_sale_normal_replace     select *  from #u_sale_normal order by old_reg_no END

通过这个复习了一个游标的使用，同时发现操作临时表比直接操作物理表要快，在用游标操作 u_sale_normal 时，速度很慢，后来改成操作临时表，5K多条数据，用 3秒钟操作完

 

 

---

安平2009@原创
qi_jianzhou@126.com

转载于:https://www.cnblogs.com/zziss/archive/2010/01/26/1656691.html