Как вывести несколько видов цен номенклатуры в одну строку в запросе 1С 8.3

← На главную

При разработке отчетов или обработок в 1С:Управление торговлей 11.4 программисты часто сталкиваются с задачей: получить список товаров, их текущие остатки и сразу несколько видов цен (например, «Оптовая» и «Розничная») в одной строке. Стандартное обращение к виртуальной таблице СрезПоследних регистра сведений ЦеныНоменклатуры выдает по одной строке на каждый вид цены, что приводит к дублированию строк номенклатуры в результате запроса. Рассмотрим подробнее, как решить эту проблему несколькими профессиональными способами.

Причина возникновения дублей

Разберем ситуацию: если в запросе мы просто соединяем таблицу остатков с таблицей цен и указываем в условии два вида цен, система SQL честно возвращает нам декартово произведение или просто несколько записей на одну единицу товара. Группировка по полю ВидЦены не помогает «схлопнуть» данные, так как значения в колонке «Цена» и «Вид цены» разные. Наша задача — развернуть эти значения из строк в колонки.

Способ 1. Использование конструкции ВЫБОР...КОГДА (Pivot)

Это самый эффективный и лаконичный метод. Мы выбираем данные из регистра цен, но вместо прямой выдачи поля Цена, мы создаем вычисляемые поля для каждого нужного нам типа цен. Проанализируем пример кода:


ВЫБРАТЬ
    ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
    СвободныеОстатки.ВНаличииОстаток КАК Остаток,
    МАКСИМУМ(ВЫБОР
            КОГДА ЦеныНоменклатуры.ВидЦены = &ВидЦеныЗаказ
                ТОГДА ЦеныНоменклатуры.Цена
            ИНАЧЕ 0
        КОНЕЦ) КАК ЦенаЗаказ,
    МАКСИМУМ(ВЫБОР
            КОГДА ЦеныНоменклатуры.ВидЦены = &ВидЦеныРозница
                ТОГДА ЦеныНоменклатуры.Цена
            ИНАЧЕ 0
        КОНЕЦ) КАК ЦенаРозница
ИЗ
    РегистрНакопления.СвободныеОстатки.Остатки КАК СвободныеОстатки
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ВидЦены В (&СписокЦен)) КАК ЦеныНоменклатуры
        ПО СвободныеОстатки.Номенклатура = ЦеныНоменклатуры.Номенклатура
СГРУППИРОВАТЬ ПО
    ЦеныНоменклатуры.Номенклатура,
    СвободныеОстатки.ВНаличииОстаток

Почему это работает: функции агрегации (в данном случае МАКСИМУМ) позволяют нам объединить несколько строк в одну. Так как для конкретной номенклатуры и конкретного вида цены условие ВЫБОР сработает только один раз, функция МАКСИМУМ выберет именно это ненулевое значение цены, «схлопнув» результат в одну красивую строку.

Способ 2. Множественные левые соединения

Если видов цен немного (две или три), мы можем присоединить таблицу цен несколько раз, каждый раз задавая жесткое условие на конкретный вид цены в параметрах связи или виртуальной таблицы. Посмотрим на этот подход:


ВЫБРАТЬ
    Номенклатура.Ссылка КАК Номенклатура,
    ЕСТЬNULL(Остатки.ВНаличииОстаток, 0) КАК Остаток,
    ЕСТЬNULL(ЦенаЗаказ.Цена, 0) КАК ЦенаЗаказ,
    ЕСТЬNULL(ЦенаРозница.Цена, 0) КАК ЦенаРозница
ИЗ
    Справочник.Номенклатура КАК Номенклатура
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки.Остатки КАК Остатки
        ПО Номенклатура.Ссылка = Остатки.Номенклатура
        
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ВидЦены = &ВидЦеныЗаказ) КАК ЦенаЗаказ
        ПО Номенклатура.Ссылка = ЦенаЗаказ.Номенклатура
        
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.SрезПоследних(&Период, ВидЦены = &ВидЦеныРозница) КАК ЦенаРозница
        ПО Номенклатура.Ссылка = ЦенаРозница.Номенклатура
ГДЕ
    НЕ Номенклатура.ЭтоГруппа

В этом примере мы используем ЕСТЬNULL, чтобы избежать пустых значений, если цена на товар не установлена. Этот метод нагляден, но при большом количестве видов цен запрос станет громоздким и может начать работать медленнее.

Важные рекомендации по оптимизации

Работая с УТ 11.4, важно учитывать специфику архитектуры данных:

Параметры виртуальных таблиц: Всегда передавайте список нужных цен прямо в параметры виртуальной таблицы СрезПоследних. Условие ГДЕ в конце запроса работает гораздо медленнее, так как заставляет систему сначала получить все цены из базы, а потом отсеивать лишние.
Учет характеристик: В УТ 11.4 цены и остатки часто хранятся в разрезе характеристик. Если вы их не учитываете, данные могут удвоиться. Добавляйте Характеристика в условия соединения ПО.
Типизация параметров: При установке параметров запроса через встроенный язык, убедитесь, что вы передаете ссылки на справочник ВидыЦен, а не просто строки с названиями. Использование метода НайтиПоНаименованию допустимо только в редких случаях; лучше использовать предопределенные элементы или ссылки из настроек.

Пример правильной установки параметров в коде 1С:


СписокЦен = Новый Массив;
СписокЦен.Добавить(Справочники.ВидыЦен.НайтиПоНаименованию("Заказ"));
СписокЦен.Добавить(Справочники.ВидыЦен.НайтиПоНаименованию("Розница"));

Запрос.УстановитьПараметр("СписокЦен", СписокЦен);
Запрос.УстановитьПараметр("ВидЦеныЗаказ", СписокЦен[0]);
Запрос.УстановитьПараметр("ВидЦеныРозница", СписокЦен[1]);
Запрос.УстановитьПараметр("Период", ТекущаяДата());

Заключение

Выясним причину неудач многих новичков: попытка использовать метод ТаблицаЗначений.Свернуть() после выполнения запроса часто не дает результата, если в таблице оставлена колонка ВидЦены. Чтобы «схлопывание» произошло успешно, данные должны быть подготовлены еще на уровне SQL-запроса с использованием агрегатных функций или раздельных соединений. Рассмотренные нами методы позволяют получить чистый результат в одну строку, готовый для загрузки в другие системы или вывода в отчет.