多列连接

我使用超过 6 百万行进行了一些测试。在 ID 列上有一个索引。

这是我想出的。

您的初始查询：

SELECT * FROM (
    SELECT t.id,
            stuff([M].query('/name').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined1],
            stuff([M].query('/car').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined2]
    FROM dbo.test t
    OUTER APPLY(SELECT (
                    SELECT id, ','+name AS name
                    ,','+car AS car
                    FROM test WHERE test.id=t.id
                    FOR XML PATH('') ,type)
                 AS  M) 
            M ) S
GROUP BY id, SomeField_Combined1, SomeField_Combined2 

这个跑了~23分钟。

我运行了这个版本，这是我第一次学习的版本。在某些方面，它似乎需要更长的时间，但事实并非如此。

SELECT test.id,
    STUFF((SELECT ', ' + ThisTable.name
            FROM   test ThisTable
            WHERE  test.id = ThisTable.id
            AND    ThisTable.name <> ''
            FOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField,
    STUFF((SELECT ', ' + car
            FROM   test ThisTable
            WHERE  test.id = ThisTable.id
            AND    ThisTable.car <> ''
            FOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField2
FROM test 
GROUP BY id

这个版本只用了 2 多分钟。

CLR 聚合几乎肯定是最快的方法。但是也许您出于某种原因不想使用一个...

你说这个来源是一个昂贵的查询。

我会#temp首先将其具体化为一个表格，以确保它只被评估一次。

CREATE TABLE #test
(
ID INT,
name NVARCHAR(128),
car  NVARCHAR(128)
);

CREATE CLUSTERED INDEX ix ON #test(ID);

我为问题中的查询获得的执行计划首先对外部查询中的每一行进行连接，然后删除重复项id, SomeField_Combined1, SomeField_Combined2。

这是非常浪费的。下面的重写避免了这种情况。

SELECT t.id,
       stuff([M].query('/name').value('/', 'varchar(max)'), 1, 1, '') AS [SomeField_Combined1],
       stuff([M].query('/car').value('/', 'varchar(max)'), 1, 1, '')  AS [SomeField_Combined2]
FROM   (SELECT DISTINCT id
        FROM   #test) t
       OUTER APPLY(SELECT (SELECT id,
                                  ',' + name AS name,
                                  ',' + car  AS car
                           FROM   #test
                           WHERE  #test.id = t.id
                           FOR XML PATH(''), type) AS M) M 

但是对于以下测试数据（对我来说，每个 id 有 1000 个 id，每个 id 有 2156 行）

INSERT INTO #test
SELECT v.number, o.name, o.type_desc
FROM   sys.all_objects o
       INNER JOIN master..spt_values v
         ON v.type = 'P' AND v.number BETWEEN 1 AND 1000 

我仍然发现 Kenneth 的解决方案有两个XML PATH调用，速度更快，资源消耗更少。

+-----------------+--------------------+------------------------+------------------+---------------------+-------------------------+-----------------------------+
|                 | CPU Time (Seconds) | Elapsed Time (Seconds) | #test Scan Count | #test Logical Reads | Worktable logical reads | Worktable lob logical reads |
+-----------------+--------------------+------------------------+------------------+---------------------+-------------------------+-----------------------------+
| Single XML PATH | 51.077             | 15.521                 | 1,005            | 60,165              | 51,161                  | 1,329,207                   |
| Double XML PATH | 3.1720             | 3.010                  | 2,005            | 92,088              | 14,951                  |   233,681                   |
+-----------------+--------------------+------------------------+------------------+---------------------+-------------------------+-----------------------------+

对于其中的每个不同id，#test它执行两个操作而不是一个，但此操作比构造 XML 然后重新解析它要便宜得多。

正如 Martin Smith 已经指出的那样，CLR 聚合可能是您最好的选择。同样，将结果存储在临时表中是个好主意。

这是使用 UNPIVOT/PIVOT 的另一种可能的 T-SQL 实现：

IF OBJECT_ID('tempdb..#test') IS NOT NULL 
    DROP TABLE #test;

CREATE TABLE #test (
    id int,
    name varchar(128),
    car varchar(128)
)

CREATE CLUSTERED INDEX ix ON #test(ID);

INSERT INTO #test
SELECT v.number, o.name, o.type_desc
FROM   sys.all_objects o
       INNER JOIN master..spt_values v
         ON v.type = 'P' AND v.number BETWEEN 1 AND 1000 

;WITH info(col) AS (
    SELECT 'car'
    UNION ALL
    SELECT 'name'
)
SELECT *
FROM info
CROSS JOIN (
    SELECT DISTINCT id
    FROM #test
) AS ids
CROSS APPLY (
    SELECT v = STUFF((
        SELECT ',' + value AS [text()]
        FROM #test
        UNPIVOT (value FOR col IN (name,car)) AS u
        WHERE col = info.col
            AND id = ids.id
            AND value <> ''
        FOR XML PATH(''), type
    ).value('.','varchar(max)'),1,1,SPACE(0))
) AS ca(val)
PIVOT (MIN(val) FOR col IN (car,name)) AS p;

它的运行时间与 Kenneth 的解决方案大致相同。

尝试这个

使用Right函数删除前导逗号而不是xml函数

使用case语句来避免空格的逗号

SELECT t.id, 
       RIGHT(A.NAME, Len(A.NAME) - 1) AS NAME, 
       RIGHT(A.car, Len(A.car) - 1)   AS car 
FROM   dbo.test t 
       OUTER apply(SELECT (SELECT id, 
                                  CASE WHEN NAME<>'' THEN ',' ELSE '' END + NAME  AS NAME, 
                                  CASE WHEN car<>'' THEN ',' ELSE '' END + car AS car 
                           FROM   test 
                           WHERE  test.id = t.id 
                           FOR xml path(''), type) AS M) A 
GROUP  BY id, 
          RIGHT(A.NAME, Len(A.NAME) - 1), 
          RIGHT(A.car, Len(A.car) - 1) 

注意：这里Group by也可以替换distinct为，因为我们没有使用任何aggregate函数