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