Как сопоставить номенклатуру в накладных и найти расхождения в количестве в запросах 1С?

В процессе разработки в системе 1С программисты часто сталкиваются с необходимостью сопоставления данных между первичными и подчиненными документами. Типичный пример — проверка соответствия расходных накладных и созданных на их основании приходных накладных. Нам требуется вывести все позиции из расходного документа и сравнить их количество с приходом, чтобы наглядно увидеть расхождения (недопоставку или избыток).

Однако при составлении запроса "в лоб" начинающие разработчики часто сталкиваются с тем, что позиции номенклатуры, которых вообще не оказалось в приходном документе, просто исчезают из отчета. Давайте подробно разберем, почему так происходит, как решить эту задачу с помощью правильного типа соединения и как оптимизировать такой запрос до уровня стандартов профессиональной разработки 1С.

В чем причина исчезновения строк: разница между внутренним и левым соединением

Рассмотрим исходную ситуацию. Изначально в запросе автора связь между табличными частями расходной и приходной накладных была прописана в секции ГДЕ через перечисление таблиц через запятую:

ИЗ
    Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав,
    Документ.ПриходнаяНакладная.Состав КАК ПриходнаяНакладнаяСостав
ГДЕ
    ПриходнаяНакладнаяСостав.Ссылка.ДокОснование = РасходнаяНакладнаяСостав.Ссылка
    И РасходнаяНакладнаяСостав.Номенклатура = ПриходнаяНакладнаяСостав.Номенклатура

С точки зрения СУБД, перечисление таблиц через запятую с последующей фильтрацией в секции ГДЕ эквивалентно Внутреннему соединению (INNER JOIN). Фильтрация по равенству номенклатуры РасходнаяНакладнаяСостав.Номенклатура = ПриходнаяНакладнаяСостав.Номенклатура работает жестко: если определенного товара из расходной накладной вообще нет в табличной части приходной накладной, то СУБД не находит совпадений и полностью исключает эту строку из результирующей выборки.

Чтобы решить эту проблему, нам необходимо использовать ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN). Этот тип связи гарантирует, что в результат запроса попадут абсолютно все строки из левой таблицы (в нашем случае — из табличной части расходной накладной), независимо от того, нашлось ли для них соответствие в правой таблице (в приходе). Если соответствия нет, поля правой таблицы заполнятся значением NULL.

Решение базовой задачи с помощью ЛЕВОГО СОЕДИНЕНИЯ и функции ЕСТЬNULL

Давайте перепишем связь таблиц с использованием левого соединения. При этом важно помнить, что если товара нет в приходной накладной, то в поле ПриходнаяНакладнаяСостав.Количество вернется значение NULL. Математические операции с NULL всегда возвращают NULL (например, 10 - NULL = NULL), что приведет к пустой колонке разницы в отчете.

Для предотвращения этой ошибки мы применим функцию ЕСТЬNULL, которая заменяет значение NULL на указанный нами альтернативный вариант (в данном случае — на число 0). Посмотрим, как будет выглядеть расчет разницы количества:

РасходнаяНакладнаяСостав.Количество - ЕСТЬNULL(ПриходнаяНакладнаяСостав.Количество, 0) КАК Разница

Теперь свяжем наши таблицы через ключевое слово ЛЕВОЕ СОЕДИНЕНИЕ по двум условиям связи: равенству документов-оснований и равенству номенклатуры:

ИЗ
    Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав
    ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПриходнаяНакладная.Состав КАК ПриходнаяНакладнаяСостав
        ПО РасходнаяНакладнаяСостав.Ссылка = ПриходнаяНакладнаяСостав.Ссылка.ДокОснование
        И РасходнаяНакладнаяСостав.Номенклатура = ПриходнаяНакладнаяСостав.Номенклатура

Такой подход решает основную проблему автора: теперь все позиции из расходной накладной выводятся в отчет. Если товара нет в приходе, то разница рассчитывается как полное количество расхода минус 0, и мы видим правильный остаток к допоставке.

Критический анализ скрытых проблем: "размножение" строк и дублирование данных

Хотя предложенный на форуме вариант кода помог автору решить сиюминутную задачу, в нем кроется огромная логическая опасность, которая на реальной базе данных приведет к недостоверным отчетам. Эта проблема называется декартовым произведением при соединении табличных частей "в лоб".

Представьте ситуацию: в расходной накладной одна и та же номенклатура "Товар А" занесена двумя строками (например, с разной ценой, характеристиками или просто по ошибке менеджера) — 10 шт. и 5 шт. В созданной на её основании приходной накладной этот товар также занесен двумя строками — 8 шт. и 7 шт. При обычном левом соединении табличных частей по условию равенства номенклатуры СУБД соединит каждую строку расхода с каждой строкой прихода. Вместо двух строк в отчете мы получим четыре строки, а итоговые показатели количества и разницы неконтролируемо умножатся!

Чтобы избежать этой критической ошибки, мы должны обязательно предварительно сгруппировать данные по номенклатуре в разрезе документов, как для расходной, так и для приходной части. Рассмотрим, как это сделать правильно и профессионально.

Оптимальное решение с использованием Временных Таблиц (ВТ)

По правилам и стандартам разработки 1С, прямое соединение физических таблиц табличных частей документов (да еще и с обращением "через точку" к реквизитам шапки в условиях связи, например ПриходнаяНакладнаяСостав.Ссылка.ДокОснование) крайне не рекомендуется. Это приводит к созданию неоптимальных планов запросов в СУБД SQL и вызывает серьезное падение производительности на больших объемах данных.

Правильным архитектурным решением является использование механизма Временных таблиц. Давайте разберем этот процесс по шагам:

1. Выбираем и группируем расход: Создаем временную таблицу ВТ_Расход. Выбираем из состава расходной накладной документ, номенклатуру и сумму количества. Накладываем все необходимые фильтры (по проведению, контрагенту, датам) прямо на этом этапе, чтобы не тащить в память лишние данные.
2. Выбираем и группируем приход: Создаем временную таблицу ВТ_Приход. Выбираем из состава приходных накладных те данные, у которых документ-основание входит в состав документов из первой временной таблицы. Группируем данные по документу-основанию и номенклатуре.
3. Соединяем подготовленные таблицы: В финальном запросе соединяем две легкие, уже сгруппированные временные таблицы левым соединением.

Давайте посмотрим на реализацию этого профессионального подхода на языке запросов 1С:

ВЫБРАТЬ
    РасходнаяСостав.Ссылка КАК Документ,
    РасходнаяСостав.Номенклатура КАК Номенклатура,
    СУММА(РасходнаяСостав.Количество) КАК КоличествоРасход
ПОМЕСТИТЬ ВТ_РасходныеДокументы
ИЗ
    Документ.РасходнаяНакладная.Состав КАК РасходнаяСостав
ГДЕ
    РасходнаяСостав.Ссылка.Проведен = ИСТИНА
    И (&НеПроверятьДата ИЛИ РасходнаяСостав.Ссылка.Дата МЕЖДУ &ДатаНач И &ДатаКон)
    И (&НеПроверятьКонтрагент ИЛИ РасходнаяСостав.Ссылка.Контрагент = &СтруктурнаяЕдиница)
СГРУППИРОВАТЬ ПО
    РасходнаяСостав.Ссылка,
    РасходнаяСостав.Номенклатура
ИНДЕКСИРОВАТЬ ПО
    Документ,
    Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ПриходнаяСостав.Ссылка.ДокОснование КАК ДокументОснование,
    ПриходнаяСостав.Номенклатура КАК Номенклатура,
    СУММА(ПриходнаяСостав.Количество) КАК КоличествоПриход
ПОМЕСТИТЬ ВТ_ПриходныеДокументы
ИЗ
    Документ.ПриходнаяНакладная.Состав КАК ПриходнаяСостав
ГДЕ
    ПриходнаяСостав.Ссылка.Проведен = ИСТИНА
    И ПриходнаяСостав.Ссылка.ДокОснование В (ВЫБРАТЬ РАЗЛИЧНЫЕ ВТ.Документ ИЗ ВТ_РасходныеДокументы КАК ВТ)
СГРУППИРОВАТЬ ПО
    ПриходнаяСостав.Ссылка.ДокОснование,
    ПриходнаяСостав.Номенклатура
ИНДЕКСИРОВАТЬ ПО
    ДокументОснование,
    Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_Расход.Документ КАК Документ,
    ВТ_Расход.Номенклатура КАК Номенклатура,
    ВТ_Расход.КоличествоРасход КАК КоличествоРасход,
    ЕСТЬNULL(ВТ_Приход.КоличествоПриход, 0) КАК КоличествоПриход,
    ВТ_Расход.КоличествоРасход - ЕСТЬNULL(ВТ_Приход.КоличествоПриход, 0) КАК Разница
ИЗ
    ВТ_РасходныеДокументы КАК ВТ_Расход
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПриходныеДокументы КАК ВТ_Приход
        ПО ВТ_Расход.Документ = ВТ_Приход.ДокументОснование
        И ВТ_Расход.Номенклатура = ВТ_Приход.Номенклатура
ГДЕ
    ВТ_Расход.КоличествоРасход - ЕСТЬNULL(ВТ_Приход.КоличествоПриход, 0) <> 0

Почему этот запрос работает быстро и надежно?

Рассмотрим подробнее преимущества представленной схемы оптимизации запроса:

• Индексация временных таблиц: Мы явно прописали индексирование временных таблиц по полям связи (Документ и Номенклатура). Это позволяет СУБД выполнять сопоставление мгновенно даже на миллионах строк.
• Отсутствие дублей (СГРУППИРОВАТЬ ПО): Благодаря предварительной группировке по номенклатуре, в финальном соединении каждая номенклатура по одному документу встречается ровно один раз. Вероятность декартова произведения и удвоения сумм полностью исключена.
• Фильтрация на ранних этапах: В приходную таблицу попадают не вообще все приходные накладные из базы данных, а только те, которые связаны с отобранными по фильтру расходными накладными. Это минимизирует объем оперативной памяти, используемый запросом.

Важная ловушка условий в секции ГДЕ при ЛЕВОМ соединении

Обратите внимание на то, как накладываются условия на присоединяемую (правую) таблицу. Если бы мы захотели отфильтровать приходные документы (например, по проведению) и написали бы условие ГДЕ ПриходнаяСостав.Ссылка.Проведен = ИСТИНА в финальном запросе, то наше левое соединение моментально превратилось бы во внутреннее.

Правило разработки 1С: Любые фильтры, накладываемые на правую присоединяемую таблицу при левом соединении, должны находиться либо в условиях связи ПО, либо (что гораздо лучше и надежнее) данные должны быть отфильтрованы заранее при помещении во временную таблицу, как мы и сделали во втором пакете запроса (ВТ_ПриходныеДокументы).

Рекомендация по улучшению архитектуры кода (Избавление от жесткого кодирования)

В коде автора из сообщения 6 присутствует строка: Справочники.СтруктурныеЕдиницы.НайтиПоКоду("1"). Настоятельно рекомендуем избегать подобных конструкций в реальной разработке.

Поиск по коду или наименованию в коде 1С считается "жестким кодированием" (hardcode). Если в процессе эксплуатации базы данных пользователь или администратор случайно изменит код этой структурной единицы, переименует её или создаст новую базу путем переноса данных с другими кодами, программа перестанет работать.

Вместо этого используйте следующие паттерны проектирования:

1. Предопределенные элементы: Если эта структурная единица является базовой системной сущностью, сделайте её предопределенной в конфигураторе и обращайтесь к ней через Справочники.СтруктурныеЕдиницы.Основная.
2. Настройки пользователя: Выносите такие значения в константы, персональные настройки пользователя или параметры учета, чтобы пользователь мог самостоятельно выбрать нужную организацию или склад в режиме 1С:Предприятие без привлечения программиста.

Следуя этим простым правилам и используя левые соединения с предварительной группировкой во временных таблицах, вы сможете создавать быстрые, отказоустойчивые и легко читаемые алгоритмы сопоставления любых документов в системе 1С!