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