将分隔列拆分为另一个表中的单独行的高效查询
我有一些数据,其中包括一个带分隔数据的列。基本上同一列中有多个记录:
A0434168.A2367943.A18456972.A0135374.A0080362.A0084546.A0100991.A0064071.A0100858
我正在尝试将此列中的数据拆分到另一个表中的单独行中。这些值的长度可变,并由句点分隔。我一直在尝试使用游标为该数据创建查找表。由于数据量太大,游标速度过慢。
我的光标如下所示:
DECLARE @ptr nvarchar(160)
DECLARE @aui nvarchar(15)
DECLARE @getmrhier3 CURSOR
SET @getmrhier3 = CURSOR FOR
SELECT cast(ptr as nvarchar(160)),aui
FROM mrhier3
FORWARD_ONLY
OPEN @getmrhier3
FETCH NEXT
FROM @getmrhier3 INTO @ptr, @aui
WHILE @@FETCH_STATUS = 0
BEGIN
if(len(@ptr) > 0)
begin
if(charindex('.',@ptr) > 0)
begin
insert into mrhierlookup(hieraui,aui)
values (substring(@ptr,0,charindex('.',@ptr)),@aui)
update mrhier3
set ptr = substring(@ptr,charindex('.',@ptr)+1,LEN(@ptr))
where aui = @aui
and ptr = @ptr
end
else
begin
insert into mrhierlookup(hieraui,aui)
values (@ptr,@aui)
update mrhier3
set ptr = ''
where aui = @aui
and ptr = @ptr
end
end
FETCH NEXT
FROM @getmrhier3 INTO @ptr, @aui
END
CLOSE @getmrhier3
DEALLOCATE @getmrhier3
当前版本的游标仅作用于列的前导值。所有的长度都是任意的。该列的长度最多为~150个字符。
使用当前数据集,构建查找表可能需要几天时间。它将有数百万条记录。
是否有更好的方法将此数据高效(快速)解析到单独的表中,以便更快地执行联接操作?
解决方案
创建拆分函数:
CREATE FUNCTION dbo.SplitStrings(@List NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN ( SELECT Item FROM
( SELECT Item = x.i.value('(./text())[1]', 'nvarchar(max)')
FROM ( SELECT [XML] = CONVERT(XML, '<i>'
+ REPLACE(@List, '.', '</i><i>') + '</i>').query('.')
) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
WHERE Item IS NOT NULL
);
GO
然后删除所有光标和循环的废话,并执行以下操作:
INSERT dbo.mrhierlookup
(
heiraui,
aui
)
SELECT s.Item, m.aui
FROM dbo.mrhier3 AS m
CROSS APPLY dbo.SplitStrings(m.ptr) AS s
GROUP BY s.Item, m.aui;
相关文章