Как избежать задвоения записей в запросе 1С при сопоставлении по ФИО и СНИЛС

← На главную

При загрузке данных из внешних источников, таких как файлы Excel, программисты 1С часто сталкиваются с ситуацией, когда одно и то же физическое лицо необходимо найти в базе по нескольким признакам. Обычно это СНИЛС и ФИО. Однако прямолинейное использование оператора ИЛИ в условии связи запроса часто приводит к неприятному эффекту — задвоению записей. Если в базе и в файле есть люди с одинаковыми именами, но разными СНИЛС, запрос может выдать комбинации "каждый с каждым", порождая избыточные строки. Разберем, как правильно построить запрос, чтобы идентификация проходила корректно и без дублей.

Анализ причины возникновения дублей

Рассмотрим ситуацию, описанную автором: в исходной таблице есть две строки с одинаковыми ФИО, но разными СНИЛС. В справочнике ФизическиеЛица ситуация аналогичная. При использовании условия ПО (ФизическиеЛица.СНИЛС = Таблица.СНИЛС ИЛИ ФизическиеЛица.ФИО = Таблица.ФИО) система SQL выполняет соединение по любому из этих условий. Если СНИЛС не совпал, но совпало ФИО, запись все равно будет присоединена. В итоге на одну строку из файла может найтись два и более соответствия в базе, что превращает 2 записи в 4 или даже больше. Проанализируем, почему так происходит: оператор ИЛИ заставляет планировщик запросов выбирать все записи, подходящие хотя бы под один критерий, не учитывая их приоритетность.

Метод двойного левого соединения

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

Для реализации этого метода мы будем использовать функцию ЕСТЬNULL, которая позволит нам выбрать результат из первого соединения, а если он пуст — из второго. Посмотрим на пример реализации такого запроса:


ВЫБРАТЬ
    ВТ_Данные.ФИОФизЛица КАК ФИО_Источник,
    ЕСТЬNULL(ФизЛицаПоСНИЛС.Ссылка, ФизЛицаПоФИО.Ссылка) КАК ФизическоеЛицоСсылка,
    ЕСТЬNULL(ФизЛицаПоСНИЛС.ФИО, ФизЛицаПоФИО.ФИО) КАК ФИО_ИзБазы
ИЗ
    ВТ_АнтропометрическиеСведения КАК ВТ_Данные
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ФизическиеЛица КАК ФизЛицаПоСНИЛС
        ПО ВТ_Данные.СНИЛС = ФизЛицаПоСНИЛС.СтраховойНомерПФР
        
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ФизическиеЛица КАК ФизЛицаПоФИО
        ПО ВТ_Данные.ФИОФизЛица = ФизЛицаПоФИО.ФИО
            И ФизЛицаПоСНИЛС.Ссылка ЕСТЬ NULL

В данном примере второе соединение срабатывает только в том случае, если по первому (по СНИЛС) ничего не было найдено (условие ФизЛицаПоСНИЛС.Ссылка ЕСТЬ NULL). Это гарантирует, что мы не получим лишних строк, если человек уже идентифицирован по уникальному номеру.

Важность нормализации данных

Выясним причину, по которой СНИЛС может не сопоставляться. В справочнике 1С СНИЛС часто хранится с разделителями (тире и пробелы), в то время как в Excel он может быть записан как сплошное число. Проанализируем ситуацию: если в коде запроса указано сравнение СНИЛС = "", а в базе хранится пустая маска вида " - - ", сравнение не сработает.

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

Удалить из СНИЛС все нецифровые символы (пробелы, тире, точки).
Привести ФИО к единому регистру и удалить лишние пробелы в начале и конце строки.
Обработать "пустые" значения. Вместо тире или строки из пробелов лучше использовать пустую строку или NULL.

Реализация финального запроса с учетом всех нюансов

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


// Помещаем данные из файла во временную таблицу
ВЫБРАТЬ
    Таблица.Колонка1 КАК ФИОФизЛица,
    Таблица.Колонка10 КАК СНИЛС_Стр,
    // Другие колонки (Рост, РазмерОдежды и т.д.)
ПОМЕСТИТЬ ВТ_Исходник
ИЗ
    &Таблица КАК Таблица;

////////////////////////////////////////////////////////////////////////////////
// Основная выборка с приоритизацией поиска
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ЕСТЬNULL(ФизЛица1.Ссылка, ФизЛица2.Ссылка) КАК ФизическоеЛицоСсылка,
    ЕСТЬNULL(ФизЛица1.ФИО, ФизЛица2.ФИО) КАК ФИО,
    ВТ.ФИОФизЛица КАК ФИО_Файл,
    ВТ.СНИЛС_Стр КАК СНИЛС_Файл
ИЗ
    ВТ_Исходник КАК ВТ
        // Пытаемся найти по СНИЛС (самый надежный способ)
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ФизическиеЛица КАК ФизЛица1
        ПО ФизЛица1.СтраховойНомерПФР = ВТ.СНИЛС_Стр
        И ВТ.СНИЛС_Стр <> "" 
        И ВТ.СНИЛС_Стр <> "-"

        // Если не нашли по СНИЛС, ищем по ФИО
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ФизическиеЛица КАК ФизЛица2
        ПО ФизЛица2.ФИО = ВТ.ФИОФизЛица
        И ФизЛица1.Ссылка ЕСТЬ NULL

Важный момент: использование ключевого слова РАЗЛИЧНЫЕ помогает в случаях, когда в самом справочнике физических лиц уже существуют дубли (две полные карточки на одного человека с одинаковыми данными). Хотя это считается ошибкой ведения учета, в реальных базах такое встречается часто.

Дополнительные советы по поиску

Для еще более точной идентификации, если ФИО повторяются часто, рекомендуется добавить в условия поиска Дату рождения. Сочетание ФИО + ДатаРождения практически уникально и позволяет обойтись без СНИЛС в большинстве случаев. Рассмотрим, как изменится условие:


ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ФизическиеЛица КАК ФизЛица2
ПО ФизЛица2.ФИО = ВТ.ФИОФизЛица
    И ФизЛица2.ДатаРождения = ВТ.ДатаРождения
    И ФизЛица1.Ссылка ЕСТЬ NULL

Проанализировав ситуацию с дублями, мы пришли к выводу, что правильная архитектура запроса в 1С строится на принципе "от частного к общему". Сначала мы ищем по максимально уникальным полям, и только при отсутствии результата расширяем область поиска. Это не только избавляет от дублей, но и повышает производительность системы, так как SQL-серверу проще обрабатывать несколько простых соединений, чем одно сложное с множеством ИЛИ.