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