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