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