SQL Server 2000: Идеи для выполнения подзапроса агрегирования конкатенации
у меня есть запрос, который возвращает строки, которые я хочу, например.
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 не имеет a 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%-й случай только half-assed также.
Потоковый перезапрос
Как более сложное, и склонное к ошибке решение, я думал об итерации отображенного списка и вращении рабочий поток пула потоков для каждого "вопроса" в списке, выполните запрос против базы данных для получения списка авторов, затем агрегировав список в памяти. Это означало бы, что список заполняется сначала в (собственном) приложении. Затем я выпускаю несколько тысяч отдельных запросов впоследствии.
Но это было бы ужасно, страшно, ужасно, медленно. Не говоря уже о пронизанном ошибкой, так как это будет работа потока.
Да да да
Adam Mechanic говорит вполне явно:
Не связывайте строки в разграниченные строки в SQL Server. Сделайте это сторона клиента.
Скажите мне, как, и я сделаю это.
/ крик
Кто-либо может думать о лучшем решении, которое является как быстро (скажите... в порядке величины), чем мой исходный "TOP 1 плюс замещающие знаки" решение?
Например, существует ли способ возвратить набор результатов, где достигают строки, связанные результаты установили? Таким образом для каждой "основной" строки, я мог достигнуть набор результатов "детали", который содержит список.
Код для лучшего ответа
Ссылка бочонка на решение 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
3 ответа
Посмотрите эти статьи:
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/ (См. решение Фила Фактора о перекрестном соединении в ответах - которое будет работать в SQL Server 2000)
Очевидно, что в SQL Server 2005 трюк с FOR XML является самым простым, гибким и в целом наиболее производительным.
Что касается возврата набора рядов для каждого ряда, если вы все еще хотите сделать это по какой-то причине, вы можете сделать это в хранимой процедуре, но клиенту нужно будет потреблять все ряды в первом наборе рядов, затем перейти к следующему набору рядов и связать его с первым рядом в первом наборе рядов и т.д.. Ваша SP должна будет открыть курсор на том же наборе, который она вернула в качестве первого набора рядов, и выполнить несколько последовательных селектов для создания всех дочерних наборов рядов. Я так делал, но только там, где действительно требовались ВСЕ данные (например, в полностью заполненном древовидном представлении).
И независимо от того, что говорят люди, делать это на стороне клиента часто является очень большой тратой полосы пропускания, потому что возврат всех строк и выполнение циклов и разбиения на стороне клиента означает, что огромное количество одинаковых столбцов передается в начале каждой строки только для того, чтобы получить изменяющийся столбец в конце строки.
Где бы вы это ни делали, это должно быть осознанное решение, основанное на вашем сценарии использования.
Вы также можете взглянуть на этот скрипт . По сути, это метод перекрестного соединения, о котором Кейд Ру также упомянул в своем сообщении.
Вышеупомянутый подход выглядит очень чистым: сначала нужно создать представление, а затем создать оператор, основанный на значениях в представлении. Второй оператор sql вы можете динамически строить в своем коде, поэтому его следует легко использовать.
Я не уверен, что это работает в 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 из 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 запрос, который заменяет FOR XML PATH на FOR XML RAW, так что это должно работать на SQL Server 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
OUTPUT, то же, что и оригинальный запрос