В практике разработки на платформе 1С часто возникает задача фильтрации списка объектов на основании данных из подчиненных таблиц или справочников. Рассмотрим типичную ситуацию: у нас есть справочник Контрагенты и подчиненный ему справочник Посещения. В Посещениях фиксируется каждое визитирование магазина клиентом. Если клиент совершил покупку, в поле ДатаПолученияОплаты записывается дата. Если покупки не было — дата остается пустой (0001-01-01).
Сложность задачи заключается в том, что один и тот же контрагент может иметь несколько записей о посещениях. Например, три раза он приходил и ничего не покупал, а на четвертый раз совершил покупку. Нам необходимо полностью исключить такого контрагента из итогового списка. В выборку должны попасть только те клиенты, у которых ни в одной записи о посещениях нет заполненной даты оплаты.
Проанализируем несколько способов решения этой задачи в языке запросов 1С.
Это один из самых элегантных способов. Мы можем сгруппировать все записи по каждому контрагенту и найти самую позднюю дату оплаты. Если максимальная дата оплаты среди всех посещений конкретного клиента остается пустой, значит, этот клиент никогда ничего не покупал. Рассмотрим реализацию этого метода подробнее.
Нам потребуется вложенный запрос или соединение с таблицей, где данные уже сгруппированы:
ВЫБРАТЬ
Контрагенты.Ссылка КАК Ссылка
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
Посещения.Владелец КАК Владелец,
МАКСИМУМ(Посещения.ДатаПолученияОплаты) КАК МаксДатаОплаты
ИЗ
Справочник.Посещения КАК Посещения
СГРУППИРОВАТЬ ПО
Посещения.Владелец
) КАК ТаблицаМаксимальныхДат
ПО Контрагенты.Ссылка = ТаблицаМаксимальныхДат.Владелец
ГДЕ
ЕСТЬNULL(ТаблицаМаксимальныхДат.МаксДатаОплаты, ДАТАВРЕМЯ(1, 1, 1)) = ДАТАВРЕМЯ(1, 1, 1)
И НЕ Контрагенты.ПометкаУдаления
В данном примере мы сначала создаем выборку по справочнику Посещения, группируя её по владельцу (контрагенту). Функция МАКСИМУМ вернет нам либо самую позднюю дату оплаты, либо пустую дату, если оплат не было. Затем мы соединяем это со справочником Контрагенты и в условии ГДЕ отсекаем всех, у кого дата заполнена. Использование ЕСТЬNULL критически важно для тех контрагентов, у которых вообще нет записей в справочнике Посещения.
Рассмотрим альтернативный подход. Мы можем сначала получить список всех "плохих" контрагентов (тех, кто совершил хотя бы одну покупку), а затем исключить их из общего списка справочника Контрагенты. Этот метод интуитивно понятнее для новичков, хотя на огромных объемах данных он может работать чуть медленнее, чем прямое соединение.
Разберем пример реализации:
ВЫБРАТЬ
Контрагенты.Ссылка
ИЗ
Справочник.Контрагенты КАК Контрагенты
ГДЕ
НЕ Контрагенты.Ссылка В (
ВЫБРАТЬ РАЗЛИЧНЫЕ
Посещения.Владелец
ИЗ
Справочник.Посещения КАК Посещения
ГДЕ
Посещения.ДатаПолученияОплаты <> ДАТАВРЕМЯ(1, 1, 1)
)
И НЕ Контрагенты.ПометкаУдаления
Здесь во вложенном запросе мы выбираем только тех владельцев, у которых дата оплаты заполнена. Оператор РАЗЛИЧНЫЕ позволяет оптимизировать список, убирая дубли. Основной запрос выводит всех контрагентов, чья ссылка не входит в этот список "покупателей".
Этот метод часто называют "Анти-соединением". Мы пытаемся соединить контрагентов с теми записями посещений, где есть оплата. Если для контрагента такая запись не найдена, в полях присоединенной таблицы появится значение NULL. Именно эти записи нам и нужны.
Посмотрим на пример кода:
ВЫБРАТЬ
Контрагенты.Ссылка КАК Контрагент
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Посещения КАК Посещения
ПО Контрагенты.Ссылка = Посещения.Владелец
И Посещения.ДатаПолученияОплаты <> ДАТАВРЕМЯ(1, 1, 1)
ГДЕ
Посещения.Ссылка ЕСТЬ NULL
И НЕ Контрагенты.ПометкаУдаления
Обратите внимание: условие проверки даты оплаты вынесено в секцию ПО (условие связи), а не в ГДЕ. Это принципиально важно. Если перенести проверку даты в ГДЕ, запрос превратится во внутреннее соединение, и мы потеряем тех контрагентов, у которых вообще нет посещений. Проверяя Посещения.Ссылка ЕСТЬ NULL, мы оставляем только тех клиентов, для которых система не нашла ни одной записи с заполненной датой оплаты.
При работе с подобными запросами следует учитывать несколько аспектов:
0001-01-01 (или ДАТАВРЕМЯ(1, 1, 1) в запросе). Значение NULL появляется только в результате левого соединения, когда запись во второй таблице не найдена. Не путайте их при написании условий.Владелец в справочнике Посещения должно быть индексировано. В стандартных механизмах 1С для подчиненных справочников это происходит автоматически, но если вы используете произвольный реквизит для связи, проверьте наличие индекса в конфигураторе.ВТ_Покупатели), а затем использовать её для исключения в основном пакете. Это упростит отладку и позволит СУБД лучше оптимизировать план запроса.Рассмотрим, как это выглядит с применением временной таблицы:
// Помещаем покупателей во временную таблицу
ВЫБРАТЬ РАЗЛИЧНЫЕ
Посещения.Владелец КАК Ссылка
ПОМЕСТИТЬ ВТ_Исключаемые
ИЗ
Справочник.Посещения КАК Посещения
ГДЕ
Посещения.ДатаПолученияОплаты <> ДАТАВРЕМЯ(1, 1, 1);
////////////////////////////////////////////////////////////////////////////////
// Основная выборка
ВЫБРАТЬ
Контрагенты.Ссылка
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Исключаемые КАК ВТ_Исключаемые
ПО Контрагенты.Ссылка = ВТ_Исключаемые.Ссылка
ГДЕ
ВТ_Исключаемые.Ссылка ЕСТЬ NULL
И НЕ Контрагенты.ПометкаУдаления
Проанализировав вышеперечисленные способы, мы видим, что задача исключения записей может быть решена разными путями в зависимости от сложности структуры данных и требований к производительности. Использование временных таблиц и левого соединения с проверкой на NULL считается наиболее профессиональным подходом в разработке на платформе 1С.