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