Часто при разработке на платформе 1С возникает задача сопоставления данных из разных таблиц, когда одни условия связи являются строго обязательными, а другие — желательными (необязательными). Проанализируем типичную ситуацию: необходимо соединить данные из таблицы заказов (ВТ_ДаннЗаказа) с таблицей поставщика (ВТ_ДаннПоставщика). При этом совпадение по номенклатуре обязательно, а совпадение по количеству или цене — желательно, но не строго необходимо. Рассмотрим подробнее, почему классические подходы с логическими операторами не работают, и разберем по шагам правильные архитектурные решения.
Первое, что приходит на ум разработчику — написать условие связи через оператор ИЛИ (OR) или использовать конструкцию ВЫБОР КОГДА непосредственно в секции ПО. Рассмотрим пример, который часто пытаются использовать:
ВТ_ДаннЗаказа.НоменклатураЗаказано = ВТ_ДаннПоставщика.Номенклатура
И (ВТ_ДаннЗаказа.КоличествоЗаказано = ВТ_ДаннПоставщика.Количество
ИЛИ ВТ_ДаннЗаказа.ЦенаЗаказано = ВТ_ДаннПоставщика.Цена)
Или еще более сложный вариант, который автор темы пытался применить, используя огромный ВЫБОР КОГДА прямо в условии ПОЛНОЕ СОЕДИНЕНИЕ ... ПО. Выясним причину, почему так делать категорически не рекомендуется:
OR и CASE внутри условий JOIN. Оптимизатор не может построить эффективный план выполнения, что часто приводит к полному сканированию таблиц (Table Scan) и декартовому произведению (перемножению всех строк). Запрос начинает зависать.ИЛИ не сообщает базе данных, какая строка "лучше". Если одна строка поставщика совпадает по номенклатуре и цене, а другая — по номенклатуре, цене и количеству, база вернет обе строки, что приведет к задвоению данных.Посмотрим на проверенные на практике паттерны, которые помогают решить задачу приоритетного сопоставления правильно и без падения производительности.
Вместо того чтобы делать одно соединение с громоздким условием, мы соединяем исходную таблицу с таблицей поставщика несколько раз через ЛЕВОЕ СОЕДИНЕНИЕ. Каждое соединение будет отвечать за свой "уровень" совпадения.
Разберем по шагам:
Номенклатура + Количество + Цена.Номенклатура + Количество.Номенклатура + Цена.Номенклатура.В секции ВЫБРАТЬ мы используем функцию ЕСТЬNULL, чтобы последовательно забрать данные из первого успешного соединения:
ВЫБРАТЬ
Заказы.Номенклатура,
ЕСТЬNULL(Пост_Идеал.Цена,
ЕСТЬNULL(Пост_Количество.Цена,
ЕСТЬNULL(Пост_Цена.Цена, Пост_Номенклатура.Цена)
)
) КАК ИтоговаяЦена
ИЗ
ВТ_ДаннЗаказа КАК Заказы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДаннПоставщика КАК Пост_Идеал
ПО Заказы.Номенклатура = Пост_Идеал.Номенклатура
И Заказы.Количество = Пост_Идеал.Количество
И Заказы.Цена = Пост_Идеал.Цена
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДаннПоставщика КАК Пост_Количество
ПО Заказы.Номенклатура = Пост_Количество.Номенклатура
И Заказы.Количество = Пост_Количество.Количество
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДаннПоставщика КАК Пост_Цена
ПО Заказы.Номенклатура = Пост_Цена.Номенклатура
И Заказы.Цена = Пост_Цена.Цена
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДаннПоставщика КАК Пост_Номенклатура
ПО Заказы.Номенклатура = Пост_Номенклатура.Номенклатура
Этот метод генерирует простые условия связи, с которыми легко справляется оптимизатор СУБД.
Если условий много, каскадные соединения могут сделать запрос нечитаемым. Проанализируем альтернативный вариант — вычисление "рейтинга" совпадения. Этот метод отлично подходит, если нужно найти наиболее точную строку.
Делаем базовое соединение только по обязательному условию (Номенклатура), а затем рассчитываем вес с помощью ВЫБОР КОГДА:
ВЫБРАТЬ
Заказы.НомерСтрокиДокумента,
Заказы.Номенклатура,
Поставщик.Цена КАК ЦенаПоставщика,
ВЫБОР КОГДА Заказы.Количество = Поставщик.Количество ТОГДА 10 ИНАЧЕ 0 КОНЕЦ +
ВЫБОР КОГДА Заказы.Цена = Поставщик.Цена ТОГДА 5 ИНАЧЕ 0 КОНЕЦ КАК ВесСовпадения
ПОМЕСТИТЬ ВТ_СвязиСВесом
ИЗ
ВТ_ДаннЗаказа КАК Заказы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДаннПоставщика КАК Поставщик
ПО Заказы.Номенклатура = Поставщик.Номенклатура
Далее мы группируем эти данные по номеру строки документа, чтобы найти максимальный ВесСовпадения, и затем соединяем таблицу саму с собой по номеру строки и найденному максимальному весу, оставляя только самые подходящие записи поставщика.
Разберем еще один способ, который заключается в создании независимых выборок с явным указанием приоритета. Мы создаем несколько запросов, соединенных через ОБЪЕДИНИТЬ ВСЕ.
Приоритет = 1.Приоритет = 2.Приоритет = 3.Затем результат помещается во временную таблицу. Итоговым запросом мы выбираем записи, группируя их и применяя агрегатную функцию МИНИМУМ(Приоритет). Соединив результат с изначальной таблицей, мы отсекаем менее релевантные строки (те, чей приоритет больше минимального найденного).
Подведем итог: попытка решить задачу опциональных связей напрямую в секции ПО с помощью сложной логики — тупиковый путь в 1С. Всегда используйте архитектурные подходы: каскадные левые соединения, расчет весовых коэффициентов или разбиение на приоритетные блоки через объединение. Это обеспечит и правильный результат без дублей, и высокую скорость работы запроса на стороне СУБД.