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