При разработке отчетов и механизмов проведения документов в среде «1С:Предприятие 8» программисты часто сталкиваются с необходимостью собрать воедино данные из нескольких источников. Типичный пример — получение остатков по счетам из регистра накопления «Счета» и остатков по взаиморасчетам из регистра «Взаиморасчеты».
Попытка решить эту задачу «в лоб» через простое объединение запросов (ОБЪЕДИНИТЬ ВСЕ) часто приводит к неожиданному результату: в финальной выборке оказывается только одна колонка, а данные из второго регистра записываются в нее же, смешиваясь с первыми. В этой статье мы подробно разберем, почему так происходит, как правильно выравнивать колонки в объединениях, как избежать дублирования строк с помощью временных таблиц и почему этот способ эффективнее стандартного соединения таблиц.
Рассмотрим исходную ситуацию. Разработчик пытается выполнить следующий запрос:
ВЫБРАТЬ
СчетаОстатки.СуммаОстаток КАК ОстатокСчет
ИЗ
РегистрНакопления.Счета.Остатки(&МоментВремени, Счёт В (&Счёт)) КАК СчетаОстатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВзаиморасчетыОстатки.СуммаОстаток КАК ОстатокВзаиморасчёт
ИЗ
РегистрНакопления.Взаиморасчеты.Остатки(&МоментВремени, Счёт В (&Счёт)) КАК ВзаиморасчетыОстатки
В результате выполнения этого запроса мы увидим только одну колонку — ОстатокСчет. В нее попадут как суммы из первого регистра, так и суммы из второго. Почему так происходит?
Правило работы объединений в SQL и 1С: При использовании конструкции ОБЪЕДИНИТЬ или ОБЪЕДИНИТЬ ВСЕ результирующая структура колонок (их имена, типы данных и количество) определяется строго по первому запросу. Поля последующих запросов сопоставляются с полями первого запроса исключительно по их порядковому номеру (индексу), а не по именам или синонимам. Синонимы полей (КАК...) во втором и последующих запросах просто игнорируются СУБД.
Поскольку в обоих запросах выше выбирается ровно по одному полю, платформа сопоставила их и объединила в одну общую колонку с именем из первого запроса. Для решения этой проблемы нам необходимо применить механизм «выравнивания» колонок.
Чтобы разнести данные из разных регистров по разным колонкам, мы должны сделать так, чтобы в каждом из объединяемых запросов присутствовало одинаковое количество полей в одинаковом порядке. Для «пустых» полей мы будем использовать заглушки в виде числового нуля (0) или значения NULL.
Давайте посмотрим на пример запроса с использованием числовых заглушек:
ВЫБРАТЬ
СчетаОстатки.Счёт КАК Счёт,
СчетаОстатки.СуммаОстаток КАК ОстатокСчет,
0 КАК ОстатокВзаиморасчет
ИЗ
РегистрНакопления.Счета.Остатки(&МоментВремени, Счёт В (&Счёт)) КАК СчетаОстатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВзаиморасчетыОстатки.Счёт,
0,
ВзаиморасчетыОстатки.СуммаОстаток
ИЗ
РегистрНакопления.Взаиморасчеты.Остатки(&МоментВремени, Счёт В (&Счёт)) КАК ВзаиморасчетыОстатки
Проанализируем структуру этого запроса:
Счёт, реальный остаток ОстатокСчет и числовую заглушку 0 КАК ОстатокВзаиморасчет.Счёт, заглушку 0 (которая сопоставится с полем ОстатокСчет) и реальный остаток СуммаОстаток (который сопоставится с полем ОстатокВзаиморасчет).Теперь в выборке действительно появятся две разные колонки остатков. Однако у этого решения в чистом виде есть один критический побочный эффект — «раздвоение» строк.
Если выполнить предыдущий запрос, СУБД вернет по две строки на каждый счет (при условии, что остатки есть в обоих регистрах):
ОстатокСчет, а ОстатокВзаиморасчет будет равен 0.ОстатокСчет будет равен 0, а ОстатокВзаиморасчет будет заполнен актуальной суммой.Чтобы схлопнуть эти строки в одну красивую запись по каждому счету, результат объединения обязательно нужно сгруппировать по ключевым полям. Для этого мы поместим результат объединения во временную таблицу, а затем выберем данные из нее с использованием агрегатной функции СУММА.
Разберем готовое решение по шагам:
ВЫБРАТЬ
СчетаОстатки.Счёт КАК Счёт,
СчетаОстатки.СуммаОстаток КАК ОстатокСчет,
0 КАК ОстатокВзаиморасчет
ПОМЕСТИТЬ ВТ_СводныеОстатки
ИЗ
РегистрНакопления.Счета.Остатки(&МоментВремени, Счёт В (&Счёт)) КАК СчетаОстатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВзаиморасчетыОстатки.Счёт,
0,
ВзаиморасчетыОстатки.СуммаОстаток
ИЗ
РегистрНакопления.Взаиморасчеты.Остатки(&МоментВремени, Счёт В (&Счёт)) КАК ВзаиморасчетыОстатки
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_СводныеОстатки.Счёт КАК Счёт,
СУММА(ВТ_СводныеОстатки.ОстатокСчет) КАК ОстатокСчет,
СУММА(ВТ_СводныеОстатки.ОстатокВзаиморасчет) КАК ОстатокВзаиморасчет
ИЗ
ВТ_СводныеОстатки КАК ВТ_СводныеОстатки
СГРУППИРОВАТЬ ПО
ВТ_СводныеОстатки.Счёт
В этом пакете запросов во второй части мы сгруппировали данные по полю Счёт. СУБД сложила числовые показатели, и нулевые заглушки благополучно прибавились к реальным остаткам, образовав единую красивую строку для каждого счета.
В качестве заглушек в первом и втором запросах объединения можно передавать не только 0, но и ключевое слово NULL. Выбор между ними зависит от того, как вы планируете выводить данные:
0, то в итоговом результате (например, при выводе в отчет или табличный документ) пустые остатки будут отображаться как нули.NULL, то в полях без остатков визуально будет отображаться пустота, что часто выглядит более аккуратно.Важный нюанс: Если мы решаем использовать NULL в качестве заглушки, на этапе финальной группировки и суммирования полей крайне рекомендуется оборачивать результат функции СУММА в функцию ЕСТЬNULL. Это позволит избежать потенциальных проблем при дальнейших математических расчетах в коде.
Посмотрим, как изменится наш запрос при использовании NULL:
ВЫБРАТЬ
СчетаОстатки.Счёт КАК Счёт,
СчетаОстатки.СуммаОстаток КАК ОстатокСчет,
ЗНАЧЕНИЕ(Справочник.Счета.ПустаяСсылка) КАК ПолеЗаглушкаДляСправочника, // Пример для объектных типов
NULL КАК ОстатокВзаиморасчет
ПОМЕСТИТЬ ВТ_ОстаткиСNULL
ИЗ
РегистрНакопления.Счета.Остатки(&МоментВремени, Счёт В (&Счёт)) КАК СчетаОстатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВзаиморасчетыОстатки.Счёт,
NULL,
ЗНАЧЕНИЕ(Справочник.Счета.ПустаяСсылка),
ВзаиморасчетыОстатки.СуммаОстаток
ИЗ
РегистрНакопления.Взаиморасчеты.Остатки(&МоментВремени, Счёт В (&Счёт)) КАК ВзаиморасчетыОстатки
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ОстаткиСNULL.Счёт КАК Счёт,
ЕСТЬNULL(СУММА(ВТ_ОстаткиСNULL.ОстатокСчет), 0) КАК ОстатокСчет,
ЕСТЬNULL(СУММА(ВТ_ОстаткиСNULL.ОстатокВзаиморасчет), 0) КАК ОстатокВзаиморасчет
ИЗ
ВТ_ОстаткиСNULL КАК ВТ_ОстаткиСNULL
СГРУППИРОВАТЬ ПО
ВТ_ОстаткиСNULL.Счёт
Некоторые разработчики предпочитают связывать таблицы остатков через ПОЛНОЕ СОЕДИНЕНИЕ (FULL JOIN) по полю связи (в данном случае — Счет). Однако стандарты разработки фирмы «1С» и требования к производительности настоятельно рекомендуют избегать такого подхода по двум ключевым причинам:
NULL. Чтобы вывести счет без искажений, в секции ВЫБРАТЬ приходится писать громоздкие и непроизводительные конструкции вида ЕСТЬNULL(ТаблицаСчета.Счет, ТаблицаВзаиморасчеты.Счет) КАК Счет.Если вы разрабатываете отчет на базе СКД, то писать сложные запросы с объединением и временными таблицами вручную вообще не обязательно. Платформа умеет решать эту задачу на уровне настроек компоновки данных.
Для реализации этого в СКД:
Счет.При формировании отчета СКД автоматически выполнит оптимальное левое или полное соединение на уровне платформы, сама сгруппирует данные и корректно обработает значения NULL, избавив вас от рутины написания сложного кода.