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

← На главную

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

Давайте проанализируем ситуацию на конкретном примере: у нас есть первая временная таблица со списком номенклатуры (например, деталей), и вторая таблица — справочник спецификаций. Нам нужно получить только те спецификации, в которых присутствуют все детали из первой таблицы. Разберем по шагам, как правильно и оптимально реализовать такой запрос в 1С.

Строгое и нестрогое реляционное деление

Прежде чем писать код, давайте определимся с логикой задачи. Рассмотрим два возможных варианта отбора:

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

Для строгого деления нам потребуется дополнительно сравнивать общее количество строк в спецификации с количеством искомых элементов. В большинстве практических задач применяется именно нестрогое деление, которое мы и рассмотрим подробнее.

Решение 1: Метод левого соединения и подсчета совпадений

Первый вариант решения, к которому часто приходят разработчики — это использование левого соединения и подсчет найденных строк с помощью функции ВЫБОР. Посмотрим на пример рабочего кода, решающего эту задачу:


ВЫБРАТЬ РАЗРЕШЕННЫЕ 
    Номенклатура.Ссылка КАК Номенклатура 
ПОМЕСТИТЬ ВТ_Комплектующие 
ИЗ 
    Справочник.Номенклатура КАК Номенклатура 
ГДЕ 
    Номенклатура.Ссылка В(&Комплектующие) 
СГРУППИРОВАТЬ ПО 
    Номенклатура.Ссылка; 

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

ВЫБРАТЬ 
    ВТ_КомплектующиеОтбор.Спецификация КАК Спецификация 
ИЗ 
    ВТ_КомплектующиеОтбор КАК ВТ_КомплектующиеОтбор 
ГДЕ 
    ВТ_КомплектующиеОтбор.КоличествоНоменклатуры = ВТ_КомплектующиеОтбор.Номенклатура

Давайте разберем по шагам логику этого запроса. Мы берем таблицу спецификаций и левым соединением присоединяем к ней таблицу искомых комплектующих. Если деталь найдена, мы прибавляем 1 к счетчику КоличествоНоменклатуры. Затем мы группируем результат по спецификации и на последнем этапе отбираем те спецификации, где счетчик найденных деталей равен общему количеству деталей в искомом списке.

Решение 2: Классический паттерн через условие ИМЕЮЩИЕ (HAVING)

Хотя первый вариант работает, архитектура платформы 1С позволяет написать этот запрос гораздо лаконичнее и элегантнее. Вместо сложных проверок на NULL мы можем использовать внутреннее соединение и конструкцию ИМЕЮЩИЕ.

Посмотрим на пример оптимального запроса:


ВЫБРАТЬ 
    Комплектующие.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_ИскомаяНоменклатура
ИЗ
    &ТаблицаКомплектующих КАК Комплектующие;

ВЫБРАТЬ
    КОЛИЧЕСТВО(*) КАК Кол
ПОМЕСТИТЬ ВТ_КоличествоИскомых
ИЗ
    ВТ_ИскомаяНоменклатура;

ВЫБРАТЬ
    Спецификации.Ссылка КАК Спецификация
ИЗ
    Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК Спецификации
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ИскомаяНоменклатура КАК Искомая
        ПО Спецификации.Номенклатура = Искомая.Номенклатура
СГРУППИРОВАТЬ ПО
    Спецификации.Ссылка
ИМЕЮЩИЕ
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Спецификации.Номенклатура) = (ВЫБРАТЬ Кол ИЗ ВТ_КоличествоИскомых)

Проанализируем этот код. Мы сразу отсекаем лишние спецификации с помощью внутреннего соединения. Затем группируем оставшиеся данные по ссылке на спецификацию. Самая главная магия происходит в секции ИМЕЮЩИЕ: мы проверяем, чтобы количество уникальных найденных номенклатурных позиций внутри этой спецификации равнялось общему количеству искомых позиций. Если числа совпадают, значит спецификация содержит весь необходимый набор.

Важные нюансы и оптимизация производительности

При решении задач реляционного деления обязательно учитывайте следующие моменты:

Используйте КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...): В табличной части спецификации одна и та же номенклатура может быть указана несколько раз (например, на разных этапах производства). Если использовать простое КОЛИЧЕСТВО() или СУММА(1), система посчитает две одинаковые строки как две разные детали. Из-за этого условие равенства сработает ошибочно. Конструкция КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Номенклатура) гарантирует подсчет только уникальных совпадений.
Не забывайте про индексы: Если база содержит десятки тысяч спецификаций, соединение таблиц без индексов вызовет серьезные проблемы с производительностью. При создании временной таблицы ВТ_ИскомаяНоменклатура обязательно добавляйте команду ИНДЕКСИРОВАТЬ ПО Номенклатура.
Метод двойного отрицания: В классическом SQL подобные задачи иногда решаются через поиск спецификаций, для которых "не существует" требуемой номенклатуры, которая "отсутствовала бы" в документе. В 1С такой подход читается крайне тяжело и работает медленнее, поэтому метод с группировкой и ИМЕЮЩИЕ является предпочтительным стандартом.

Таким образом, применяя правильную группировку и секцию ИМЕЮЩИЕ, мы получаем понятный, легко читаемый и быстрый код для отбора данных по полному совпадению списков.