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

← На главную

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

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

Метод 1: Свертка и агрегация во временной таблице

Этот метод был предложен в обсуждении как наиболее понятный. Суть его заключается в том, чтобы «схлопнуть» все операции по серийному номеру и присвоить каждой операции числовой вес. Например, для «Реализации» мы назначим вес +1, а для «Погашения» — вес -1.

Рассмотрим по шагам, как это реализовать:

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

Проанализируем пример кода для этого метода:


ВЫБРАТЬ
    ДвиженияСерийныхНомеров.СерияНоменклатуры КАК СерияНоменклатуры,
    СУММА(ВЫБОР
            КОГДА ДвиженияСерийныхНомеров.ХозяйственнаяОперация = &Реализация
                ТОГДА 1
            ИНАЧЕ -1
        КОНЕЦ) КАК БалансОпераций
ПОМЕСТИТЬ втСумма
ИЗ
    РегистрСведений.ДвиженияСерийныхНомеров КАК ДвиженияСерийныхНомеров
СГРУППИРОВАТЬ ПО
    ДвиженияСерийныхНомеров.СерияНоменклатуры;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    втСумма.СерияНоменклатуры,
    ДвиженияСерийныхНомеров.Номенклатура,
    ДвиженияСерийныхНомеров.Количество
ИЗ
    втСумма КАК втСумма
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДвиженияСерийныхНомеров КАК ДвиженияСерийныхНомеров
        ПО втСумма.СерияНоменклатуры = ДвиженияСерийныхНомеров.СерияНоменклатуры
ГДЕ
    втСумма.БалансОпераций > 0
    И ДвиженияСерийныхНомеров.ХозяйственнаяОперация = &Реализация

Важный нюанс: если в вашей системе возможно частичное погашение (например, по сумме или количеству), то вместо единиц лучше использовать ресурсы регистра (Сумма или Количество), умножая их на -1 в случае погашения. Тогда условие БалансОпераций > 0 покажет остаток, который еще не был погашен.

Метод 2: Использование Левого соединения и проверки на NULL (Анти-соединение)

Этот способ часто называют «классическим». Мы попробуем соединить таблицу «саму с собой». В левой части запроса мы выберем все реализации, а к правой части попытаемся присоединить погашения по тому же серийному номеру. Если погашение не найдется, поля правой таблицы примут значение NULL.

Разберем этот алгоритм подробнее:

Выбираем из регистра все записи с типом «Реализация».
Присоединяем этот же регистр (создаем алиас, например, Погашения).
В условии соединения указываем совпадение серийных номеров и тип операции «Погашение» для правой таблицы.
В секции ГДЕ отфильтровываем только те записи, где в правой таблице серийный номер ЕСТЬ NULL.

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


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

Этот метод обычно работает быстрее на больших объемах данных, так как оптимизатор СУБД эффективно обрабатывает такие соединения, особенно если поле СерияНоменклатуры проиндексировано.

Метод 3: Использование конструкции ИМЕЮЩИЕ (HAVING)

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

Выясним, как это работает на практике:


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

В данном примере условие ИМЕЮЩИЕ проверяет, что среди записей по данной серии хотя бы раз встречалась «Реализация», но ни разу не встретилось «Погашение».

Как сгруппировать результат по Номенклатуре, скрыв серии?

Часто в итоговом отчете пользователю не нужно видеть каждый серийный номер, а достаточно увидеть общее количество не погашенных сертификатов или товаров. Чтобы убрать детальные записи и получить результат вида «Номенклатура — Количество», выполните следующие действия:

Используйте любой из вышеописанных методов (например, с временной таблицей) для получения списка «зависших» серийных номеров.
В финальном запросе выберите поле Номенклатура и примените функцию СУММА(Количество).
Обязательно добавьте СГРУППИРОВАТЬ ПО только по тем полям, которые должны остаться в результате (например, Номенклатура).

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


ВЫБРАТЬ
    ДвиженияСерийныхНомеров.Номенклатура КАК Номенклатура,
    СУММА(ДвиженияСерийныхНомеров.Количество) КАК Количество
ИЗ
    втСумма КАК втСумма
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ДвиженияСерийныхНомеров КАК ДвиженияСерийныхНомеров
    ПО втСумма.СерияНоменклатуры = ДвиженияСерийныхНомеров.СерияНоменклатуры
ГДЕ
    втСумма.БалансОпераций > 0
    И ДвиженияСерийныхНомеров.ХозяйственнаяОперация = &Реализация
СГРУППИРОВАТЬ ПО
    ДвиженияСерийныхНомеров.Номенклатура

Полезный совет: Чтобы запрос работал максимально быстро, убедитесь, что поле СерияНоменклатуры является измерением регистра. В 1С измерения индексируются автоматически. Если же это реквизит, проверьте наличие признака «Индексировать» в свойствах метаданных конфигуратора.

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