SQL Server 世界中的字符串聚合

引言

本文包含T-SQL示例，展示了如何通过使用其他结构更灵活有效地执行字符串连接。

背景

在实践中，将字符串连接成一个的任务非常常见。遗憾的是，T-SQL标准没有提供在聚合SUM函数中使用字符串数据的可能性。

Msg 8117, Level 16, State 1, Line 1 
Operand data type char is invalid for sum operator.

作为解决此类任务的方案，MySQL添加了GROUP_CONCAT函数，而Oracle则添加了LISTAGG函数。SQL Server 却没有这种内置功能。

然而，这不应被视为一个缺点，因为T-SQL的功能允许通过使用将在下面揭示的其他结构更灵活有效地执行字符串连接。

使用代码

假设我们需要使用以下表中的数据，将几个用逗号分隔的字符串连接成一个

IF OBJECT_ID('dbo.Chars', 'U') IS NOT NULL
    DROP TABLE dbo.Chars
GO
CREATE TABLE dbo.Chars ([Char] CHAR(1) PRIMARY KEY)
INSERT INTO dbo.Chars ([Char])
VALUES ('A'), ('B'), ('C'), ('F'), ('D')   

此任务最明显的解决方案是使用游标

DECLARE 
      @Chars VARCHAR(100)
    , @Char CHAR(1)
DECLARE cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
    SELECT [Char]
    FROM dbo.Chars
OPEN cur
FETCH NEXT FROM cur INTO @Char
WHILE @@FETCH_STATUS = 0 BEGIN
    SET @Chars = ISNULL(@Chars + ', ' + @Char, @Char) 
    FETCH NEXT FROM cur INTO @Char
END
CLOSE cur
DEALLOCATE cur
SELECT @Chars  

使用游标本身会导致查询执行效率降低，而且至少看起来不太优雅。

为了避免使用游标，可以通过变量赋值连接字符串

DECLARE @Chars VARCHAR(100)
SELECT @Chars = ISNULL(@Chars + ', ' +[Char], [Char])   
FROM dbo.Chars
SELECT @Chars   

一方面，该结构相当简单；另一方面，它在大量获取时的性能远不能令人满意。

因此，当需要在SELECT块内进行连接时，此方法将不可行。

在这种情况下，可以求助于XML功能，使用以下结构

SELECT Chars = STUFF((
	SELECT ', ' + [Char]
	FROM dbo.Chars
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '') 

如果我们查看在dbForge Studio for SQL Server中检索到的执行计划，我们可以注意到value方法的高查询成本

为了避免此操作，可以使用XQuery属性重写查询

SELECT Chars = STUFF(CAST((
	SELECT [text()] = ', ' + [Char]
	FROM dbo.Chars
	FOR XML PATH(''), TYPE) AS
VARCHAR(100)), 1, 2, '')   

结果，我们将获得一个非常简单且快速的执行计划

原则上，通过列进行字符串连接不会造成特别大的困难。

一个更有趣的情况是需要连接多个列的情况。例如，我们有下表

IF OBJECT_ID('dbo.EntityValues', 'U') IS NOT NULL
    DROP TABLE dbo.EntityValues
GO
CREATE TABLE dbo.EntityValues (
      EntityID INT
    , Value1 CHAR(1)
    , Value2 CHAR(1) 
)
CREATE NONCLUSTERED INDEX IX_WorkOut_EntityID
	ON dbo.EntityValues (EntityID)
GO 
INSERT INTO dbo.EntityValues (EntityID, Value1, Value2)
VALUES 
    (1, 'A', 'X'),
    (2, 'B', 'Y'),
    (2, 'C', 'Z'),
    (2, 'F', 'H'),
    (1, 'D', 'R')  

其中数据必须按以下方式分组

或者，我们可以复制XML调用，但随后我们会重新读取，这可能会严重影响查询执行效率

SELECT 
      ev.EntityID
    , Values1 = STUFF(CAST((
        SELECT [text()] = ', ' + ev2.Value1
        FROM dbo.EntityValues ev2
        WHERE ev2.EntityID = ev.EntityID
        FOR XML PATH(''), TYPE) AS VARCHAR(100)), 1, 2, '')
    , Values2 = STUFF(CAST((
        SELECT [text()] = ', ' + ev2.Value2
        FROM dbo.EntityValues ev2
        WHERE ev2.EntityID = ev.EntityID
        FOR XML PATH(''), TYPE) AS VARCHAR(100)), 1, 2, '')             
FROM ( 
    SELECT DISTINCT EntityID
    FROM dbo.EntityValues
) ev 

如果查看执行计划，很容易就能相信这一点

为了减少重新读取，可以使用一个小的 XML hack

SELECT
      ev.EntityID
    , Values1 = STUFF(
        REPLACE(
            CAST([XML].query('for $a in /a return xs:string($a)') 
        AS VARCHAR(100)), ' ,', ','), 1, 1, '') 
    , Values2 = STUFF(
        REPLACE(
            CAST([XML].query('for $b in /b return xs:string($b)') 
        AS VARCHAR(100)), ' ,', ','), 1, 1, '') 
FROM (
    SELECT DISTINCT EntityID
    FROM dbo.EntityValues
) ev
CROSS APPLY (
    SELECT [XML] = CAST((
        SELECT 
              [a] = ', ' + ev2.Value1
            , [b] = ', ' + ev2.Value2
        FROM dbo.EntityValues ev2
        WHERE ev2.EntityID = ev.EntityID
        FOR XML PATH('')
    ) AS XML)
) t  

但由于多次调用query方法，此查询也不会是最佳的。

另一种方法 - 使用游标

IF
OBJECT_ID('tempdb.dbo.#EntityValues') IS NOT NULL
	DROP TABLE #EntityValues
GO
SELECT DISTINCT
	  EntityID
	, Values1 = CAST(NULL AS VARCHAR(100))
	, Values2 = CAST(NULL AS VARCHAR(100))
INTO #EntityValues
FROM dbo.EntityValues
DECLARE
	  @EntityID INT
	, @Value1 CHAR(1)
	, @Value2 CHAR(1)
DECLARE cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
    SELECT
	      EntityID
	    , Value1
	    , Value2
    FROM dbo.EntityValues
OPEN cur
FETCH NEXT FROM cur INTO
	  @EntityID
	, @Value1
	, @Value2
WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE #EntityValues
    SET 
          Values1 = ISNULL(Values1 + ', ' + @Value1, @Value1) 
        , Values2 = ISNULL(Values2 + ', ' + @Value2, @Value2)
    WHERE EntityID = @EntityID
FETCH NEXT FROM cur INTO
          @EntityID
        , @Value1
        , @Value2
END
CLOSE cur
DEALLOCATE cur
SELECT *
FROM #EntityValues 

然而，正如实践表明的那样，在使用大型ETL包时，最有效的解决方案是在UPDATE语句中赋值变量的能力

IF
OBJECT_ID('tempdb.dbo.#EntityValues') IS NOT NULL
	DROP TABLE #EntityValues
GO
DECLARE
      @Values1 VARCHAR(100)
    , @Values2 VARCHAR(100)
SELECT
      EntityID
    , Value1
    , Value2
    , RowNum = ROW_NUMBER() OVER (PARTITION BY EntityID ORDER BY 1/0)
    , Values1 = CAST(NULL AS VARCHAR(100))
    , Values2 = CAST(NULL AS VARCHAR(100))
INTO #EntityValues
FROM dbo.EntityValues
UPDATE #EntityValues
SET 
      @Values1 = Values1 =
        CASE WHEN RowNum = 1 
            THEN Value1
            ELSE @Values1 + ', ' + Value1 
        END
    , @Values2 = Values2 = 
        CASE WHEN RowNum = 1 
            THEN Value2
            ELSE @Values2 + ', ' + Value2 
        END
SELECT
      EntityID
    , Values1 = MAX(Values1) 
    , Values2 = MAX(Values2)
FROM #EntityValues
GROUP BY EntityID 

SQL Server没有内置的替代GROUP_CONCAT和LISTAGG函数的方案。尽管如此，这并不会妨碍有效地执行字符串连接任务。本文的目的是清楚地展示这一点。