SQL Server 2000：执行串联聚合子查询的想法

Question

我有一个返回我想要的行的查询，例如

QuestionID  QuestionTitle  UpVotes  DownVotes  
==========  =============  =======  =========  
2142075     Win32: Cre...        0          0  
2232727     Win32: How...        2          0  
1870139     Wondows Ae...       12          0  

现在我想要返回一个列，其中包含逗号分隔的“作者”列表（例如原始海报和编辑）。例如：

QuestionID  QuestionTitle  UpVotes  DownVotes  Authors
==========  =============  =======  =========  ==========
2142075     Win32: Cre...        0          0  Ian Boyd  
2232727     Win32: How...        2          0  Ian Boyd, roygbiv
1870139     Wondows Ae...       12          0  Ian Boyd, Aaron Klotz, Jason Diller, danbystrom

伪造

SQL Server 2000 没有 CONCAT(AuthorName, ', ') 聚合操作，我一直在伪造它 - 对 TOP 执行简单的子选择1 作者，以及作者计数。

QuestionID  QuestionTitle  UpVotes  DownVotes  FirstAuthor  AuthorCount  
==========  =============  =======  =========  ===========  =========== 
2142075     Win32: Cre...        0          0  Ian Boyd               1 
2232727     Win32: How...        2          0  Ian Boyd               2
1870139     Wondows Ae...       12          0  Ian Boyd               3

如果有多个作者，那么我会向用户显示一个省略号（“...”），以表明有多个作者。例如，用户会看到：

QuestionID  QuestionTitle  UpVotes  DownVotes  Authors
==========  =============  =======  =========  ==========
2142075     Win32: Cre...        0          0  Ian Boyd
2232727     Win32: How...        2          0  Ian Boyd, …
1870139     Wondows Ae...       12          0  Ian Boyd, …

这效果很好，因为通常问题不会被编辑 - 这意味着我完美支持 99% 的情况，而 1% 的情况只有一半 -assed 也是如此。

线程重新查询

作为一个更复杂且容易出现错误的解决方案，我正在考虑迭代显示的列表，并为列表中的每个“问题”启动一个线程池工作线程。 list，对数据库执行查询以获取作者列表，然后在内存中聚合该列表。这意味着该列表首先填充（本机）应用程序。然后我会发出几千个单独的查询。

但这将是可怕的、可怕的、可怕的、缓慢的。更不用说漏洞百出，因为这将是线程工作。

是啊是啊

亚当机械师说得很清楚 ：

不要将行连接成分隔符 SQL Server 中的字符串。做客户端 侧面。

告诉我怎么做，我会做的。

/cry

谁能想到一个更好的解决方案，比我原来的“TOP 1 加省略号”解决方案一样快（比如说......在一个数量级内）？

例如，是否有一种方法可以返回结果集，其中到达行具有关联的结果集？因此，对于每个“主”行，我可以获得包含列表的“详细”结果集。

最佳答案代码

Cade的 链接到 Adam Machanic 的解决方案 我喜欢最好的。一个用户定义的函数，似乎通过魔法进行操作：

CREATE FUNCTION dbo.ConcatAuthors(@QuestionID int)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @Output VARCHAR(8000)
    SET @Output = ''

    SELECT @Output =    CASE @Output 
                WHEN '' THEN AuthorName 
                ELSE @Output + ', ' + AuthorName 
                END
    FROM  (
        SELECT QuestionID, AuthorName, QuestionDate AS AuthorDate FROM Questions
        UNION
        SELECT QuestionID, EditorName, EditDate FROM QuestionEdits
    ) dt
    WHERE dt.QuestionID = @QuestionID
    ORDER BY AuthorDate

    RETURN @Output
END

使用 T-SQL 用法：

SELECT QuestionID, QuestionTitle, UpVotes, DownVotes, dbo.ConcatAuthors(AuthorID)
FROM Questions

Answer 1

看看这些文章：

http://dataeducation.com/ rowset-string-concatenation-which-method-is-best/

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ (请参阅响应中 Phil Factor 的交叉连接解决方​​案 - 该解决方案将在 SQL Server 2000 中工作）

显然，在 SQL Server 2005 中，FOR XML 技巧是最简单、最灵活且通常性能最高的。

至于为每行返回一个行集，如果由于某种原因您仍然想这样做，您可以在存储过程中执行此操作，但客户端将需要消耗第一个行集中的所有行，然后转到下一个行集并将其与第一个行集中的第一行关联起来，等等。您的 SP 需要在作为第一个行集返回的同一集上打开一个游标，并按顺序运行多个选择以生成所有子行集。这是我已经完成的一项技术，但仅限于实际需要所有数据的情况（例如，在完全填充的树视图中）。

不管人们怎么说，在客户端执行此操作通常会浪费大量带宽，因为返回所有行并在客户端执行循环和中断意味着在开始时会传输大量相同的列。每行只是为了获取行末尾的变化列。

无论您在何处执行此操作，都应该根据您的用例做出明智的决定。

Answer 2

我尝试了 3 种方法来解决此问题，即此处发布的一种方法：activex 脚本和 UDF 函数。

对我来说最有效的脚本（速度方面）是奇怪的 Axtive-X 脚本，它运行多个查询来获取要连接的附加数据。

UDF 平均需要 22 分钟来转换，子查询方法（在此处发布）大约需要 5 分钟，而 activeX 脚本需要 4 分 30 分钟，这让我很恼火，因为这是我希望放弃的脚本。我得看看是否可以在其他地方提高效率。

我认为额外的 30 秒被用于存储数据的 tempdb 使用，因为我的脚本需要 order by。

应该指出的是，我正在连接大量的文本数据。

Answer 3

您还可以查看此脚本。这基本上就是 Cade Roux 在他的帖子中提到的交叉连接方法。

上面的方法看起来非常干净：您必须首先创建一个视图，然后根据视图中的值创建一个语句。您可以在代码中动态构建第二个 sql 语句，因此它应该可以直接使用。

Answer 4

我不确定这是否适用于 SQL Server 2000，但您可以尝试一下：

--combine parent and child, children are CSV onto parent row
CREATE TABLE #TableA (RowID int, Value1 varchar(5), Value2 varchar(5))
INSERT INTO #TableA VALUES (1,'aaaaa','A')
INSERT INTO #TableA VALUES (2,'bbbbb','B')
INSERT INTO #TableA VALUES (3,'ccccc','C')

CREATE TABLE #TableB (RowID int, TypeOf varchar(10))
INSERT INTO #TableB VALUES (1,'wood')
INSERT INTO #TableB VALUES (2,'wood')
INSERT INTO #TableB VALUES (2,'steel')
INSERT INTO #TableB VALUES (2,'rock')
INSERT INTO #TableB VALUES (3,'plastic')
INSERT INTO #TableB VALUES (3,'paper')


SELECT
    a.*,dt.CombinedValue
    FROM #TableA        a
        LEFT OUTER JOIN (SELECT
                             c1.RowID
                                 ,STUFF(
                                          (SELECT
                                               ', ' + TypeOf
                                               FROM (SELECT
                                                         a.RowID,a.Value1,a.Value2,b.TypeOf
                                                         FROM #TableA                 a
                                                             LEFT OUTER JOIN #TableB  b ON a.RowID=b.RowID
                                                    ) c2
                                               WHERE c2.rowid=c1.rowid
                                               ORDER BY c1.RowID, TypeOf
                                               FOR XML PATH('') 
                                          )
                                          ,1,2, ''
                                       ) AS CombinedValue
                             FROM (SELECT
                                       a.RowID,a.Value1,a.Value2,b.TypeOf
                                       FROM #TableA                 a
                                           LEFT OUTER JOIN #TableB  b ON a.RowID=b.RowID
                                  ) c1
                             GROUP BY RowID
                        ) dt ON a.RowID=dt.RowID

OUTPUT from SQL Server 2005:

RowID       Value1 Value2 CombinedValue
----------- ------ ------ ------------------
1           aaaaa  A      wood
2           bbbbb  B      rock, steel, wood
3           ccccc  C      paper, plastic

(3 row(s) affected)

EDIT query that Replaces FOR XML PATH with FOR XML RAW, so this should work on SQL服务器 2000

SELECT
    a.*,dt.CombinedValue
    FROM #TableA        a
        LEFT OUTER JOIN (SELECT
                             c1.RowID
                                 ,STUFF(REPLACE(REPLACE(
                                          (SELECT 
                                               ', ' + TypeOf as value
                                               FROM (SELECT
                                                         a.RowID,a.Value1,a.Value2,b.TypeOf
                                                         FROM #TableA                 a
                                                             LEFT OUTER JOIN #TableB  b ON a.RowID=b.RowID
                                                    ) c2
                                               WHERE c2.rowid=c1.rowid
                                               ORDER BY c1.RowID, TypeOf
                                               FOR XML RAW
                                          )
                                         ,'<row value="',''),'"/>','')
                                   , 1, 2, '') AS CombinedValue
                             FROM (SELECT
                                       a.RowID,a.Value1,a.Value2,b.TypeOf
                                       FROM #TableA                 a
                                           LEFT OUTER JOIN #TableB  b ON a.RowID=b.RowID
                                  ) c1
                             GROUP BY RowID
                        ) dt ON a.RowID=dt.RowID

输出，与原始查询相同