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

Программист 1С v8.3 (Управляемые формы) 1С:ERP Управление предприятием Управленческий учет
← На главную

При разработке отчетов или обработок в 1С часто возникает задача объединения данных из нескольких источников, например, из разных табличных частей документов или справочников. При этом могут потребоваться дополнительные условия для выбора данных, например, если одно поле заполнено, использовать его, в противном случае — другое. Разберем, как эффективно решить такую задачу, объединив данные двух табличных частей с учетом условного выбора наименований и корректной обработки количества.

Предположим, у нас есть две табличные части. В первой табличной части (далее будем называть ее ТЧ1) присутствуют поля Наименование, НовоеНаименование и Количество. Во второй табличной части (ТЧ2) есть поля Наименование и Количество, но отсутствует поле НовоеНаименование. Наша цель — получить единый список, в котором:

  1. Будут присутствовать все строки из ТЧ1.
  2. Если в строке ТЧ1 поле НовоеНаименование заполнено, то именно оно должно отображаться в итоговом наименовании. В противном случае — отображается Наименование из ТЧ1.
  3. Затем к этому списку необходимо добавить строки из ТЧ2, которых нет в ТЧ1 (то есть, уникальные позиции из ТЧ2).
  4. Количество для строк из ТЧ1 берется из ТЧ1, а для добавляемых строк из ТЧ2 — из ТЧ2.

Для наглядности рассмотрим пример данных:

ТЧ1:


Наименование | НовоеНаименование | Кол
-------------|-------------------|----
Стол         | Стол123           | 1
Стул         |                   | 2
Табурет      |                   | 1

ТЧ2:


Наименование | Кол
-------------|----
Стол         | 1
Стул         | 2

Ожидаемый результат должен выглядеть примерно так:


Наименование | Кол
-------------|----
Стол123      | 1
Стул         | 2
Табурет      | 1

Обратите внимание, что позиция "Стол" из ТЧ2 не добавляется, так как она уже есть в ТЧ1. Также, для "Стол" из ТЧ1 используется Стол123, а для "Стул" и "Табурет" — их основные наименования, поскольку НовоеНаименование не заполнено.

Различие между операторами СОЕДИНЕНИЕ и ОБЪЕДИНИТЬ в запросах 1С

Прежде чем перейти к решению, важно понять фундаментальную разницу между операторами СОЕДИНЕНИЕ (JOIN) и ОБЪЕДИНИТЬ (UNION) в языке запросов 1С.

  1. СОЕДИНЕНИЕ (JOIN): Этот оператор используется для горизонтального объединения данных из разных таблиц. Он добавляет колонки одной таблицы к колонкам другой таблицы на основе указанного условия связи. Например, если мы соединяем документ с его табличной частью, мы получаем расширенную строку, содержащую поля и из шапки, и из строки табличной части. Существуют различные типы соединений: ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN), ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN), ПРАВОЕ СОЕДИНЕНИЕ (RIGHT JOIN) и ПОЛНОЕ СОЕДИНЕНИЕ (FULL JOIN), которые определяют, как обрабатываются строки без совпадений.
  2. ОБЪЕДИНИТЬ (UNION): Этот оператор используется для вертикального объединения результатов нескольких выборок. Он добавляет строки одной выборки к строкам другой. Главное требование при использовании ОБЪЕДИНИТЬ (без ключевого слова ВСЕ) — это одинаковое количество колонок, их типы и порядок в каждой из объединяемых выборок. ОБЪЕДИНИТЬ автоматически удаляет дублирующиеся строки из итогового результата, тогда как ОБЪЕДИНИТЬ ВСЕ сохраняет все строки, включая дубликаты. В нашем случае, требование "если совпадения есть, то их нафиг" прямо указывает на использование ОБЪЕДИНИТЬ без ВСЕ.

Исходя из нашей задачи, где нам нужно взять все строки из одной "основной" таблицы и добавить к ним уникальные строки из другой таблицы, ОБЪЕДИНИТЬ является наиболее подходящим инструментом.

Пошаговое построение запроса

Рассмотрим, как мы будем строить запрос для решения поставленной задачи. Мы будем использовать конкретные объекты метаданных, как это было предложено в обсуждении на форуме: Документ.КоммерческоеПредложениеКлиенту.Товары (как первая табличная часть) и Справочник.РесурсныеСпецификации.МатериалыИУслуги (как вторая табличная часть).

Шаг 1: Выбираем данные из первой табличной части с условным наименованием

Сначала мы формируем выборку из первой табличной части. В этой выборке нам нужно решить вопрос с условным наименованием: если НовоеНаименование заполнено, используем его, иначе — стандартное Наименование. Также выбираем Количество и, для лучшей идентификации, Артикул номенклатуры.

Для условного выбора наименования мы используем конструкцию ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ... КОНЕЦ. Проверка на заполненность строкового поля может быть выполнена через сравнение с пустой строкой (""). Если поле НовоеНаименование может быть ссылочным типом и допускать NULL, то дополнительно можно использовать проверку ЕСТЬ NULL или ЗНАЧЕНИЕ(Справочник.ИмяСправочника.ПустаяСсылка).


ВЫБРАТЬ
    КоммерческоеПредложениеКлиентуТовары.Номенклатура КАК Номенклатура,
    КоммерческоеПредложениеКлиентуТовары.Номенклатура.Артикул КАК Артикул,
    КоммерческоеПредложениеКлиентуТовары.Количество КАК Количество,
    ВЫБОР
        КОГДА КоммерческоеПредложениеКлиентуТовары.НовоеНаименование ЕСТЬ NULL
            ТОГДА КоммерческоеПредложениеКлиентуТовары.Номенклатура.Наименование
        КОГДА КоммерческоеПредложениеКлиентуТовары.НовоеНаименование = ""
            ТОГДА КоммерческоеПредложениеКлиентуТовары.Номенклатура.Наименование
        ИНАЧЕ КоммерческоеПредложениеКлиентуТовары.НовоеНаименование
    КОНЕЦ КАК НаимКонтр
ИЗ
    Документ.КоммерческоеПредложениеКлиенту.Товары КАК КоммерческоеПредложениеКлиентуТовары
ГДЕ
    КоммерческоеПредложениеКлиентуТовары.Ссылка В
        (ВЫБРАТЬ
            ЗаказКлиента.ДокументОснование
        ИЗ
            Документ.ЗаказКлиента КАК ЗаказКлиента
        ГДЕ
            ЗаказКлиента.Ссылка = &Ссылка)

Здесь мы используем вложенный запрос для фильтрации строк табличной части КоммерческоеПредложениеКлиентуТовары по ссылке на документ-основание, который связан с нашим ЗаказКлиента. Это более оптимальный способ, чем ЛЕВОЕ СОЕДИНЕНИЕ всего документа, если нам нужны только строки из табличной части, связанные с конкретным документом.

Шаг 2: Выбираем данные из второй табличной части

Теперь мы формируем выборку из второй табличной части — Справочник.РесурсныеСпецификации.МатериалыИУслуги. Важный момент: эта табличная часть не содержит поля НовоеНаименование. Однако для использования оператора ОБЪЕДИНИТЬ количество и типы колонок в обеих выборках должны быть одинаковыми.

Поэтому мы должны "имитировать" поле НаимКонтр, присвоив ему пустое строковое значение, чтобы оно соответствовало колонке из первой выборки.


ВЫБРАТЬ
    РесурсныеСпецификацииМатериалыИУслуги.Номенклатура,
    РесурсныеСпецификацииМатериалыИУслуги.Номенклатура.Артикул,
    РесурсныеСпецификацииМатериалыИУслуги.КоличествоУпаковок,
    "" КАК НаимКонтр
ИЗ
    Справочник.РесурсныеСпецификации.МатериалыИУслуги КАК РесурсныеСпецификацииМатериалыИУслуги
ГДЕ
    РесурсныеСпецификацииМатериалыИУслуги.Ссылка.Основание = &Ссылка

В этой части мы выбираем Номенклатуру, Артикул, КоличествоУпаковок (это поле, содержащее количество в ресурсной спецификации) и добавляем пустую строку в качестве НаимКонтр.

Шаг 3: Объединяем две выборки

Теперь мы можем объединить две полученные выборки с помощью оператора ОБЪЕДИНИТЬ. Как было отмечено ранее, ОБЪЕДИНИТЬ без ВСЕ автоматически исключит дубликаты, что соответствует требованию "если совпадения есть, то их нафиг".


ВЫБРАТЬ
    КоммерческоеПредложениеКлиентуТовары.Номенклатура КАК Номенклатура,
    КоммерческоеПредложениеКлиентуТовары.Номенклатура.Артикул КАК Артикул,
    КоммерческоеПредложениеКлиентуТовары.Количество КАК Количество,
    ВЫБОР
        КОГДА КоммерческоеПредложениеКлиентуТовары.НовоеНаименование ЕСТЬ NULL
            ТОГДА КоммерческоеПредложениеКлиентуТовары.Номенклатура.Наименование
        КОГДА КоммерческоеПредложениеКлиентуТовары.НовоеНаименование = ""
            ТОГДА КоммерческоеПредложениеКлиентуТовары.Номенклатура.Наименование
        ИНАЧЕ КоммерческоеПредложениеКлиентуТовары.НовоеНаименование
    КОНЕЦ КАК НаимКонтр
ИЗ
    Документ.КоммерческоеПредложениеКлиенту.Товары КАК КоммерческоеПредложениеКлиентуТовары
ГДЕ
    КоммерческоеПредложениеКлиентуТовары.Ссылка В
        (ВЫБРАТЬ
            ЗаказКлиента.ДокументОснование
        ИЗ
            Документ.ЗаказКлиента КАК ЗаказКлиента
        ГДЕ
            ЗаказКлиента.Ссылка = &Ссылка)

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    РесурсныеСпецификацииМатериалыИУслуги.Номенклатура,
    РесурсныеСпецификацииМатериалыИУслуги.Номенклатура.Артикул,
    РесурсныеСпецификацииМатериалыИУслуги.КоличествоУпаковок,
    ""
ИЗ
    Справочник.РесурсныеСпецификации.МатериалыИУслуги КАК РесурсныеСпецификацииМатериалыИУслуги
ГДЕ
    РесурсныеСпецификацииМатериалыИУслуги.Ссылка.Основание = &Ссылка

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

Обработка NULL значений для количества

В некоторых случаях, особенно при использовании ПОЛНОЕ СОЕДИНЕНИЕ или когда данные могут отсутствовать в одной из таблиц, поля могут принимать значение NULL. Для числовых полей, таких как Количество, это может привести к некорректным расчетам. В таких ситуациях мы можем использовать функцию ЕСТЬNULL() или дополнительную конструкцию ВЫБОР КОГДА ... ЕСТЬ NULL ....

Например, если бы мы использовали ПОЛНОЕ СОЕДИНЕНИЕ, и нам нужно было бы взять количество из одной таблицы, если оно есть, или из другой, если в первой NULL, это выглядело бы так:


ВЫБРАТЬ
    // ... другие поля ...
    ЕСТЬNULL(КП.Количество, РесурсныеСпецификацииМатериалыИУслуги.КоличествоУпаковок) КАК Количество
ИЗ
    // ... ваше соединение ...

Или, более явно, с помощью ВЫБОР КОГДА, как было предложено в сообщении 7:


ВЫБРАТЬ
    // ... другие поля ...
    ВЫБОР
        КОГДА КП.Количество ЕСТЬ NULL
            ТОГДА РесурсныеСпецификацииМатериалыИУслуги.КоличествоУпаковок
        ИНАЧЕ КП.Количество
    КОНЕЦ КАК Количество
ИЗ
    // ... ваше соединение ...

В нашем случае с ОБЪЕДИНИТЬ, если мы гарантируем, что в каждой из объединяемых выборок поле Количество всегда будет заполнено (т.е., не NULL), то дополнительная обработка NULL может не потребоваться для итогового поля Количество. Однако, если КоличествоУпаковок в Справочник.РесурсныеСпецификации.МатериалыИУслуги может быть NULL, вам следует обернуть его в ЕСТЬNULL() в соответствующей части запроса.

Оптимизация запросов с использованием временных таблиц (ПОМЕСТИТЬ)

Для сложных запросов, включающих множество соединений, фильтраций или вычислений, может быть полезно использование временных таблиц с оператором ПОМЕСТИТЬ. Это позволяет разбить сложный запрос на несколько более простых шагов, выполняя промежуточные вычисления и сохраняя их результат во временной таблице, которую затем можно использовать в последующих частях запроса.

Например, вы могли бы сначала сформировать одну из частей объединения во временную таблицу, а затем использовать ее для дальнейшего объединения или соединения:


ВЫБРАТЬ
    КоммерческоеПредложениеКлиентуТовары.Номенклатура КАК Номенклатура,
    КоммерческоеПредложениеКлиентуТовары.Номенклатура.Артикул КАК Артикул,
    КоммерческоеПредложениеКлиентуТовары.Количество КАК Количество,
    ВЫБОР
        КОГДА КоммерческоеПредложениеКлиентуТовары.НовоеНаименование ЕСТЬ NULL
            ТОГДА КоммерческоеПредложениеКлиентуТовары.Номенклатура.Наименование
        КОГДА КоммерческоеПредложениеКлиентуТовары.НовоеНаименование = ""
            ТОГДА КоммерческоеПредложениеКлиентуТовары.Номенклатура.Наименование
        ИНАЧЕ КоммерческоеПредложениеКлиентуТовары.НовоеНаименование
    КОНЕЦ КАК НаимКонтр
ПОМЕСТИТЬ ПерваяТЧ_Подготовленная
ИЗ
    Документ.КоммерческоеПредложениеКлиенту.Товары КАК КоммерческоеПредложениеКлиентуТовары
ГДЕ
    КоммерческоеПредложениеКлиентуТовары.Ссылка В
        (ВЫБРАТЬ
            ЗаказКлиента.ДокументОснование
        ИЗ
            Документ.ЗаказКлиента КАК ЗаказКлиента
        ГДЕ
            ЗаказКлиента.Ссылка = &Ссылка)
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ПерваяТЧ_Подготовленная.Номенклатура,
    ПерваяТЧ_Подготовленная.Артикул,
    ПерваяТЧ_Подготовленная.Количество,
    ПерваяТЧ_Подготовленная.НаимКонтр
ИЗ
    ПерваяТЧ_Подготовленная КАК ПерваяТЧ_Подготовленная

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    РесурсныеСпецификацииМатериалыИУслуги.Номенклатура,
    РесурсныеСпецификацииМатериалыИУслуги.Номенклатура.Артикул,
    РесурсныеСпецификацииМатериалыИУслуги.КоличествоУпаковок,
    ""
ИЗ
    Справочник.РесурсныеСпецификации.МатериалыИУслуги КАК РесурсныеСпецификацииМатериалыИУслуги
ГДЕ
    РесурсныеСпецификацииМатериалыИУслуги.Ссылка.Основание = &Ссылка

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

Дополнительные рекомендации и потенциальные проблемы

  1. Типы данных: При использовании ОБЪЕДИНИТЬ убедитесь, что соответствующие колонки в каждой выборке имеют совместимые типы данных. Например, если в одной выборке колонка Наименование имеет тип "Строка", а в другой — "Ссылка", вам, возможно, потребуется явно преобразовать ссылочный тип к строке с помощью функции ПРЕДСТАВЛЕНИЕ(). В нашем случае, так как Номенклатура и НовоеНаименование (если оно строковое) возвращают строковые представления или сами являются строками, проблем не возникает.
  2. Индексы: Для повышения производительности запросов, особенно тех, что используют соединения и фильтрацию, убедитесь, что используемые поля (например, Номенклатура.Артикул, поля для связи документов) индексированы в базе данных.
  3. Параметры запроса: Всегда используйте параметры запроса (например, &Ссылка) вместо конкатенации строк для передачи значений в запрос. Это предотвращает SQL-инъекции и улучшает производительность, позволяя 1С кэшировать планы выполнения запросов.
  4. Проверка на пустую ссылку: Если поле НовоеНаименование является ссылкой на справочник и может быть незаполненным, проверка НовоеНаименование ЕСТЬ NULL будет корректной. Если это строковое поле, то НовоеНаименование = "". В нашем примере мы комбинируем оба подхода для надежности, предполагая, что НовоеНаименование может быть строкой или ссылкой, которая конвертируется в строку.

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

Читайте также

5 материалов по теме
  1. 01 Как объединить две таблицы значений с помощью запроса при неизвестной структуре колонок
  2. 02 Как объединить две таблицы значений в одну в 1С?
  3. 03 Как соединить две выборки в запросе 1С, типизировать Таблицу Значений и исправить ошибки несовместимости типов данных
  4. 04 Как соединить две табличные части в 1С для сравнения заказанной и проданной номенклатуры
  5. 05 Как заполнить табличную часть данными из таблицы значений в 1С
← На главную