Как в запросе 1С исключить из результатов одного запроса данные другого запроса

← На главную

При разработке на платформе 1С:Предприятие 8 программисты часто сталкиваются с задачей фильтрации данных по принципу исключения. Типичный пример: необходимо выбрать все характеристики номенклатуры, у которых задан определенный "Размер", но при этом полностью отсутствуют записи о свойстве "Рост". Эта задача осложняется тем, что в регистре сведений ЗначенияСвойствОбъектов информация хранится в виде набора строк (модель Entity-Attribute-Value), и простая фильтрация в разделе ГДЕ не дает нужного результата.

В этой статье мы подробно разберем, как правильно реализовать такое исключение, проанализируем типичные ошибки и рассмотрим наиболее эффективные методы решения задачи.

Анализ проблемы: почему обычный фильтр не работает

Рассмотрим ситуацию, описанную в теме. В регистре ЗначенияСвойствОбъектов для одной характеристики (объекта) может быть несколько записей. Например:

Объект: "Характеристика 1", Свойство: "Размер", Значение: "48"
Объект: "Характеристика 1", Свойство: "Рост", Значение: "170"
Объект: "Характеристика 2", Свойство: "Размер", Значение: "48"

Если мы напишем запрос с условием ГДЕ Свойство.Наименование <> "Рост", система просто исключит вторую строку из выборки, но "Характеристика 1" все равно попадет в результат, так как первая строка (с размером) соответствует условию. Наша же цель — полностью убрать "Характеристику 1", так как у нее в принципе существует запись со свойством "Рост".

Метод 1: Использование временных таблиц и соединений (рекомендуемый)

Этот способ является наиболее наглядным и производительным. Мы разделим задачу на три этапа: отбор целевых объектов, отбор объектов-исключений и их сопоставление. Проанализируем правильный код, к которому пришли участники обсуждения:


ВЫБРАТЬ
    ЗначенияСвойствОбъектов.Объект КАК ХарактеристикаНоменклатуры,
    ЗначенияСвойствОбъектов.Свойство КАК Свойство
ПОМЕСТИТЬ ВТ_ОтобранныеПоРазмерам
ИЗ
    РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ГДЕ
    ЗначенияСвойствОбъектов.Свойство.Наименование = "Размер"
    И ЗначенияСвойствОбъектов.Значение.Наименование = &Размер
    И ЗначенияСвойствОбъектов.Объект ССЫЛКА Справочник.ХарактеристикиНоменклатуры
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ЗначенияСвойствОбъектов.Объект КАК ХарактеристикаНоменклатуры
ПОМЕСТИТЬ ВТ_ОтобранныеПоРосту
ИЗ
    РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ГДЕ
    ЗначенияСвойствОбъектов.Свойство.Наименование = "Рост"
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_ОтобранныеПоРазмерам.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры
ИЗ
    ВТ_ОтобранныеПоРосту КАК ВТ_ОтобранныеПоРосту
    ПРАВОЕ СОЕДИНЕНИЕ ВТ_ОтобранныеПоРазмерам КАК ВТ_ОтобранныеПоРазмерам
    ПО ВТ_ОтобранныеПоРосту.ХарактеристикаНоменклатуры = ВТ_ОтобранныеПоРазмерам.ХарактеристикаНоменклатуры
ГДЕ
    ВТ_ОтобранныеПоРосту.ХарактеристикаНоменклатуры ЕСТЬ NULL

Разберем, что происходит в этом запросе по шагам:

Первый запрос: Мы собираем во временную таблицу ВТ_ОтобранныеПоРазмерам все характеристики, у которых есть нужный нам размер. Это наш основной массив данных.
Второй запрос: Мы создаем список всех характеристик, у которых в принципе заполнено свойство "Рост". Это наш "черный список".
Третий запрос: Мы соединяем эти таблицы. Использование ПРАВОЕ СОЕДИНЕНИЕ (или ЛЕВОЕ СОЕДИНЕНИЕ, если поменять таблицы местами) позволяет нам сопоставить записи. Условие ГДЕ ВТ_ОтобранныеПоРосту.ХарактеристикаНоменклатуры ЕСТЬ NULL отсекает те строки, для которых нашлась пара в "черном списке".

Метод 2: Использование оператора НЕ В (Вложенный запрос)

Если данных не слишком много, можно использовать более компактный синтаксис с оператором НЕ В. Рассмотрим этот подход:


ВЫБРАТЬ
    ЗначенияСвойствОбъектов.Объект КАК ХарактеристикаНоменклатуры
ИЗ
    РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ГДЕ
    ЗначенияСвойствОбъектов.Свойство.Наименование = "Размер"
    И ЗначенияСвойствОбъектов.Значение.Наименование = &Размер
    И НЕ ЗначенияСвойствОбъектов.Объект В (
        ВЫБРАТЬ
            Т_Рост.Объект
        ИЗ
            РегистрСведений.ЗначенияСвойствОбъектов КАК Т_Рост
        ГДЕ
            Т_Рост.Свойство.Наименование = "Рост"
    )

Этот метод интуитивно понятен: "Выбрать все с размером X, которых НЕТ в списке тех, у кого есть рост". Однако стоит помнить, что на больших объемах данных (десятки и сотни тысяч записей) вложенные запросы с НЕ В могут работать медленнее, чем временные таблицы с индексами.

Важные замечания по оптимизации

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

1. Поиск по ссылке вместо наименования. В примерах выше используется Свойство.Наименование = "Размер". В реальной практике это считается плохим тоном. Правильнее передавать ссылку на свойство в параметрах: ЗначенияСвойствОбъектов.Свойство = &СвойствоРазмер. Поиск по ссылке (УИД) в базе данных выполняется на порядок быстрее, чем сравнение строк.

2. Индексация временных таблиц. Если вы используете первый метод и объектов в ВТ_ОтобранныеПоРазмерам много, обязательно добавьте индексацию:


ПОМЕСТИТЬ ВТ_ОтобранныеПоРазмерам
...
ИНДЕКСИРОВАТЬ ПО
    ХарактеристикаНоменклатуры

Это позволит СУБД мгновенно выполнять соединение таблиц.

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

Альтернативный подход: Группировка и условие ИМЕЮЩИЕ

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


ВЫБРАТЬ
    ЗначенияСвойствОбъектов.Объект КАК ХарактеристикаНоменклатуры
ИЗ
    РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ГДЕ
    (ЗначенияСвойствОбъектов.Свойство = &СвойствоРазмер И ЗначенияСвойствОбъектов.Значение = &ЗначениеРазмер)
    ИЛИ ЗначенияСвойствОбъектов.Свойство = &СвойствоРост
СГРУППИРОВАТЬ ПО
    ЗначенияСвойствОбъектов.Объект
ИМЕЮЩИЕ
    МАКСИМУМ(ВЫБОР КОГДА ЗначенияСвойствОбъектов.Свойство = &СвойствоРазмер ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) = 1
    И МАКСИМУМ(ВЫБОР КОГДА ЗначенияСвойствОбъектов.Свойство = &СвойствоРост ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) = 0

Выясним причину эффективности этого метода: он проходит по таблице регистра всего один раз. Мы выбираем строки, относящиеся либо к размеру, либо к росту, группируем их по объекту и проверяем условия: флаг "Размер" должен быть равен 1, а флаг "Рост" — 0.

Таким образом, мы рассмотрели три различных способа исключения данных из запроса. Выбор конкретного метода зависит от объема ваших данных и требований к читаемости кода. Для большинства задач в 1С наиболее сбалансированным решением остается использование временных таблиц и левого соединения с проверкой на NULL.