loading..
Русский    English
12:36
листать

Функция STRING_AGG

Агрегация текстовых данных

Рассмотрим такую задачу.

Перечислить через запятую все корабли из таблицы Ships, которые принадлежат Японии.

Получить список кораблей Японии труда не составляет:

Консоль
Выполнить
  1. SELECT name FROM Ships s JOIN Classes c ON s.class=c.class
  2. WHERE country='Japan'
  3. ORDER BY name;

В MySQL есть замечательная агрегатная функция GROUP_CONCAT, которая решает поставленную задачу:

  1. SELECT GROUP_CONCAT(name) ships_list FROM Ships s JOIN Classes c ON s.class=c.class
  2. WHERE country='Japan'
  3. ORDER BY name;

ships_list
haruna,hiei,kirishima,kon,musashi,yamato

По умолчанию в качестве разделителя как раз используется запятая, хотя мы можем выбрать любой символ.

Если выполнить группировку, то легко получить список кораблей для каждой страны:

  1. SELECT country, GROUP_CONCAT(name) ships_list
  2. FROM Ships s JOIN Classes c ON s.class=c.class
  3. GROUP BY country
  4. ORDER BY country, name;

country    ships_list
gt.britain    renown,repulse,resolution,ramillies,revenge,royal oak,royal sovereign
japan    haruna,hiei,kirishima,kongo,musashi,yamato
usa    iowa,missouri,new jersey,wisconsin,north carolina,south dakota,washington,california,tennessee

Для SQL Server решение нашей задачи можно получить менее естественным способом - через представление результата выборки в форме XML:

Консоль
Выполнить
  1. SELECT STUFF(
  2. (SELECT ','+name AS 'data()' FROM Ships s JOIN Classes c ON s.class=c.class
  3. WHERE country='Japan'
  4. ORDER BY name FOR XML PATH('')
  5. ),1,1,'');

Группировка по стране еще добавит сложности. Но мы не будем этого делать, поскольку в SQL Server, начиная с версии 2017, появилась функция STRING_AGG, позволяющая конкатенировать строки. Эта функция имеет два обязательных аргумента - строковое выражение, которое и будет использоваться для сцепления, и разделитель.

  1. SELECT country, STRING_AGG(name,',') ships_list
  2. FROM Ships s JOIN Classes c ON s.class=c.class
  3. GROUP BY country
  4. ORDER BY country;

country    ships_list
Gt.Britain    Renown,Repulse,Resolution,Ramillies,Revenge,Royal Oak,Royal Sovereign
Japan    Musashi,Yamato,Haruna,Hiei,Kirishima,Kongo
USA    North Carolina,South Dakota,Washington,Iowa,Missouri,New Jersey,Wisconsin,California,Tennessee

Из представленного результата видно, что корабли в списке не отсортированы. Сортировка в стиле GROUP_CONCAT здесь не работает. Чтобы задать порядок сортировки, используется необязательное предложение WITHIN GROUP:

  1. SELECT country, STRING_AGG(name,',') WITHIN GROUP (ORDER BY name) ships_list
  2. FROM Ships s JOIN Classes c ON s.class=c.class
  3. GROUP BY country
  4. ORDER BY country;

country    ships_list
Gt.Britain    Ramillies,Renown,Repulse,Resolution,Revenge,Royal Oak,Royal Sovereign
Japan    Haruna,Hiei,Kirishima,Kongo,Musashi,Yamato
USA    California,Iowa,Missouri,New Jersey,North Carolina,South Dakota,Tennessee,Washington,Wisconsin

Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
несколько дней назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100