我有一些数据包括一个带有分隔数据的列.基本上在同一列中有多个记录：
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,charindex('.',@ptr)),@aui)
update mrhier3
set ptr = substring(@ptr,@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;