Как в 1С соединить запрос с таблицей значений

Задача соединить запрос с таблицей значений возникает в 1С постоянно: нужно отфильтровать или обогатить данные информационной базы списком значений, подготовленным в коде — например, списком номенклатуры из файла, результатами другого запроса или данными, рассчитанными алгоритмом. Напрямую «присоединить» таблицу значений к тексту запроса нельзя: язык запросов работает только с таблицами базы данных и временными таблицами. Поэтому таблицу значений сначала передают в запрос как параметр, материализуют во временную таблицу, а затем соединяют её с обычными таблицами через LEFT JOIN, INNER JOIN или условие В.

Типовая схема выглядит так: в тексте запроса пишется конструкция «ВЫБРАТЬ ... ПОМЕСТИТЬ ВТ ИЗ &ТаблицаЗначений КАК ТЗ», затем временная таблица соединяется с нужными объектами метаданных. Запросу назначается менеджер временных таблиц, а сама таблица значений передаётся через УстановитьПараметр. Ниже разобраны все шаги, ограничения и типичные ошибки этого подхода.

Зачем соединять запрос с таблицей значений и когда это оправдано

Самые частые сценарии: фильтрация данных по списку, загруженному из Excel или полученному от пользователя; подбор остатков и цен только по нужной номенклатуре; сопоставление данных базы с внешними данными (например, выгрузкой из другой системы); передача в запрос результатов сложных расчётов, которые невозможно выразить на языке запросов. Во всех этих случаях таблица значений выступает как «входной фильтр» или источник дополнительных колонок.

Соединение через временную таблицу почти всегда выигрывает у перебора в цикле с запросом на каждой итерации: один запрос с соединением выполняется на сервере СУБД и использует индексы, тогда как сотни мелких запросов в цикле создают сетевую и серверную нагрузку. Однако если таблица значений содержит всего 2–3 строки, а отбор можно выразить простым условием В, передавать целую таблицу не обязательно — достаточно массива значений.

Шаг 1. Подготовка таблицы значений: типизация колонок

Главное требование к таблице значений, передаваемой в запрос, — явная типизация колонок через ОписаниеТипов. Если колонка создана без типа (например, Колонки.Добавить("Номенклатура") без второго параметра), платформа может не суметь вывести тип поля временной таблицы, и запрос завершится ошибкой или соединение будет работать некорректно. Тип должен совпадать с типом поля, с которым идёт соединение: для ссылки на справочник — СправочникСсылка.Номенклатура, для строки — Строка с указанной длиной, для чисел — Число с разрядностью.

Пример подготовки таблицы:

ТЗ = Новый ТаблицаЗначений;
ТЗ.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
ТЗ.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 3)));

НоваяСтрока = ТЗ.Добавить();
НоваяСтрока.Номенклатура = ЭлементСправочника;
НоваяСтрока.Количество = 10;

Дополнительно полезно проиндексировать колонки, по которым будет идти соединение: ТЗ.Индексы.Добавить("Номенклатура"). На больших таблицах это ускоряет обработку, хотя основную работу по оптимизации выполняет СУБД при материализации временной таблицы.

Шаг 2. Передача таблицы в запрос и создание временной таблицы

Таблица значений передаётся в запрос как обычный параметр. В тексте запроса первый пакет выбирает данные из параметра и помещает их во временную таблицу. Запросу обязательно назначается МенеджерВременныхТаблиц — без него временные таблицы будут удаляться сразу после выполнения пакета, и соединение окажется невозможным.

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

Результат = Запрос.Выполнить().Выгрузить();

1. 1
   Создайте таблицу значений и явно укажите типы всех колонок через ОписаниеТипов
2. 2
   Заполните таблицу данными и при необходимости добавьте индексы по полям соединения
3. 3
   Создайте запрос, назначьте ему новый МенеджерВременныхТаблиц
4. 4
   Передайте таблицу через УстановитьПараметр("ТЗ", ТЗ)
5. 5
   В первом пакете запроса выберите поля из &ТЗ и поместите их во временную таблицу
6. 6
   Во втором пакете соедините временную таблицу с таблицами базы через ЛЕВОЕ или ВНУТРЕННЕЕ СОЕДИНЕНИЕ
7. 7
   Выполните запрос и проверьте результат выборкой или выгрузкой

Шаг 3. Выбор типа соединения

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

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

Сравнение вариантов

ВНУТРЕННЕЕ СОЕДИНЕНИЕ

Подходит, когда нужны только совпадающие строки. Ограничение: строки из ТЗ без пары в базе теряются без следа.

ЛЕВОЕ СОЕДИНЕНИЕ

Подходит для сверки и обогащения списка: все строки ТЗ сохраняются. Требует ЕСТЬNULL для полей из правой таблицы.

Условие В (ВЫБРАТЬ ... ГДЕ Ссылка В (ВЫБРАТЬ ... ИЗ ВТ))

Подходит, когда ТЗ нужна только как фильтр без дополнительных колонок. Проще по записи, но не передаёт данные из ТЗ в результат.

Альтернатива: соединение без временной таблицы через параметр-массив

Если таблица значений нужна только для отбора по одному полю (типичный случай — список ссылок), соединение вообще не требуется. Достаточно выгрузить колонку в массив и использовать условие В: Запрос.УстановитьПараметр("Список", ТЗ.ВыгрузитьКолонку("Номенклатура")) и в тексте запроса «ГДЕ Номенклатура В (&Список)». Это проще, быстрее и не требует менеджера временных таблиц.

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

Маршрут решения

Как выбрать действие

Нужен только отбор по одному полю списком значенийИспользуйте массив и условие В (&Список) — без временной таблицы

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

Соединение идёт по двум и более полям одновременноТолько временная таблица с соединением по всем ключевым полям

ТЗ содержит десятки тысяч строкСократите ТЗ до нужных колонок, проиндексируйте ключи и проверьте план запроса на производительность

Производительность и ограничения

Передача таблицы значений в запрос означает физическое создание временной таблицы на сервере СУБД и загрузку в неё всех строк. Для таблиц в несколько тысяч строк это незаметно, но при десятках и сотнях тысяч строк время материализации становится ощутимым. Практические меры: передавайте только те колонки, которые реально используются в запросе; удаляйте дубли по ключевым полям (ТЗ.Свернуть), если дубликаты не нужны; соединяйте по индексированным полям таблиц базы.

Есть и ограничения платформы. В запрос нельзя передать таблицу значений с колонками типа ТаблицаЗначений, ХранилищеЗначения и некоторыми другими несериализуемыми типами. Поля с неограниченной длиной строки могут создавать проблемы при соединении — задавайте строкам конкретную длину. В запросах с итогами и в пакетных запросах следите за областью видимости временных таблиц: они живут, пока жив менеджер временных таблиц, и недоступны из другого запроса без общего менеджера.

Проверка результата и типичные ошибки

После выполнения запроса проверьте три вещи: количество строк результата (при левом соединении оно должно быть не меньше числа строк ТЗ, при внутреннем — не больше), отсутствие неожиданных NULL в ключевых полях и корректность типов колонок в выгрузке. Быстрый способ отладки — выполнить отдельный запрос «ВЫБРАТЬ * ИЗ ВТ_Данные» с тем же менеджером временных таблиц и убедиться, что временная таблица материализовалась правильно.

Контрольные точки

Как проверить результат

Количество строк результата при ЛЕВОМ СОЕДИНЕНИИ

Норма

Не меньше числа строк исходной ТЗ (или равно ей при уникальных ключах)

Тревожный признак

Строк меньше, чем в ТЗ — часть данных потеряна, вероятно использовано внутреннее соединение или дубли свёрнуты

Значения полей из таблиц базы

Норма

Числа вместо NULL благодаря ЕСТЬNULL

Тревожный признак

NULL в числовых колонках, ошибки при дальнейших расчётах

Найденные совпадения

Норма

Все ожидаемые позиции из ТЗ нашли пару в базе

Тревожный признак

Массовые NULL — проверьте совпадение типов и форматов ключевых полей

Часто задаваемые вопросы

Можно ли передать таблицу значений в запрос без временной таблицы?

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

Почему запрос выдаёт ошибку «Таблица не найдена: ВТ_Данные»?

Почти всегда причина в отсутствии менеджера временных таблиц: без него временная таблица уничтожается сразу после выполнения пакета, который её создал. Назначьте запросу Новый МенеджерВременныхТаблиц до выполнения.

Соединение не находит совпадений, хотя данные точно есть. Что проверить?

Сначала — типы полей: ссылка не сравнится со строкой, а строка длиной 50 может не совпасть со строкой другой длины в отдельных случаях. Затем — сами значения: лишние пробелы, регистр, разные представления. Выгрузите временную таблицу отдельным запросом и сравните значения глазами.

Можно ли использовать одну временную таблицу в нескольких запросах?

Да, если всем запросам назначен один и тот же объект МенеджерВременныхТаблиц. Это удобный приём: материализовать ТЗ один раз и переиспользовать её в серии запросов, не передавая параметр повторно.

Что быстрее: соединение с временной таблицей или цикл с запросами?

При любом заметном количестве строк соединение быстрее: один запрос выполняется на сервере СУБД с использованием индексов, тогда как цикл порождает отдельный запрос на каждую строку. Цикл оправдан только для единичных обращений или когда логика на каждой итерации не выражается запросом.

Как передать в запрос таблицу с колонкой составного типа?

Технически можно — укажите в ОписаниеТипов несколько типов. Но соединение по составному полю работает медленнее и хуже индексируется. Если возможно, приведите колонку к одному конкретному типу, совпадающему с типом поля соединения.