SQL Server 2000：执行串联聚合子查询的想法 [英] SQL Server 2000: Ideas for performing concatenation aggregation subquery
问题描述
我有一个查询，该查询返回我想要的行，例如
i have a query that returns rows that i want, e.g.
QuestionID QuestionTitle UpVotes DownVotes
========== ============= ======= =========
2142075 Win32: Cre... 0 0
2232727 Win32: How... 2 0
1870139 Wondows Ae... 12 0
现在，我想返回一个列，其中包含用逗号分隔的 作者列表（例如原始海报和编辑者）。例如：
Now i want to have a column returned, that contains a comma separated list of "Authors" (e.g. original poster and editors). e.g.:
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，'，'）聚合操作，我一直在伪造它-对<$ c执行简单的子选择$ c> TOP 1 作者，以及作者计数。
SQL Server 2000 does not have a CONCAT(AuthorName, ', ') aggregation operation, i've been faking it - performing simple sub-selects for the TOP 1 author, and the author count.
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
如果一位作者，然后我向用户显示一个省略号（…），以表示有多个。例如用户会看到：
If there is more than one author, then i show the user an ellipses ("…"), to indicate there is more than one. e.g. the user would see:
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％的情况也只有一半 -assert 。
And that works well enough, since normally a question isn't edited - which means i'm supporting the 99% case perfectly, and the 1% case only half-assed as well.
作为一个更复杂且易出错的解决方案，我正在考虑迭代显示的列表，并为每个线程创建一个线程池工作线程列表中的 问题，对数据库执行查询以获取作者列表，然后将列表汇总到内存中。这意味着该列表首先填充（本机）应用程序。然后，我随后发出几千个单独的查询。
As a more complicated, and bug-prone solution, i was thinking of iterating the displayed list, and spinning up a thread-pool worker thread for each "question" in the list, perform a query against the database to get the list of authors, then aggregating the list in memory. This would mean that the list fills first in the (native) application. Then i issue a few thousand individual queries afterwards.
但这太慢，太可怕，太慢了。
But that would be horribly, horrendously, terribly, slow. Not to mention bug-riddled, since it will be thread work.
亚当机械师说得很清楚：
在SQL Server中不要将行连接成分隔的
字符串。
Don't concatenate rows into delimited strings in SQL Server. Do it client side.
告诉我如何做，我会做。
Tell me how, and i'll do it.
谁能想到更好的解决方案，那比我原来的 TOP 1 plus椭圆形解决方案要快（例如...一个数量级）？
Can anyone think of a better solution, that is as fast (say...within an order of magnitude) than my original "TOP 1 plus ellipses" solution?
例如，有没有办法返回结果集，到达范围行具有关联的结果集？因此，对于每个主行，我可以得到一个包含列表的详细结果集。
For example, is there a way to return a results set, where reach row has an associated results set? So for each "master" row, i could get at a "detail" results set that contains the list.
Cade的链接到 Adam Machanic的解决方案我最喜欢。用户定义的函数，似乎可以通过魔术操作：
Cade's link to Adam Machanic's solution i like the best. A user-defined function, that seems to operate via magic:
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
推荐答案
看看这些文章：
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中可以使用）
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ (See Phil Factor's cross join solution in the responses - which will work in SQL Server 2000)
很明显，在SQL Server 2005中，FOR XML技巧最简单，最灵活并且通常是性能最高的。
Obviously in SQL Server 2005, the FOR XML trick is easiest, most flexible and generally most performant.
每行的行集，如果出于某种原因仍要执行此操作，则可以在存储过程中执行此操作，但是客户端将需要使用第一个行集中的所有行，然后转到下一个行集并将其与第一行集中的第一行，等等。您的SP将需要在与作为第一个行集返回的相同集合上打开游标，并依次运行多个选择以生成所有子行集。这是我已经完成的一项技术，但仅在实际需要 ALL 数据的地方（例如，在完全填充的树状视图中）。
As far as returning a rowset for each row, if you still want to do that for some reason, you can do that in a stored procedure, but the client will need to consume all the rows in the first rowset and then go to the next rowset and associate it with the first row in the first rowset, etc. Your SP would need to open a cursor on the same set it returned as the first rowset and run multiple selects in sequence to generate all the child rowsets. It's a technique I've done, but only where ALL the data actually was needed (for instance, in a fully-populated tree view).
无论人们怎么说，客户端这样做通常会浪费大量带宽，因为返回所有行并在客户端进行循环和中断意味着在客户端传输了大量相同的列。
And regardless of what people say, doing it client-side is often a very big waste of bandwidth, because returning all the rows and doing the looping and breaking in the client side means that huge number of identical columns are being transferred at the start of each row just to get the changing column at the end of the row.
无论您做什么，它都应该是一个明智的决定基于您的用例。
Wherever you do it, it should be an informed decision based on your use case.
这篇关于SQL Server 2000：执行串联聚合子查询的想法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
