При разработке сложных отчетов или обработок в системе 1С:Предприятие 8 программисты часто сталкиваются с ситуацией, когда данные в базе хранятся «вертикально» (каждая характеристика или деталь в новой строке), а вывести их нужно «горизонтально» (в одну строку, но в разные колонки). Типичный пример — работа с ресурсными спецификациями, где для одного изделия может быть указано несколько однотипных комплектующих, например, «Проушина 1» и «Проушина 2».
Если попытаться соединить таблицу продукции с таблицей автокомпонентов простым левым соединением, возникает эффект «декартова произведения»: количество строк в результате начинает множиться, создавая путаницу и дубли. В этой статье мы разберем, как правильно спроектировать запрос, чтобы получить уникальные записи с распределением данных по нужным столбцам.
Проанализируем ситуацию: у нас есть основная таблица (изделия) и подчиненная таблица (компоненты). Когда мы пытаемся присоединить компоненты к изделию, и в подчиненной таблице находится две записи (две проушины), система 1С честно сопоставляет каждую запись из левой части с каждой из правой. Если же мы пытаемся соединить таблицу саму с собой, чтобы получить две разные колонки, мы получаем комбинации «1-1», «1-2», «2-1» и «2-2». Вместо одной строки с двумя колонками мы видим четыре строки.
Рассмотрим три эффективных способа решения этой задачи, от простого к сложному.
Этот метод идеально подходит, если вы точно знаете, что количество повторяющихся элементов не превышает двух. Мы можем сгруппировать записи по основному изделию и выбрать минимальное и максимальное значение характеристики.
Разберем по шагам логику такого запроса. Сначала мы выбираем все данные во временную таблицу, а затем схлопываем их через СГРУППИРОВАТЬ ПО:
ВЫБРАТЬ
Спецификации.Ссылка КАК Спецификация,
Спецификации.Характеристика КАК Проушина
ПОМЕСТИТЬ ВТ_Данные
ИЗ
Справочник.РесурсныеСпецификации.МатериалыИУслуги КАК Спецификации
ГДЕ
Спецификации.Номенклатура.Наименование ПОДОБНО "проушина%";
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Данные.Спецификация,
МИНИМУМ(ВТ_Данные.Проушина) КАК Проушина1,
ВЫБОР
КОГДА МАКСИМУМ(ВТ_Данные.Проушина) <> МИНИМУМ(ВТ_Данные.Проушина)
ТОГДА МАКСИМУМ(ВТ_Данные.Проушина)
ИНАЧЕ NULL
КОНЕЦ КАК Проушина2
ИЗ
ВТ_Данные КАК ВТ_Данные
СГРУППИРОВАТЬ ПО
ВТ_Данные.Спецификация
В данном примере функция МИНИМУМ всегда вернет первое значение, а ВЫБОР КОГДА проверит, есть ли второе значение, отличное от первого. Если деталь всего одна, во второй колонке останется NULL, что позволяет избежать дублирования одной и той же детали в обеих колонках.
Если видов деталей несколько (например, Шток, Опора, Проушина), и для каждого вида нужно создать свою колонку, удобнее использовать конструкцию ОБЪЕДИНИТЬ ВСЕ с последующей группировкой. Это классический способ «разворота» таблицы в 1С.
Проанализируем пример, где мы разделяем разные типы запчастей по разным полям:
ВЫБРАТЬ
BoMs.product КАК Продукт,
BoMs.SKU КАК bumper,
NULL КАК rod,
NULL КАК mount
ИЗ
ТаблицаBoMs КАК BoMs
ГДЕ
BoMs.part ПОДОБНО "%опора буфера%"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
BoMs.product,
NULL,
BoMs.SKU,
NULL
ИЗ
ТаблицаBoMs КАК BoMs
ГДЕ
BoMs.part ПОДОБНО "%шток%"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
BoMs.product,
NULL,
NULL,
BoMs.SKU
ИЗ
ТаблицаBoMs КАК BoMs
ГДЕ
BoMs.part ПОДОБНО "%дно%"
После этого объединения необходимо поместить результат во временную таблицу и выполнить СГРУППИРОВАТЬ ПО полю Продукт, применив функцию МАКСИМУМ к колонкам bumper, rod и mount. Поскольку в каждой строке заполнена только одна колонка, а остальные — NULL, группировка соберет все данные в одну красивую строку.
Это самый продвинутый метод. Он необходим, когда однотипных деталей (проушин) может быть больше двух или их количество заранее неизвестно. Так как в языке запросов 1С нет встроенной функции ROW_NUMBER(), мы создадим её самостоятельно через левое соединение таблицы с самой собой по неравенству.
Рассмотрим, как пронумеровать детали внутри каждой спецификации:
Ссылка = Ссылка И Наименование > Наименование.
ВЫБРАТЬ
Т1.Ссылка КАК Спецификация,
Т1.Характеристика КАК Характеристика,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Характеристика) + 1 КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Нумерация
ИЗ
Справочник.РесурсныеСпецификации.МатериалыИУслуги КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.РесурсныеСпецификации.МатериалыИУслуги КАК Т2
ПО Т1.Ссылка = Т2.Ссылка
И Т1.Характеристика > Т2.Характеристика
ГДЕ
Т1.Номенклатура.Наименование ПОДОБНО "проушина%"
СГРУППИРОВАТЬ ПО
Т1.Ссылка,
Т1.Характеристика;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Спец.Ссылка КАК РесурснаяСпецификация,
ЕСТЬNULL(П1.Характеристика, "") КАК Проушина_1,
ЕСТЬNULL(П2.Характеристика, "") КАК Проушина_2
ИЗ
Справочник.РесурсныеСпецификации КАК Спец
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Нумерация КАК П1
ПО Спец.Ссылка = П1.Спецификация И П1.НомерСтроки = 1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Нумерация КАК П2
ПО Спец.Ссылка = П2.Спецификация И П2.НомерСтроки = 2
Важный нюанс: условие Т1.Характеристика > Т2.Характеристика работает корректно только если значения характеристик (или их ссылок/кодов) уникальны в пределах одной спецификации. Если значения идентичны, нумерация может сбиться, тогда следует добавить в условие соединения уникальный идентификатор строки (например, НомерСтроки из табличной части).
При работе с такими запросами стоит помнить о производительности и чистоте кода:
ПОДОБНО "%шток%" крайне медленный, так как он заставляет СУБД сканировать всю таблицу без использования индексов. Если возможно, используйте отбор по реквизиту Номенклатура.ВидНоменклатуры или по дополнительным реквизитам.ИНДЕКСИРОВАТЬ ПО для полей, по которым происходит соединение (в нашем случае — Спецификация).ЕСТЬNULL(), чтобы избежать пустых значений в итоговом отчете, особенно если планируются дальнейшие вычисления.Выяснив причину появления лишних строк и применив один из описанных методов группировки или нумерации, мы сможем привести любую неупорядоченную таблицу к плоскому виду, удобному для анализа и выгрузки.