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