Склеить два поля в запросе

Добавил пользователь Morpheus
Обновлено: 10.09.2024

Я ищу способ объединить строки поля в группе по запросу. Так, например, у меня есть таблица:

и я хотел сгруппировать по company_id, чтобы получить что-то вроде:

В mySQL есть встроенная функция для этого group_concat

PostgreSQL 9.0 или более поздняя версия:

В последних версиях Postgres (с конца 2010 года) есть string_agg(expression, delimiter) функция, которая будет выполнять именно то, о чем спрашивался вопрос, даже позволяя указать строку разделителя:

В Postgres 9.0 также добавлена возможность указывать ORDER BY предложение в любом агрегированном выражении ; в противном случае порядок не определен. Теперь вы можете написать:

PostgreSQL 8.4 или более поздняя версия:

PostgreSQL 8.4 (в 2009 году) представил функцию агрегирования, array_agg(expression) которая объединяет значения в массив. Затем array_to_string() можно использовать, чтобы дать желаемый результат:

string_agg для версий до 8.4:

В случае, если кто-то сталкивается с этим в поисках прокладки совместимости для баз данных до 9.0, можно реализовать все, string_agg кроме ORDER BY пункта.

Таким образом, с приведенным ниже определением это должно работать так же, как в 9.x Postgres DB:

Но это будет синтаксическая ошибка:

Протестировано на PostgreSQL 8.3.

Пользовательские варианты (все версии Postgres)

До 9.0 не было встроенной агрегатной функции для объединения строк. Простейшая пользовательская реализация ( предложенная Вайда Габо в этом посте списка рассылки , среди многих других) заключается в использовании встроенной textcat функции (которая скрывается за || оператором):

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

Объединение в запросах 1С 8.3 - это выполнение двух разных запросов и объединение их результатов в один общий.

Требования к объединяемым таблицам это одинаковое количество полей. Если соответствующие друг другу поля в разных таблицах имеют один и тот же тип, то итоговое поле будет тоже иметь этот тип, если же тип разный, то итоговое поле будет иметь составной тип данных. Упорядочивать (группировать, подводить итоги) таблиц имеет смысл только после их объединения в конечный результат.

ОБЪЕДИНИТЬ - конструкция объединяет два результата и группирует повторяющие строки (дубликаты строк из разных запросов будут удалены).
ОБЪЕДИНИТЬ ВСЕ - также объединяет два результата, но строки в результате автоматически не группирует (дубликаты строк из разных запросов удалены не будут). Используется для ускорения выполнения запроса, когда заведомо невозможно получения одинаковых строк.

? Объединение двух таблиц в одну

&НаСервере
Процедура ОбъединениеДвухТаблицВОдну ()

РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;

&НаСервере
Процедура ОбъединениеБолееДвухТаблицВОдну ()

&НаСервере
Процедура СохранениеДубликатовВРезультатеОбъединенияТаблиц ()

&НаСервере
Процедура ОбъединениеТаблицСРазнымКоличествомПолей ()

&НаСервере
Процедура УпорядочиваниеПриОбъединенииТаблиц ()

В процессе работы я столкнулся с необходимостью объединения строк в базе данных. Казалось бы, это достаточно тривиальная задача, но не все так просто, как кажется на первый взгляд. Давайте подробнее рассмотрим различные методы конкатенации строк в SQL и те проблемы, с которыми я столкнулся.

Оператор +

Пока все просто, но это не то, что было мне нужно. Потому что, мне было нужно объединять большое количество значений, а делать это через знак + было не удобно. Давайте рассмотрим и другие способы конкатенации.

Функция CONCAT

Данная функция позволяет объединить все параметры, передаваемые в нее в качестве аргументов. Минимальное количество параметров два. Добавлю очень важное замечание, данная функция появилась в SQL Server начиная с 2012 версии. Это, кстати, принципиально. Потому что зачастую используются старые версии сервера, и вы не сможете использовать новые возможности языка. Всегда проверяйте, поддерживает ли ваша версия SQL сервера используемые возможности. Особенно при переносе с одного сервера на другой.

Работает данная функция аналогично оператору +. Например:

Но этот вариант меня тоже не устраивал, потому что мне нужно было объединить результаты через запятую.

Функция CONCAT_WS

Данная функция позволяет конкатенировать строки через разделитель. Но ее основной проблемой, как и многих других, которые мы рассмотрим в дальнейшем, что она была добавлена только в версии SQL Server 2017, который пока практически нигде не используется. Первым параметром указывается разделитель, затем перечисляются параметры.

Так как у меня используется SQL куда более старой версии, то воспользоваться я ей благополучно не смог… Поэтому пришлось начать куда более не стандартные функции для конкатенации.

Функция STAFF

Переходим к более сложным примерам, и для начала рассмотрим предметную область. У нас есть три таблицы, это Человек, Проект и Назначение. Все достаточно просто, обычная реализация связи многие к многим человека и проекта.

Если очень упростить, то задача состояла в том, чтобы вывести через запятую все проекты назначенные на человека. Сначала я попытался воспользоваться функцией STAFF. Рассмотрим элементарный, но бесполезный пример

Здесь, в подзапросе мы получаем все элементы таблицы Project, и соединяем их через точку с запятой, начиная именно с точки запятой, а затем просто удаляем первый символ (как раз не нужную точку с запятой). И выводим для каждого назначения.

Но когда я захотел сгруппировать результаты по идентификатору пользователя с помощью GROUP BY я узнал, что сделать этого не смогу, так как STAFF не является агрегирующей функцией. Поэтому пошел дальше изучать просторы интернета.

Функция STRING_AGG

Отличная функция которая полностью решила все мои проблемы. За исключением одного, она появилась в SQL Server 2017, поэтому оказалась хоть и очень хорошей, но бесполезной

Функция GROUP_CONCAT

Ну и наконец самое сладкое. Не найдя ни одной подходящей конкатенирующей агрегирующей функции, я нашел возможность с помощью кастомной агрерирующей функции. Есть проект на github orlando-colamatteo/ms-sql-server-group-concat-sqlclr, который предоставляет готовый скрипт, который добавляет новую конкатенирующую функцию GROUP_CONCAT. Посмотрим пример.

Именно благодаря этой функции я решил все свои проблемы и выполнил поставленную задачу. Моя огромная благодарность ее создателю. Ну а теперь давайте рассмотрим процесс установки и что это вообще такое.

Установка GROUP_CONCAT в MS SQL Server

Для начала заходим на github и скачиваем проект. Распаковываем его в любую директорию. Заходим в папку D:\ms-sql-server-group-concat-sqlclr-master\GroupConcat\Installation Scripts и открываем файл GroupConcatInstallation.sql. Он уже практически готов к использованию. Единственно что нужно сделать, это изменить имя базы данных на используемое у вас.

Обратите внимание, что для выполнения вам потребуются права администратора на SQL Server. После этого вы сможете использовать данную агрегатную функцию в пределах базы данных. MS SQL Management Studio может подчеркивать функцию как ошибку, но не пугайтесь, он будет успешно работать.

Что такое SQL CLR?

С ее помощью предоставляется возможность создания пользовательских функций, типов данных, аргегатных функций, триггеров, хранимых процедур, написанных на высокопроизводительных языках. Благодаря этому можно значительно увеличить производительность, а также расширять функционал стандартного SQL. Пример можно изучить в статье на Habr.

Соединение строк sql — Заключение

На этом у меня все. Надеюсь данный материал будет полезен. Но больше всего меня поражает то, что компания Microsoft ввела агрегирующую функцию конкатенации строк только в 2017 году…

Нужен селект (для работы с базой access). В таблице REGISTRY выбрать все поля, где объединенные поля Code и Numb равны некоему полю.
Проблема в объединении этих полей в селекте и сравнении их с другим полем. Туплю( Заранее спасибо!

Да, два поля соединить без пробелов (оба числовые). пробовала с concat - access ругается на неизвестную функцию.

вы нас обманываете. в числовом поле не может быть "001"
там может быть только 1 (без ведущих нулей).
Ну или поле имеет не числовой тип!

кстати, для текстовых полей в MS Access можно использовать операцию "+":

Спасибо! Запрос получился:
SELECT *
FROM REGISTRY
WHERE (SELECT code+ "." + numb
FROM REGISTRY) = Поле;

Только вот теперь, когда в Поле вношу искомое значение, аксесс выдает: "Данный подчиненный запрос должен возвращать не более одной записи. Исправьте инструкцию SELECT в подчиненном запросе и укажите в ней возвращение одной записи". Теперь туплю, как эту одну запись указать :'(

Читайте также: