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