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

← На главную

При разработке отчетов или динамических списков программисты часто сталкиваются с ситуацией, когда вместо одной аккуратной строки с номенклатурой и данными по разным складам запрос выдает «лесенку» или десятки дублирующихся строк. Проблема становится особенно острой, когда к остаткам нужно добавить цены разных видов (например, оптовую, розничную и региональную). Давайте подробно разберем, почему это происходит и как правильно составить запрос, чтобы получить одну строку на каждую позицию номенклатуры.

Анализируем причину дублирования строк

Рассмотрим стандартную ситуацию: мы соединяем таблицу Справочник.Номенклатура с виртуальной таблицей остатков и виртуальной таблицей цен. Если у номенклатуры есть остатки на трех разных складах и установлены цены трех видов, то при обычном ЛЕВОЕ СОЕДИНЕНИЕ система создаст декартово произведение строк. Вместо того чтобы «схлопнуть» данные, 1С выводит все возможные комбинации «Склад — Вид цены».

Основная ошибка заключается в использовании полей Склад или ВидЦены в блоке СГРУППИРОВАТЬ ПО. Как только вы добавляете эти поля в группировку, система обязана разделить результат на разные строки для каждого уникального склада или типа цены. Чтобы этого избежать, нам нужно использовать метод «развертывания» данных (Pivot) через агрегатные функции.

Решение 1: Использование агрегатных функций с условием ВЫБОР

Этот метод позволяет собрать данные из разных строк в разные колонки одной строки. Вместо того чтобы группировать по складам, мы проверяем условие прямо внутри функции СУММА или МАКСИМУМ.

Рассмотрим пример запроса, который собирает остатки по трем разным регионам (Магнитогорск, Челябинск, Оренбург) и соответствующие им цены:


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

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

Важный нюанс: Обратите внимание, что в СГРУППИРОВАТЬ ПО осталась только ссылка на номенклатуру. Поля складов и видов цен в группировку не входят, так как они обрабатываются внутри условий ВЫБОР.

Решение 2: Использование временных таблиц (оптимальный подход)

Если база данных большая, прямое соединение справочника с виртуальными таблицами в одном запросе может работать медленно. Проанализируем более профессиональный подход — использование временных таблиц. Это позволит сначала подготовить данные, а затем быстро их соединить.

Разделим задачу на этапы:

Сначала получим остатки по нужным складам и поместим их во временную таблицу, сразу сгруппировав по номенклатуре.
Затем получим цены и также сохраним их во временную таблицу.
В финальном запросе соединим справочник с этими таблицами.

Посмотрим на пример реализации такого пакетного запроса:


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

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

// Шаг 3: Итоговая сборка
ВЫБРАТЬ
    Спр.Ссылка КАК Ссылка,
    ЕСТЬNULL(ВТ_Остатки.КолвоМагнитогорск, 0) КАК КолвоМагнитогорск,
    ЕСТЬNULL(ВТ_Остатки.КолвоЧелябинск, 0) КАК КолвоЧелябинск,
    ЕСТЬNULL(ВТ_Цены.ЦенаМагнитогорск, 0) КАК ЦенаМагнитогорск
ИЗ
    Справочник.Номенклатура КАК Спр
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК ВТ_Остатки ПО Спр.Ссылка = ВТ_Остатки.Номенклатура
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Цены КАК ВТ_Цены ПО Спр.Ссылка = ВТ_Цены.Номенклатура
ГДЕ
    НЕ Спр.ЭтоГруппа

Использование параметров виртуальных таблиц (например, Склад В (&ВсеНужныеСклады)) критически важно для производительности. Это заставляет СУБД фильтровать данные еще до того, как они попадут в оперативную память для обработки запросом.

Особенности работы с NULL и пустыми значениями

Выясним причину, почему иногда в колонках вместо цифр отображается пустота или NULL. При использовании ЛЕВОЕ СОЕДИНЕНИЕ, если данных во второй таблице нет (например, товара нет на складе), 1С подставляет значение NULL. Чтобы результат всегда содержал число, используйте функцию ЕСТЬNULL(Значение, 0).

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

Проблема динамического списка и заголовка формы

Проанализируем ситуацию, когда после усложнения запроса (использования временных таблиц или пакетных запросов) в динамическом списке пропадает заголовок формы или перестает работать стандартный поиск. Это происходит из-за того, что система «теряет» связь с основной таблицей справочника.

Для исправления этой ситуации выполним следующие шаги:

В свойствах динамического списка найдите поле Основная таблица.
Явно укажите там Справочник.Номенклатура.
Убедитесь, что в итоговом запросе поле Ссылка из справочника выбрано первым и не подменяется ссылками из регистров.

Если заголовок все равно не восстанавливается, его можно задать программно в модуле формы при событии ПриСозданииНаСервере, установив свойство ЭтаФорма.Заголовок = "Справочник Номенклатура". Однако правильная настройка основной таблицы в 99% случаев решает проблему автоматически.

Использование СКД как альтернатива

Если ваша задача — создание отчета, а не формы списка, мы рекомендуем использовать Систему компоновки данных (СКД). В СКД не нужно писать сложные конструкции ВЫБОР КОГДА. Достаточно составить простой плоский запрос (Номенклатура, Склад, Остаток), а в настройках СКД выбрать тип отчета «Таблица». Поместив склады в колонки, вы получите автоматическое развертывание данных в одну строку без лишних усилий по программированию запроса.