При разработке сложных отчетов и алгоритмов обработки кадровых данных в 1С разработчики часто сталкиваются с задачей: необходимо собрать список сотрудников по физическим лицам, сопоставить каждому виду занятости числовой приоритет (например, основное место работы — 1, внешнее совместительство — 2, внутреннее совместительство — 3) и отобрать ровно одну запись с минимальным значением приоритета во временную таблицу для дальнейших расчетов. При попытке добавить секцию УПОРЯДОЧИТЬ ПО в запрос создания временной таблицы конструктор запросов выдает ошибку или блокирует сохранение.
Разберем платформенные ограничения языка запросов 1С, выясним причину такого поведения и рассмотрим два проверенных способа решения этой задачи.
В языке запросов платформы 1С:Предприятие действуют строгие правила относительно использования конструкции УПОРЯДОЧИТЬ ПО:
ПОМЕСТИТЬ) или во вложенном запросе. СУБД оперирует нереляционными множествами, и физический порядок строк в таблице без явного запроса не имеет смысла.ПЕРВЫЕ: Сортировка разрешена во временных таблицах только тогда, когда в секции выборки указано ключевое слово ПЕРВЫЕ N. В этом случае СУБД сначала производит сортировку, а затем отсекает ровно N записей.ПЕРВЫЕ 1 отбирает одну запись из всего результата запроса целиком, а не по одной записи для каждого физического лица (в языке запросов 1С отсутствуют оконные функции вроде ROW_NUMBER() OVER (...)).Если перед нами стоит задача выбрать строго одну запись из всего справочника с наивысшим приоритетом (или по одному конкретному физическому лицу, переданному в параметры запроса), мы можем использовать конструкцию ВЫБРАТЬ ПЕРВЫЕ совместно с УПОРЯДОЧИТЬ ПО.
Рассмотрим пример запроса, помещающего одну первую строку во временную таблицу:
ВЫБРАТЬ ПЕРВЫЕ 1
Сотрудники.Ссылка КАК Сотрудник,
ВЫБОР
КОГДА Сотрудники.ВидЗанятости = ЗНАЧЕНИЕ(Перечисление.ВидыЗанятостиВОрганизации.ОсновноеМестоРаботы)
ТОГДА 1
КОГДА Сотрудники.ВидЗанятости = ЗНАЧЕНИЕ(Перечисление.ВидыЗанятостиВОрганизации.Совместительство)
ТОГДА 2
ИНАЧЕ 3
КОНЕЦ КАК Приоритет
ПОМЕСТИТЬ ВтОдинСотрудник
ИЗ
Справочник.СотрудникиОрганизаций КАК Сотрудники
ГДЕ
Сотрудники.Физлицо = &Физлицо
УПОРЯДОЧИТЬ ПО
Приоритет ИЕРАРХИЯ,
Сотрудники.Ссылка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВтОдинСотрудник.Сотрудник КАК Сотрудник,
ВтОдинСотрудник.Приоритет КАК Приоритет
ИЗ
ВтОдинСотрудник КАК ВтОдинСотрудник
;
////////////////////////////////////////////////////////////////////////////////
УНИЧТОЖИТЬ ВтОдинСотрудник
Посмотрим на логику выполнения: запрос сначала вычисляет поле Приоритет, затем упорядочивает строки по возрастанию веса и отсекает первую запись, помещая ее в таблицу ВтОдинСотрудник.
Когда требуется обработать массив данных по всем физическим лицам одновременно и получить ровно по одной строке на каждое физлицо, использование конструкции ПЕРВЫЕ 1 не подходит. В этом случае применяется классический паттерн группировки с агрегатной функцией МИНИМУМ() и последующим соединением.
Разберем по шагам реализацию этого паттерна:
ВЫБОР КОГДА ... ТОГДА ... КОНЕЦ.Физлицо и МИНИМУМ(Приоритет) с группировкой по физическому лицу.Физлицо и Приоритет).МИНИМУМ(Сотрудник) для исключения задвоения строк.Проанализируем готовый текст пакетного запроса:
ВЫБРАТЬ
Сотрудники.Физлицо КАК Физлицо,
Сотрудники.Ссылка КАК Сотрудник,
ВЫБОР
КОГДА Сотрудники.ВидЗанятости = ЗНАЧЕНИЕ(Перечисление.ВидыЗанятостиВОрганизации.ОсновноеМестоРаботы)
ТОГДА 1
КОГДА Сотрудники.ВидЗанятости = ЗНАЧЕНИЕ(Перечисление.ВидыЗанятостиВОрганизации.Совместительство)
ТОГДА 2
ИНАЧЕ 3
КОНЕЦ КАК Приоритет
ПОМЕСТИТЬ ВтВсеСотрудники
ИЗ
Справочник.СотрудникиОрганизаций КАК Сотрудники
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВтВсеСотрудники.Физлицо КАК Физлицо,
МИНИМУМ(ВтВсеСотрудники.Приоритет) КАК МинПриоритет
ПОМЕСТИТЬ ВтМинимальныеПриоритеты
ИЗ
ВтВсеСотрудники КАК ВтВсеСотрудники
СГРУППИРОВАТЬ ПО
ВтВсеСотрудники.Физлицо
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВтВсеСотрудники.Физлицо КАК Физлицо,
МИНИМУМ(ВтВсеСотрудники.Сотрудник) КАК Сотрудник,
ВтВсеСотрудники.Приоритет КАК Приоритет
ПОМЕСТИТЬ ВтИтогПоФизлицам
ИЗ
ВтВсеСотрудники КАК ВтВсеСотрудники
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВтМинимальныеПриоритеты КАК ВтМинимальныеПриоритеты
ПО ВтВсеСотрудники.Физлицо = ВтМинимальныеПриоритеты.Физлицо
И ВтВсеСотрудники.Приоритет = ВтМинимальныеПриоритеты.МинПриоритет
СГРУППИРОВАТЬ ПО
ВтВсеСотрудники.Физлицо,
ВтВсеСотрудники.Приоритет
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВтИтогПоФизлицам.Физлицо КАК Физлицо,
ВтИтогПоФизлицам.Сотрудник КАК Сотрудник,
ВтИтогПоФизлицам.Приоритет КАК Приоритет
ИЗ
ВтИтогПоФизлицам КАК ВтИтогПоФизлицам
;
////////////////////////////////////////////////////////////////////////////////
УНИЧТОЖИТЬ ВтВсеСотрудники;
УНИЧТОЖИТЬ ВтМинимальныеПриоритеты;
УНИЧТОЖИТЬ ВтИтогПоФизлицам
Если запрос выполняется в рамках внешней обработки или алгоритма проведения, где объем выборки небольшой, можно получить все строки без группировок, выгрузить результат в объект ТаблицаЗначений, применить метод Сортировать() и затем программно свернуть коллекцию:
ТаблицаРезультат = Запрос.Выполнить().Выгрузить();
ТаблицаРезультат.Сортировать("Физлицо Возр, Приоритет Возр");
// Свертка или программный обход строк с сохранением только первой записи по физлицу
ПредыдущееФизлицо = Неопределено;
Индекс = 0;
Пока Индекс < ТаблицаРезультат.Количество() Цикл
Строка = ТаблицаРезультат[Индекс];
Если Строка.Физлицо = ПредыдущееФизлицо Тогда
ТаблицаРезультат.Удалить(Строка);
Иначе
ПредыдущееФизлицо = Строка.Физлицо;
Индекс = Индекс + 1;
КонецЕсли;
КонецЦикла;
Важное замечание: При работе с большими массивами данных предпочтительнее использовать вариант с агрегатной функцией МИНИМУМ() внутри пакета запросов, так как расчет на стороне СУБД выполняется значительно быстрее и не создает избыточной нагрузки на оперативную память сервера приложений 1С.