При составлении отчетов по движению товаров часто возникает необходимость сопоставить данные из разных регистров — например, выгрузить закупки по контрагенту и посмотреть, были ли продажи по полученным позициям номенклатуры. Разберем распространенную проблему: разработчик использует ЛЕВОЕ СОЕДИНЕНИЕ, однако товары, по которым были приходы, но не было реализации, полностью выпадают из результата запроса.
Проанализируем ситуацию на примере исходного запроса, выясним причину такого поведения и рассмотрим несколько способов правильного построения запросов на языке 1С.
Рассмотрим фрагмент запроса, в котором проявляется проблема:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Закупки.Номенклатура КАК Номенклатура,
Закупки.Количество,
СУММА(Закупки.Стоимость) КАК Стоимость,
Продажи.Номенклатура КАК Номенклатура1,
Продажи.Количество КАК Количество1,
СУММА(Продажи.Стоимость) КАК Стоимость1
ИЗ
РегистрНакопления.Закупки КАК Закупки
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи КАК Продажи
ПО (Закупки.Номенклатура = Продажи.Номенклатура)
ГДЕ
Закупки.Контрагент = &ВыбКонтр
И Закупки.Период >= &д1
И Закупки.Период <= &д2
И Продажи.Период >= &д1
И Продажи.Период <= &д2
СГРУППИРОВАТЬ ПО
Закупки.Номенклатура,
Закупки.Количество,
Закупки.Контрагент,
Продажи.Номенклатура,
Продажи.Количество,
Продажи.Контрагент
Главная причина неработоспособности левого соединения кроется в секции ГДЕ:
И Продажи.Период >= &д1
И Продажи.Период <= &д2
При левом соединении, если по номенклатуре закупки не было ни одной продажи, СУБД подставляет значение NULL для всех полей из правой таблицы (в данном случае РегистрНакопления.Продажи). Когда система начинает вычислять условие ГДЕ, любые проверки вида NULL >= &д1 возвращают Ложь. В результате строки, для которых продажи отсутствуют, полностью отсекаются. Таким образом, фактически ЛЕВОЕ СОЕДИНЕНИЕ превращается во ВНУТРЕННЕЕ СОЕДИНЕНИЕ.
Если требуется оставить обращение к физическим таблицам регистров, все отборы, относящиеся к присоединяемой (правой) таблице, необходимо перенести из секции ГДЕ в условие связи ПО.
ВЫБРАТЬ
Закупки.Номенклатура КАК Номенклатура,
СУММА(Закупки.Количество) КАК Количество,
СУММА(Закупки.Стоимость) КАК Стоимость,
ЕСТЬNULL(Продажи.Номенклатура, ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)) КАК НоменклатураПродажи,
СУММА(ЕСТЬNULL(Продажи.Количество, 0)) КАК КоличествоПродажи,
СУММА(ЕСТЬNULL(Продажи.Стоимость, 0)) КАК СтоимостьПродажи
ИЗ
РегистрНакопления.Закупки КАК Закупки
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи КАК Продажи
ПО Закупки.Номенклатура = Продажи.Номенклатура
И Продажи.Период МЕЖДУ &д1 И &д2
ГДЕ
Закупки.Контрагент = &ВыбКонтр
И Закупки.Период МЕЖДУ &д1 И &д2
СГРУППИРОВАТЬ ПО
Закупки.Номенклатура,
Продажи.Номенклатура
В этом случае отбор по периоду продаж выполняется в момент соединения. Если продажи за указанный период отсутствуют, строка закупки не отбрасывается, а поля продаж заполняются значением NULL, которое обрабатывается функцией ЕСТЬNULL.
Обращение к физическим таблицам регистров накопления напрямую является неоптимальным. Для получения итогов за период следует использовать виртуальные таблицы Обороты. Отборы по периоду и контрагенту передаются непосредственно в параметры виртуальных таблиц.
ВЫБРАТЬ
ЗакупкиОбороты.Номенклатура КАК Номенклатура,
ЗакупкиОбороты.КоличествоОборот КАК КоличествоЗакупки,
ЗакупкиОбороты.СтоимостьОборот КАК СтоимостьЗакупки,
ЕСТЬNULL(ПродажиОбороты.КоличествоОборот, 0) КАК КоличествоПродажи,
ЕСТЬNULL(ПродажиОбороты.СтоимостьОборот, 0) КАК СтоимостьПродажи
ИЗ
РегистрНакопления.Закупки.Обороты(&д1, &д2, , Контрагент = &ВыбКонтр) КАК ЗакупкиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(&д1, &д2, , ) КАК ПродажиОбороты
ПО ЗакупкиОбороты.Номенклатура = ПродажиОбороты.Номенклатура
УПОРЯДОЧИТЬ ПО
Номенклатура
Использование параметров виртуальных таблиц позволяет платформе 1С генерировать оптимальные SQL-запросы, существенно сокращая объем считываемых данных с сервера БД.
Прямое соединение таблиц движений без предварительной группировки содержит скрытую проблему: если по одной номенклатуре было 2 закупки и 3 продажи, соединение сформирует 6 строк (декартово произведение). В результате суммы закупок и продаж исказятся и умножатся на количество совпадений.
Чтобы избежать искажения данных, разберем по шагам правильную схему запроса с использованием временных таблиц:
ВЫБРАТЬ
ЗакупкиОбороты.Номенклатура КАК Номенклатура,
ЗакупкиОбороты.КоличествоОборот КАК Количество,
ЗакупкиОбороты.СтоимостьОборот КАК Стоимость
ПОМЕСТИТЬ ВТ_Закупки
ИЗ
РегистрНакопления.Закупки.Обороты(&д1, &д2, , Контрагент = &ВыбКонтр) КАК ЗакупкиОбороты
;
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.КоличествоОборот КАК Количество,
ПродажиОбороты.СтоимостьОборот КАК Стоимость
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.Продажи.Обороты(&д1, &д2, , ) КАК ПродажиОбороты
;
ВЫБРАТЬ
ВТ_Закупки.Номенклатура КАК Номенклатура,
ВТ_Закупки.Количество КАК КоличествоЗакупки,
ВТ_Закупки.Стоимость КАК СтоимостьЗакупки,
ЕСТЬNULL(ВТ_Продажи.Количество, 0) КАК КоличествоПродажи,
ЕСТЬNULL(ВТ_Продажи.Стоимость, 0) КАК СтоимостьПродажи
ИЗ
ВТ_Закупки КАК ВТ_Закупки
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Продажи КАК ВТ_Продажи
ПО ВТ_Закупки.Номенклатура = ВТ_Продажи.Номенклатура
УПОРЯДОЧИТЬ ПО
Номенклатура
Посмотрим на другие недочеты, встречающиеся в исходных запросах при работе со соединениями:
ВЫБРАТЬ РАЗЛИЧНЫЕ вместе с СГРУППИРОВАТЬ ПО: Конструкция СГРУППИРОВАТЬ ПО сама по себе обеспечивает уникажность группировочных полей. Использование РАЗЛИЧНЫЕ создает лишнюю нагрузку на сервер БД.Количество нельзя выносить в секцию СГРУППИРОВАТЬ ПО. Их необходимо сворачивать с помощью агрегатных функций СУММА(Количество), иначе результат будет разбиваться на отдельные строки при каждом изменении количества.ЕСТЬNULL: При выводе числовых показателей из левого соединения всегда применяйте функцию ЕСТЬNULL(Поле, 0), чтобы избежать пустых ячеек в отчете и ошибок при вычислении итогов.