Как Stuff и "для Xml Path" работают на сервере Sql
Таблица:
+----+------+
| Id | Name |
+----+------+
| 1 | aaa |
| 1 | bbb |
| 1 | ccc |
| 1 | ddd |
| 1 | eee |
+----+------+
Требуемый вывод:
+----+---------------------+
| Id | abc |
+----+---------------------+
| 1 | aaa,bbb,ccc,ddd,eee |
+----+---------------------+
Query:
SELECT ID,
abc = STUFF(
(SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
)
FROM temp1 GROUP BY id
Этот запрос работает правильно. Но мне просто нужно объяснение, как это работает, или есть какой-либо другой или короткий способ сделать это.
Я очень смущен, чтобы понять это.
Ответы
Ответ 1
Вот как это работает:
1. Получить строку XML-элемента с FOR XML
Добавление FOR XML PATH в конец запроса позволяет выводить результаты запроса как элементы XML с именем элемента, содержащимся в аргументе PATH. Например, если бы мы выполнили следующую инструкцию:
SELECT ',' + name
FROM temp1
FOR XML PATH ('')
Пропустив пустую строку (FOR XML PATH ('')), мы получим следующее:
,aaa,bbb,ccc,ddd,eee
2. Удалить ведущую запятую с помощью кнопки STUFF
Оператор STUFF буквально "наполняет" одну строку в другую, заменяя символы в первой строке. Однако мы используем его просто для удаления первого символа результирующего списка значений.
SELECT abc = STUFF((
SELECT ',' + NAME
FROM temp1
FOR XML PATH('')
), 1, 1, '')
FROM temp1
Параметры STUFF:
- Строка, которая будет "заполнена" (в нашем случае полный список имен с
ведущая запятая)
- Место для начала удаления и вставки символов (1, вставлялось в пустую строку)
- Количество символов для удаления (1, являющаяся ведущей запятой)
Итак, в итоге получим:
aaa,bbb,ccc,ddd,eee
3. Присоединитесь к id, чтобы получить полный список
Далее мы просто присоединяем это к списку идентификаторов в таблице temp, чтобы получить список идентификаторов с именем:
SELECT ID, abc = STUFF(
(SELECT ',' + name
FROM temp1 t1
WHERE t1.id = t2.id
FOR XML PATH (''))
, 1, 1, '') from temp1 t2
group by id;
И у нас есть наш результат:
-----------------------------------
| Id | Name |
|---------------------------------|
| 1 | aaa,bbb,ccc,ddd,eee |
-----------------------------------
Надеюсь, это поможет!
Ответ 2
В этой статье рассматриваются различные способы объединения строк в SQL, включая улучшенную версию вашего кода, которая не кодирует XML-конкатенированные значения.
SELECT ID, abc = STUFF
(
(
SELECT ',' + name
FROM temp1 As T2
-- You only want to combine rows for a single ID here:
WHERE T2.ID = T1.ID
ORDER BY name
FOR XML PATH (''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '')
FROM temp1 As T1
GROUP BY id
Чтобы понять, что происходит, начните с внутреннего запроса:
SELECT ',' + name
FROM temp1 As T2
WHERE T2.ID = 42 -- Pick a random ID from the table
ORDER BY name
FOR XML PATH (''), TYPE
Поскольку вы указываете FOR XML, вы получите одну строку, содержащую фрагмент XML, представляющий все строки.
Поскольку вы не указали псевдоним столбца для первого столбца, каждая строка будет обернута в элемент XML с именем, указанным в скобках после FOR XML PATH. Например, если у вас есть FOR XML PATH ('X'), вы получите XML-документ, похожий на:
<X>,aaa</X>
<X>,bbb</X>
...
Но, поскольку вы не указали имя элемента, вы просто получите список значений:
,aaa,bbb,...
.value('.', 'varchar(max)') просто извлекает значение из полученного XML-фрагмента без XML-кодирования любых "специальных" символов. Теперь у вас есть строка, которая выглядит так:
',aaa,bbb,...'
Затем функция STUFF удаляет ведущую запятую, давая окончательный результат, который выглядит следующим образом:
'aaa,bbb,...'
На первый взгляд он выглядит довольно запутанным, но он имеет тенденцию работать довольно хорошо по сравнению с некоторыми другими вариантами.
Ответ 3
Режим PATH используется для генерации XML из запроса SELECT
1. SELECT
ID,
Name
FROM temp1
FOR XML PATH;
Ouput:
<row>
<ID>1</ID>
<Name>aaa</Name>
</row>
<row>
<ID>1</ID>
<Name>bbb</Name>
</row>
<row>
<ID>1</ID>
<Name>ccc</Name>
</row>
<row>
<ID>1</ID>
<Name>ddd</Name>
</row>
<row>
<ID>1</ID>
<Name>eee</Name>
</row>
Выход представляет собой XML-элемент, ориентированный на элементы, где каждое значение столбца в результирующем наборе строк завернуто в элемент строки. Поскольку в предложении SELECT не указаны псевдонимы для имен столбцов, генерируемые имена дочерних элементов совпадают с именами соответствующих столбцов в предложении SELECT.
Для каждой строки в наборе строк добавлен тег.
2.
SELECT
ID,
Name
FROM temp1
FOR XML PATH('');
Ouput:
<ID>1</ID>
<Name>aaa</Name>
<ID>1</ID>
<Name>bbb</Name>
<ID>1</ID>
<Name>ccc</Name>
<ID>1</ID>
<Name>ddd</Name>
<ID>1</ID>
<Name>eee</Name>
Для шага 2: если вы укажете строку нулевой длины, элемент-обтекатель не будет создан.
3.
SELECT
Name
FROM temp1
FOR XML PATH('');
Ouput:
<Name>aaa</Name>
<Name>bbb</Name>
<Name>ccc</Name>
<Name>ddd</Name>
<Name>eee</Name>
4. SELECT
',' +Name
FROM temp1
FOR XML PATH('')
Ouput:
,aaa,bbb,ccc,ddd,eee
На шаге 4 мы конкатенируем значения.
5. SELECT ID,
abc = (SELECT
',' +Name
FROM temp1
FOR XML PATH('') )
FROM temp1
Ouput:
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
6. SELECT ID,
abc = (SELECT
',' +Name
FROM temp1
FOR XML PATH('') )
FROM temp1 GROUP by iD
Ouput:
ID abc
1 ,aaa,bbb,ccc,ddd,eee
На шаге 6 мы группируем дату по идентификатору.
STUFF (source_string, start, length, add_string)
Параметры или аргументы
исходная_строка
Исходная строка для изменения.
Начало
Позиция в source_string для удаления символов длины, а затем вставить add_string.
длина
Количество символов для удаления из source_string.
add_string
Последовательность символов для вставки в исходную строку в начальной позиции.
SELECT ID,
abc =
STUFF (
(SELECT
',' +Name
FROM temp1
FOR XML PATH('')), 1, 1, ''
)
FROM temp1 GROUP by iD
Output:
-----------------------------------
| Id | Name |
|---------------------------------|
| 1 | aaa,bbb,ccc,ddd,eee |
-----------------------------------
Ответ 4
В Azure SQL Database и SQL Server (начиная с 2017) появилась новая функциональность для обработки этого точного сценария. Я считаю, что это послужит официальным официальным методом для того, что вы пытаетесь выполнить с помощью метода XML/STUFF. Пример:
select id, STRING_AGG(name, ',') as abc
from temp1
group by id
STRING_AGG - https://msdn.microsoft.com/en-us/library/mt790580.aspx
EDIT: Когда я изначально разместил это, я упомянул SQL Server 2016, поскольку, как я думал, я видел это по потенциальной функции, которая должна была быть включена. Либо я вспомнил, что неправильно или что-то изменилось, спасибо за предлагаемое исправление исправления версии. Кроме того, довольно впечатлен и не был полностью осведомлен о многоступенчатом процессе обзора, который просто втянул меня в окончательный вариант.
Ответ 5
In для пути xml
Если мы определим любое значение, подобное [для пути xml ('ENVLOPE'), тогда
выше теги будут добавлены с каждой строкой.
Ответ 6
Declare @Temp As Table (Id Int,Name Varchar(100))
Insert Into @Temp values(1,'A'),(1,'B'),(1,'C'),(2,'D'),(2,'E'),(3,'F'),(3,'G'),(3,'H'),(4,'I'),(5,'J'),(5,'K')
Select X.ID,
stuff((Select ','+ Z.Name from @Temp Z Where X.Id =Z.Id For XML Path('')),1,1,'')
from @Temp X
Group by X.ID
Ответ 7
SELECT ID,
abc = STUFF(
(SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
)
FROM temp1 GROUP BY id
Здесь в приведенном выше запросе функция STUFF используется для удаления первой запятой (,) из сгенерированной строки xml (,aaa,bbb,ccc,ddd,eee), тогда она станет (aaa,bbb,ccc,ddd,eee).
И FOR XML PATH('') просто преобразует данные столбца в строку (,aaa,bbb,ccc,ddd,eee), но в PATH мы передаем ', поэтому он не будет создавать тег XML.
И в конце мы сгруппировали записи, используя столбец ID.
Ответ 8
Я отлаживал и, наконец, возвращал свой "stuffed" запрос к нему обычным способом.
Просто
select * from myTable for xml path('myTable')
дает мне содержимое таблицы для записи в таблицу журнала из триггера, который я отлаживаю.
