时间戳

首页 > 解决方案 > 为大量行拆分逗号分隔字段

sql-server - 为大量行拆分逗号分隔字段

问题描述

我正在尝试将数据从一个模式迁移到另一个模式，并且在拆分逗号分隔字段时遇到了一些问题。

S1：源数据库

S2：目标数据库

S1 有一个名为ownerUserIDList的字段，它链接到S2 中的AMID，问题是 S2 在该字段中只允许 1 个值，而 S1 可以有多个并且在数据库中以逗号分隔，所以我的计划是拆分字段并使用拆分的最后一个元素。

问题是它花费的时间太长并且不断超时。该表有大约 600k 行。我尝试了一些我在 SO 上找到的方法，但对于大型数据集来说，似乎没有一个方法那么快。

下面所有的例子都会被做成一个更新语句，用于实际的数据迁移，这些只是为了比较运行时间。

方法一：

SELECT TOP 100000 CASE 
        WHEN ownerUserIDList LIKE '%,%' THEN SUBSTRING( ownerUserIDList , LEN(ownerUserIDList) -  CHARINDEX(',',REVERSE(ownerUserIDList)) + 2  , LEN(ownerUserIDList)  )
        ELSE ownerUserIDList
    END
FROM S1.UserTable WHERE ownerUserIDList != '' AND ownerUserIDList IS NOT NULL

SQL Server 执行时间：CPU 时间 = 2359 毫秒，经过时间 = 728479 毫秒。

方法2：

SELECT TOP 100000 value 
FROM S1.UserTable
    CROSS APPLY STRING_SPLIT(ownerUserIDList, ',') 
    WHERE ownerUserIDList != '' AND ownerUserIDList IS NOT NULL
GO

SQL Server 执行时间：CPU 时间 = 1719 毫秒，经过时间 = 399817 毫秒。

考虑到我将不得不在多个更大的表上运行类似的查询，这两者都非常慢。有没有其他方法可以更快地拆分这些字段？

表结构非常简单：

S1：

UserID    ownerUserIDList   S2_AMID
---------------------------------------
1           20              1
2           20, 21, 23      4
3           21, 22, 23      4

S2：

AMID    S1_UserID
-----------------
1       20
2       21
3       22
4       23

标签： sql-servertsql

解决方案

如果你真的只想要最后一个条目，试试这个。您可以添加子句以避免空列表或单个条目列表。

declare @t table(UserID int, OwnerID varchar(50))
insert @t values (1,'1,2,3'),(2,'3,4,5'),(3,'6,7,8')
select *, left(reverse(ownerID),CHARINDEX(',',reverse(ownerID))-1) from @t

这非常接近您的第一个选择，但我怀疑您会更接近。也许是一个 CLI 函数？我会把它留给其他人来编码。

推荐阅读