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