SQL Server存储过程,解决数据拆分问题

转载  郑建华-华仔部落   2020-05-20   239人阅读  1 条评论

    今天在解决BUG的过程中,碰到了一个问题。由于程序原因,导致某个数据表中的部分历史数据,合并到了一条记录中。

image.png

    如上图,id为1的数据,report_id为20011415414786,content显示为2=3=4=5。而正常的数据,应该是如图红框部分,相同report_id,不同content值对应多条数据。并且错误数据较多,不是一条两条,而是几十条数据。最主要的问题是,像这种类似的错误,很有可能在其他正式环境还会存在。如果仅仅只是,手动写insert语句,一条一条做添加,那就太繁琐了,大量的无用功。使用sql能解决这个问题么?好像是不可以,这种带逻辑的处理,怕是只能使用存储过程了。

    在写存储过程之前,先将表结构和数据展示处理,用作演示使用。

CREATE TABLE [dbo].[form_cause] (
  [id] int  IDENTITY(1,1) NOT NULL,
  [content] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [otherValue] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [report_id] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  CONSTRAINT [PK__form_cau__3213E83F24134F1B] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)  
ON [PRIMARY]
GO

ALTER TABLE [dbo].[form_cause] SET (LOCK_ESCALATION = TABLE)
GO

INSERT INTO [dbo].[form_cause]([content], [otherValue], [report_id]) VALUES ('2=3=4=5', NULL, '20011415414786');
INSERT INTO [dbo].[form_cause]([content], [otherValue], [report_id]) VALUES ('5=12=18', NULL, '20011418003562');
INSERT INTO [dbo].[form_cause]([content], [otherValue], [report_id]) VALUES ('5', NULL, '20032612043418');
INSERT INTO [dbo].[form_cause]([content], [otherValue], [report_id]) VALUES ('6', NULL, '20032612043418');
INSERT INTO [dbo].[form_cause]([content], [otherValue], [report_id]) VALUES ('8', NULL, '20032612043418');

接下来是我编写的存储过程,其中使用到了网上找的一个函数,另外在存储过程中,添加了注释内容,便于理解。(应该有更优的写法,欢迎指出)

--创建查询字符串下标函数 取字符串中,某个字符,出现第几次的下标值
create function fn_find(@find varchar(8000), @str varchar(8000), @n smallint)
    returns int
as
begin
    if @n < 1 return (0)
    declare @start smallint, @count smallint, @index smallint, @len smallint
    set @index = charindex(@find, @str)
    if @index = 0 return (0)
    else select @count = 1, @len = len(@find)
    while @index > 0 and @count < @n
        begin
            set @start = @index + @len
            select @index = charindex(@find, @str, @start), @count = @count + 1
        end
    if @count < @n set @index = 0
    return (@index)
end 
go


--创建拆分表数据存储过程
CREATE PROCEDURE [dbo].[splitFormCause]
	
	AS
	BEGIN
		Declare @temp varchar(50);-- 游标的临时变量
		Declare @tempCount int;-- content字段中匹配=的个数
		Declare @contentStr varchar(50);-- content字段的完整值
		Declare @tempContent varchar(50);-- content字段拆分后的单个值(不包括最后一个值)
		Declare @lastTempContent varchar(50);-- cotent字段拆分后的最后一个值
		Declare @dataCount int;--对应游标的数据量
		Declare @i int;--内层循环使用的下标
		Declare report_id_cursor cursor --定义一个游标
		for( select report_id from form_cause where content like '%=%') --取form_cause表中content字段包含=的全部数据,以report_id的值作为游标值
		open report_id_cursor --打开游标
		--获取游标值到 临时变量中
		fetch next from  report_id_cursor into  @temp;
		--开始循环
		while @@FETCH_STATUS=0
			begin 
				set @tempCount=0 --初始化数量
				set @i=1 --初始化内层循环下标为1
				--获取对应游标的数据量
				select  @dataCount = (select count(1) from form_cause where report_id=@temp)
				--判断对应游标数据量大于1,则不进行后续操作
				if(@dataCount>1)
					begin 
						--获取下一个游标值,并且跳出循环
						fetch next from  report_id_cursor into  @temp
						continue;
					end
				--计算当前游标数据的 content值中=的个数
				select @tempCount= (select len(content)-len(replace(content, '=', ''))  from form_cause where report_id=@temp)
				--给当前游标数据的content赋值到变量中
				select @contentStr=( select content from form_cause where report_id=@temp)
					--以==个数,开始遍历数据
					while @i<=@tempCount
						begin 
							--截取当前下标下的拆分数据,将其赋值给变量 (通过fn_find函数+substring函数,获取当前拆分的数据)
							select @tempContent=substring(@contentStr,dbo.fn_find('=',@contentStr,@i-1)+1,(dbo.fn_find('=',@contentStr,@i)-dbo.fn_find('=',@contentStr,@i-1)-1))
							--执行插入语句,根据拆分遍历以及游标值
							insert into form_cause (content,report_id) values(@tempContent,@temp)
							--内存循环下标自增
							set @i=@i+1
					   end
					   --处理最后一条数据的content值 
					select @lastTempContent=(
						select substring(@contentStr,dbo.fn_find('=',@contentStr,@tempCount)+1,len(@contentStr)-dbo.fn_find('=',@contentStr,@tempCount))
					)
					--插入最后一条数据
					insert into form_cause (content,report_id) values(@lastTempContent,@temp)
					--结束当前循环,并获取下一个游标
					fetch next from  report_id_cursor into  @temp;
			end	
			close report_id_cursor --关闭游标
			deallocate report_id_cursor --释放游标
END
go

创建函数和存储过程后,运行该存储过程

image.png

再次查看表数据,已经拆分完毕。为了保险起见,在存储过程中,未将原需要拆分的数据删除,可在执行存储过程之后,确认数据正常后,再批量将原数据删除。

image.png

    本文地址:https://www.lee2333.com/?id=31
    温馨提示:文章内容系作者个人观点,不代表微微一笑抽了筋对观点赞同或支持。
    版权声明:本文为转载文章,来源于 郑建华-华仔部落 ,版权归原作者所有,欢迎分享本文,转载请保留出处!

    发表评论


    表情

    评论列表

    1. 微微一笑抽了筋
      微微一笑抽了筋  @回复

      嘿嘿