Часто при работе со сложными пакетными запросами в 1С разработчики сталкиваются с неожиданной проблемой: при соединении нескольких временных таблиц итоговый результат содержит меньше строк, чем ожидалось. Кажется, что данные просто "режутся" или теряются. Давайте подробно разберем, почему это происходит и какие существуют способы решения этой задачи.
Проанализируем типичную ситуацию. У нас есть пакетный запрос, который последовательно формирует несколько временных таблиц. Например:
Когда мы соединяем эти две таблицы, чтобы получить единый отчет, в итоговой выборке мы видим только 5 строк. При этом нам нужно, чтобы в результате было 10 строк, как в первой таблице, а для тех договоров, по которым не было доходов, соответствующие поля были бы просто пустыми (NULL).
Вся суть проблемы кроется в неправильном понимании или применении типов соединений таблиц в языке запросов 1С. Основным инструментом здесь выступает ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN).
Как работает ЛЕВОЕ СОЕДИНЕНИЕ? Оно берет все записи из левой таблицы (той, что указана до слов ЛЕВОЕ СОЕДИНЕНИЕ) и для каждой из них пытается найти соответствующую запись в правой таблице по заданному условию связи.
Ключевой момент здесь — результат всегда будет базироваться на левой таблице. Если в левой таблице 5 строк, а в правой 10, то после левого соединения вы получите максимум 5 строк. Именно это и произошло в исходной проблеме автора.
Посмотрим на пример финального запроса, который приводил к потере строк:
ВЫБРАТЬ
Рез1.Сотрудник,
Рез2.Долг,
Рез2.Количество,
Рез1.Сумма
ИЗ
Рез1 КАК Рез1 // Временная таблица с 5 строками (доходы)
ЛЕВОЕ СОЕДИНЕНИЕ Рез2 КАК Рез2 // Временная таблица с 10 строками (долги)
ПО Рез1.Сотрудник = Рез2.Сотрудник;
Здесь левой таблицей является Рез1, в которой всего 5 строк. Запрос берет эти 5 строк и присоединяет к ним данные из Рез2. Остальные 5 строк из Рез2, для которых не нашлось соответствия в Рез1, просто игнорируются. Теперь, когда причина ясна, рассмотрим способы решения.
Это самый простой и быстрый способ исправить ситуацию, который и помог автору вопроса. Если мы хотим получить все строки из таблицы, где их больше, мы должны сделать ее левой в соединении.
Давайте перепишем запрос, поменяв таблицы местами:
ВЫБРАТЬ
Рез2.Сотрудник,
Рез2.Долг,
Рез2.Количество,
Рез1.Сумма
ИЗ
Рез2 КАК Рез2 // Таблица с 10 строками теперь слева
ЛЕВОЕ СОЕДИНЕНИЕ Рез1 КАК Рез1 // Таблица с 5 строками теперь справа
ПО Рез1.Сотрудник = Рез2.Сотрудник;
Что изменилось? Теперь запрос возьмет все 10 строк из таблицы Рез2. Для тех 5 строк, по которым найдутся соответствия в Рез1, поле Сумма заполнится данными. Для оставшихся 5 строк, по которым соответствий нет, поле Сумма будет иметь значение NULL. Таким образом, мы получаем желаемый результат — 10 строк в итоговой выборке.
Этот способ более универсален и подходит для случаев, когда в обеих таблицах могут быть уникальные записи, которые мы хотим видеть в результате. Для этого используется ПОЛНОЕ СОЕДИНЕНИЕ (FULL OUTER JOIN).
Как работает ПОЛНОЕ СОЕДИНЕНИЕ? Оно включает в результат все записи из обеих таблиц.
NULL.NULL.Это идеальный вариант, когда нужно, например, вывести всех сотрудников, у которых есть либо долги, либо доходы, вне зависимости от наличия второго показателя.
Пример запроса с полным соединением:
ВЫБРАТЬ
ЕСТЬNULL(Рез1.Сотрудник, Рез2.Сотрудник) КАК Сотрудник,
Рез2.Долг,
Рез2.Количество,
Рез1.Сумма
ИЗ
Рез1 КАК Рез1
ПОЛНОЕ СОЕДИНЕНИЕ Рез2 КАК Рез2
ПО Рез1.Сотрудник = Рез2.Сотрудник;
Важный момент: при использовании ПОЛНОГО СОЕДИНЕНИЯ ключевое поле, по которому идет связь (в нашем случае `Сотрудник`), может оказаться NULL то из одной, то из другой таблицы. Поэтому для его вывода лучше использовать функцию ЕСТЬNULL(), чтобы гарантированно получить значение из той таблицы, где оно есть.
Существует распространенная ошибка, которая сводит на нет все преимущества левого соединения. Это наложение фильтра на поля правой таблицы в секции ГДЕ (WHERE).
Давайте проанализируем фрагмент одного из запросов автора:
...
ИЗ
Сотрудник КАК Сотрудник
ЛЕВОЕ СОЕДИНЕНИЕ РегистрБухгалтерии.Хозрасчетный.Остатки(...) КАК ХозрасчетныйОстатки
ПО (Сотрудник.Договор = ВЫРАЗИТЬ(ХозрасчетныйОстатки.Субконто2 КАК Справочник.ДоговорыКонтрагентов))
ГДЕ
Сотрудник.Период МЕЖДУ &НачалоПериода И &КонецПериода
И ВЫРАЗИТЬ(ХозрасчетныйОстатки.Субконто2 КАК Справочник.ДоговорыКонтрагентов) В (...)
...
Здесь к правой таблице ХозрасчетныйОстатки в секции ГДЕ применяется условие отбора по полю Субконто2. Вспомним, что если для строки из левой таблицы (Сотрудник) не нашлось соответствия в правой, все поля правой таблицы (включая ХозрасчетныйОстатки.Субконто2) заполняются значением NULL.
Условие ... ВЫРАЗИТЬ(NULL КАК ...) В (...) в секции ГДЕ вернет ЛОЖЬ. В результате все строки, для которых не нашлось пары в правой таблице, будут отфильтрованы из итогового результата. Таким образом, ЛЕВОЕ СОЕДИНЕНИЕ фактически превращается во ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN), и строки снова "режутся".
Решение: Все условия отбора, относящиеся к правой таблице, следует переносить из секции ГДЕ в условие связи ПО (ON) или применять их при формировании самой правой таблицы (например, в параметрах виртуальной таблицы или во вложенном запросе).
В завершение рассмотрим несколько общих рекомендаций, которые помогут не только решить проблему с потерей строк, но и ускорить выполнение сложных пакетных запросов.
ПОМЕСТИТЬ обязательно создавайте индексы по тем полям, которые будут использоваться для соединения с другими таблицами в секции ПО. Это кардинально ускоряет выполнение соединений на больших объемах данных..Остатки, .Обороты, .СрезПоследних) всегда эффективнее задавать в их параметрах, а не в секции ГДЕ. Это позволяет платформе 1С получить данные из базы наиболее оптимальным способом.Таким образом, внимательное отношение к типам соединений и месту расположения условий фильтрации поможет вам избежать "потери" данных и создавать корректные и быстрые запросы.