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

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

有下面一批数据

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