Как получить розничную и закупочную цены в разных колонках одного запроса 1С?

← На главную

При разработке складских и торговых отчетов часто требуется отобразить в одной строке не только остатки товаров, но и несколько видов цен одновременно — например, розничную и закупочную. Разберем, как правильно составить такой запрос к регистрам 1С, почему возникает ошибка с дублированием значений и как оптимизировать производительность выборки.

Причина ошибки дублирования цены

Проанализируем распространенную ситуацию: разработчик пытается вывести два типа цен, обращаясь к одной и той же виртуальной таблице РегистрСведений.ЦеныНоменклатуры.СрезПоследних, но задает псевдоним только для одной выборки или применяет условие по типу цен только к одной из колонок. В результате в обеих колонках отчета выводится одна и та же цена (например, розничная), либо закупочная цена не рассчитывается вовсе.

Кроме того, распространенной ошибкой является соединение таблиц по строковому полю Артикул вместо ссылки на справочник Номенклатура. Соединение всегда следует выполнять по ссылочным полям, так как поиск по строкам работает медленнее и может приводить к неоднозначным результатам.

Способ 1. Несколько левых соединений с виртуальной таблицей среза цен

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

Разберем по шагам текст запроса на встроенном языке 1С:


ТипЦенРозница = Справочники.ТипыЦенНоменклатуры.НайтиПоКоду("ЦБ000001");
ТипЦенЗакупка = Справочники.ТипыЦенНоменклатуры.НайтиПоКоду("000000001");

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

Запрос.УстановитьПараметр("КОД", МассивАртикулов);
Запрос.УстановитьПараметр("Дата", ТекущаяДата());
Запрос.УстановитьПараметр("ТипЦенРозница", ТипЦенРозница);
Запрос.УстановитьПараметр("ТипЦенЗакупка", ТипЦенЗакупка);

Способ 2. Получение нескольких цен через один срез с условной агрегацией

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

Разберем пример такого запроса:


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

Правила оптимизации и частые нюансы при работе с ценами

Для корректной работы запроса и высокой скорости формирования отчета обратим внимание на следующие важные моменты:

Передача даты в виртуальные таблицы: Если не передать параметр даты в первый параметр виртуальной таблицы СрезПоследних (оставив пустое место), платформа получит срез на текущий момент времени. Если остатки формируются на определенный момент в прошлом, цены также необходимо получать на ту же дату: ЦеныНоменклатуры.СрезПоследних(&Дата, ...).
Перенос условий из секции ГДЕ в параметры таблиц: Фильтрация в секции ГДЕ выполняется после соединения таблиц, что заставляет платформу считывать лишние данные. Все условия по номенклатуре и складам следует передавать напрямую в параметры виртуальных таблиц Остатки и СрезПоследних.
Обработка отсутствующих значений: При левом соединении для позиций, у которых не установлена цена, поле вернет NULL. Обязательно используем функцию ЕСТЬNULL(Поле, 0), чтобы избежать ошибок при арифметических операциях в отчете.
Учет характеристик номенклатуры: Если в конфигурации включен учет по характеристикам, регистр цен хранит отдельную запись для каждой комбинации товара и характеристики. В таком случае условие соединения необходимо дополнять равенством характеристик (И Товары.Характеристика = Цены.Характеристика), иначе запрос вернет декартово произведение строк.