Сгруппировать в 1с в запросе
Язык запросов 1С 8 — незаменимый инструмент для программиста 1С, он позволяет писать более лаконичный, простой, понятный код, использовать меньшее количество ресурсов системы при работе с данными. Данная статья открывает серию уроков, посвященных языку запросов 1С 8. На первом уроке мы рассмотри структуру главного оператора данного языка — ВЫБРАТЬ. При помощи этого оператора можно создавать выборки из таблиц базы данных. Выбранные данные таблиц можно сортировать, накладывать на них условия, связывать и объединять с данными других таблиц, группировать по различным полям и много другое.
Язык запросов 1с предприятия 8 — Структура оператора ВЫБРАТЬ
Рассмотрим структуру оператора ВЫБРАТЬ (в квадратных скобках указаны необязательные части оператора). Язык запросов 1С предоставляет широкий набор инструментов для создания выборок данных.
Ключевые слова и блоки работы с полями
- ВЫБРАТЬ — ключевое слово обозначающее начало оператора;
- РАЗРЕШЕННЫЕ— указывает, что в выборку должны попадать записи таблиц, доступ на чтение к которым разрешен для данного пользователя;
- РАЗЛИЧНЫЕ— указывает, что в выборку должны попадать только различные (по всем полям) стоки. Иными словами, из выборки будут исключены повторяющиеся строки;
- ПЕРВЫЕ A— если указать данное ключевое слово, то в выборку попадут только первые A из выбранных запросом строк, где A — натуральное число;
- Блок полей — в данном блоке указываются поля, которые необходимо включить в выборку. Эти поля будут случить столбцами выборки. В самом простом случае поле выглядит следующим образом:
Ключевые слова и блоки работы с таблицами
- ПОМЕСТИТЬ ИмяВременнойТаблицы — ключевое слово ПОМЕСТИТЬ предназначено для создания временной таблицы с определенным именем, которая будет храниться в оперативной памяти в данном сеансе 1С 8, пока он не завершится или пока временная таблица не будет уничтожена. Следует заметить, что имена временных таблиц в одном сеансе 1С 8 не должны повторяться;
- Блок таблиц и связей — в блоке указываются все таблицы используемые в данном запросе, а также связи между ними. Начинается блок с ключевого слова ИЗ, за ним следует имя и псевдоним первой таблицы. Если данная таблица связана с другими таблицами, то указываются связи. Язык запросов 1С содержит следующий набор типов связей:
- ВНУТРЕННЕЕ СОЕДИНЕНИЕ — запись из левой таблицы попадет в выборку только при выполнении условия связи, запись из правой таблицы попадет в выборку только при выполнении условия связи;
- ЛЕВОЕ СОЕДИНЕНИЕ — запись из левой таблицы попадет в выборку в любом случае, запись из правой таблицы попадет в выборку только при выполнении условия связи;
- ПОЛНОЕ СОЕДИНЕНИЕ — запись из левой таблицы попадет в выборку сначала в любом случае, потом только при выполнении условия связи, запись из правой таблицы попадет в выборку сначала в любом случае, потом только при выполнении условия связи. При этом получившиеся в результате связи повторяющиеся строки исключаются из выборки.
Ключевые слова и блоки преобразования данных
- Блок группировок — данный блок служит для группировки строк таблицы. Строки объединяются в одну, если значения полей указанных после ключевого слова СГРУППИРОВАТЬ ПО оказываются одинаковыми. При этом все остальные поля суммируются, усредняются, максимизируются, либо минимизируются при помощи агрегатных функций. Агрегатные функции используют в блоке полей. Пример:
- Блок условий — в данном блоке после ключевого слова ГДЕ указываются условные выражения разделенные логическими операторами И, ИЛИ, для того чтобы любая из выбранных строк попала в выборку, необходимо, чтобы все условия в совокупности имели значение Истина.
- ОБЪЕДИНИТЬ ВСЕ — данное ключевое слово используется для объединения запросов (операторов ВЫБРАТЬ). Язык запросов 1С позволяет объединять несколько запросов в один. Для того, чтобы запросы можно было объединить у них должен быть одинаковый набор полей;
- «;» — точка с запятой используется для разделения не зависимых друг от друга операторов ВЫБРАТЬ;
- ИНДЕКСИРОВАТЬ ПО — ключевое слово используется для индексации полей указанных после него;
- Блок итогов — используется для построения древовидных выборок. По каждому из группировочных полей, указанных после ключевого слова ПО, будет создана отдельная строка в выборке. В этой строке при помощи агрегатных функций будут посчитаны итоговые значения полей указанных после ключевого слова ИТОГИ.
Хотите продолжить изучать язык запросов 1с 8? Тогда прочтите следующую статью: Программная работа с запросом в 1С 8
Научитесь пользоваться конструктором запросов: Конструктор запросов 1С — обучение на примерах
6. При группировке требуется, чтобы значения в столбце Цена просуммировались. Для этого подойдёт агрегатная функция
7. При группировке требуется найти максимальное значение в столбце Цена. Для этого подойдёт агрегатная функция
8. При группировке требуется найти количество различных значений в столбце Цена. Для этого подойдёт агрегатная функция
Домашнее задание
а) Напишите запрос, который группирует цвета, встречающиеся среди еды:
Эталонное решение. Сначала пишем код сами, проверяем на компьютере (как и все примеры программ из уроков), только потом сверяемся
Войдите на сайт как ученик
б) Напишите запрос, который находит максимальную калорийность еды для каждого цвета:
Эталонное решение. Сначала пишем код сами, проверяем на компьютере (как и все примеры программ из уроков), только потом сверяемся
Войдите на сайт как ученик
в) Напишите запрос, который группирует еду по вкусу и цвету:
Эталонное решение. Сначала пишем код сами, проверяем на компьютере (как и все примеры программ из уроков), только потом сверяемся
Войдите на сайт как ученик
Если всё ещё не совсем понятно, как это группировать сразу по двум полям, то давайте разберём такой пример:
Пусть у нас есть следующая таблица:
И мы хотим сгруппировать её по полю А. Это значит, что мы возьмём все строчки с одинаковым полем А и "слепёшим" (свернём, сгруппируем) в одну.
Поэтому на выходе будет:
А теперь пусть есть таблица:
А Б
1 3
1 3
1 5
2 4
2 4
2 6И мы группируем сразу по А и Б. Это значит, что мы возьмём все строчки с одинаковыми А и Б и "слепёшим" (свернём, сгруппируем) в одну.
Поэтому на выходе будет:
А Б
1 3
1 5
2 4
2 6А теперь замените А на Вкус, а Б на Цвет.
г) Напишите запрос, который выведет количество цветов в таблице Справочник.Цвета:
Эталонное решение. Сначала пишем код сами, проверяем на компьютере (как и все примеры программ из уроков), только потом сверяемся
Войдите на сайт как ученик
Для учеников
Уровень сложности уроков идёт снизу вверх. Поэтому старайтесь заниматься последовательно, ничего не пропуская и не откладывая на потом.
Обязательно выполняйте все тесты и домашние задания, старайтесь улучшить свои результаты. Сверяйтесь с эталонными решениями только после самостоятельного выполнения задач.
На вопросы учеников — отвечаю по почте, но прежде загляните в ЧАВО (ссылка) .
Эти операторы возможно использовать во всех 4 ситуациях, но контекст (окружаемые переменные у них отличаются).
В первых двух случаях условия накладываются на поля выборки, например:
ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты КАК СпрКонтрагенты ГДЕ СпрКонтрагенты.ИНН<>""
Обращение идет через имя таблицы (в простых запросах может опускаться, но в сложных может вызывать неоднозначность поля).
Данное условие вызывает ограничение выборки (обычно уменьшает ее)
В конструкторе запроса находятся на вкладке условия:
В произвольном варианте пишется текстом, в обычном выбирается поле, оператор сравнения и значение:
ВЫБОР КОГДА
Этот условный оператор используется для преобразования значения, ограничивает же выборку только когда расположен после служебного слова ГДЕ.
Допустимо несколько подчиненных условий. Может находится в полях выборки, полях условий ГДЕ, группировок, итогов (универсальный оператор для сложных условий)Например, так выглядит вариант преобразования:
ВЫБРАТЬ
ВЫБОР
КОГДА 1 = 2
ТОГДА "Никогда не будет истиной"
КОГДА 1 = 1
ТОГДА "Всегда будет истиной"
ИНАЧЕ ДАТАВРЕМЯ(1, 1, 1)
КОНЕЦ КАК ПолеПредставленияЗначенияВариант использования в условии ГДЕ, результат выборки должен возвращать булево значение либо сравниваться с чем-то еще
Пример № 1
ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты КАК К ГДЕ ВЫБОР КОГДА К.ИНН = "" ТОГДА ЛОЖЬ ИНАЧЕ ИСТИНА КОНЕЦ
Пример № 2:
ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты КАК К ГДЕ ВЫБОР КОГДА К.ИНН = "" ТОГДА "" ИНАЧЕ "Заполнено" КОНЕЦ = "Заполнено"
Параметры виртуальных таблиц
В данном случае оперирование идет с полями таблиц, например ресурсами, измерениями, реквизитами. В этих условиях обращение идет без точки (контекст этого не требует).
ВЫБРАТЬ * ИЗ РегистрСведений.КурсыВалют.СрезПоследних(, Валюта.Код = "RUR")
В конструкторе запроса они находятся вот здесь:
Стоит отметить, что обычно используется ограничение над измерениями, так как иначе условие отнесется к выборке для получения, а не на сам результат (на ресурсы ограничения следует наложить в операторе ГДЕ, чтобы получить ожидаемый результат).
Накладывание условия в запросе на измерения, если нет обращения через точку (как в примере выше), ускоряет выборку: на больших таблицах, это будет визуально заметно; на небольших можно использовать как удобно.
ИМЕЮЩИЕ
Для наложения отбора для результата «группировочных» функции
ВЫБРАТЬ
Контрагенты.ИНН,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контрагенты.Ссылка) КАК Ссылка
ИЗ
Справочник.Контрагенты КАК КонтрагентыСГРУППИРОВАТЬ ПО
Контрагенты.ИННИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контрагенты.Ссылка) > 1 И
Контрагенты.ИНН <> ""В данную выборку попадут дубли ИНН, когда оно вообще заполнено.
В конструкторе запроса также находится на вкладке условия
В этой конструкции также возможно использование «группировочных» операторов И,ИЛИ, но сравнивать можно с константами запроса, с функцией или значением, входящим в группировку (другие реквизиты справочника в данном примере не будут доступны для сравнения -> тогда следует использовать ГДЕ).
Делать деньги без рекламы может только монетный двор.
— Томас МаколейГруппировка в запросе 1С 8.3 - это объединение в группу одинаковых строк таблицы по определенному признаку и получение суммарных результатов по числовым полям, которая объявляется ключевым словом СГРУППИРОВАТЬ ПО . Важно понимать, что при группировке происходит удаление строк (записей) таблицы запроса. В отличии от итогов, записей становиться меньше.
- Группировочные поля - в разрезе которых производится свёртка
- Группируемые поля - которые сворачиваются (например, числовые поля которые суммируются)
- СУММА - только для числовых полей, суммирует в итоги переданные числа
- МАКСИМУМ - для любых полей, вычисляет максимальное значение из переданных параметров,
- МИНИМУМ - для любых полей, вычисляет минимальное значение из переданных параметров,
- СРЕДНЕЕ - только для числовых полей, вычисляет среднее - сумма или количество переданных параметров
- КОЛИЧЕСТВО - для любых полей, вычисляет количество всех переданных параметров
- КОЛИЧЕСТВО РАЗЛИЧНЫХ - для любых полей, вычисляет количество различных параметров
✔ Группировка исходных данных в запросе
&НаСервере
Процедура ГруппировкаИсходныхДанныхВЗапросе ()// Группировка элементов справочника Материалы, при помощи функции СУММА,
// в разрезе срока использования (группировочное поле) по весу (группируемое поле)
Запрос = Новый Запрос ( "ВЫБРАТЬ
| СрокИспользования,
| СУММА(Вес)
|ИЗ
| Справочник.Материалы
|ГДЕ
| ЭтоГруппа = ЛОЖЬ
|СГРУППИРОВАТЬ ПО
| СрокИспользования" );РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;&НаСервере
Процедура ГруппировкаИсходныхДанныхВЗапросеБезГруппируемыхПолей ()// Наличие группируемых полей не является обязательным в группировке элементов
// Можно также использовать ВЫБРАТЬ РАЗЛИЧНЫЕ Сроки Использования. Результат будет тот же
Запрос = Новый Запрос ( "ВЫБРАТЬ
| СрокИспользования
|ИЗ
| Справочник.Материалы
|ГДЕ
| ЭтоГруппа = ЛОЖЬ
|СГРУППИРОВАТЬ ПО
| СрокИспользования" );РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;&НаСервере
Процедура ГруппировкаВЗапросеБезГруппировочныхПолейПоВсейТаблице ()// Наличие группируемых полей не является обязательным в группировке элементов
// Получение средних значений по всем элементам материалов
Запрос = Новый Запрос ( "ВЫБРАТЬ
| СРЕДНЕЕ(Вес),
| СРЕДНЕЕ(Плотность),
| СРЕДНЕЕ(Фасовка)
|ИЗ
| Справочник.Материалы
|ГДЕ
| ЭтоГруппа = ЛОЖЬ" );РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;Упорядочивание в запросе 1С 8.3 - это функция сортировки результата запроса, для которой используются операторы УПОРЯДОЧИТЬ ПО и АВТОУПОРЯДОЧИВАНИЕ.
- В секции с оператором УПОРЯДОЧИТЬ ПО можно использовать агрегатные функции (СУММА, МИНИМУМ, МАКСИМУМ, СРЕДНЕЕ, КОЛИЧЕСТВО [РАЗЛИЧНЫЕ]), которые были использованы для группировки результата. Сортировка для строк, чисел и булево (примитивных типов) происходит по алфавиту и возрастанию. Для ссылочных типов сортировка происходит не по коду (или представлению ссылки), а по уникальному идентификатору (внутреннему представлению ссылки). Если указано свойство иерархичности, то возможно упорядочивание в соответствии с иерархией.
- Оператор АВТОУПОРЯДОЧИВАНИЕ включает режим автоматического упорядочивания полей и используется для вывода записей в неизменном (постоянном) порядке. Аналогичен сортировки по умолчанию: для документов это дата и номер, а для справочников это код или наименование. В том случае, когда поле для упорядочивания ссылается на иерархический справочник, то будет применена иерархическая сортировка по данному справочнику. Автоупорядочивание в запросе может сильно тормозить процесс. Если сортировка не нужна, лучше вообще ее не использовать. Во многих случаях эффективнее записать сортировку через ключевое слово УПОРЯДОЧИТЬ ПО .
✔ Упорядочивание результата запроса по определенным полям
&НаСервере
Процедура УпорядочиваниеРезультатаЗапросаПоОпределеннымПолям ()// Выборка материалов, упорядоченных сперва по возрастанию срока использования,
// а потом по убыванию веса
Запрос = Новый Запрос ( "ВЫБРАТЬ
| Наименование,
| СрокИспользования,
| Вес
|ИЗ
| Справочник.Материалы
|УПОРЯДОЧИТЬ ПО
| СрокИспользования ВОЗР,
| Вес УБЫВ" );РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;&НаСервере
Процедура УпорядочиваниеРезультатаЗапросаПоИерархии ()// Выборка контрагентов в порядке их следования в иерархии
Запрос = Новый Запрос ( "ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.Контрагенты КАК Производитель
|УПОРЯДОЧИТЬ ПО
| Наименование Иерархия" );РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;&НаСервере
Процедура УпорядочиваниеРезультатаЗапросаПоВыражению ()// Выборка материалов, упорядоченных сперва по убыванию максимальной фасовке и плотности вместе
Запрос = Новый Запрос ( "ВЫБРАТЬ
| Наименование,
| Фасовка,
| Плотность
|ИЗ
| Справочник.Материалы
|УПОРЯДОЧИТЬ ПО
| (Фасовка + Плотность) УБЫВ" );РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;&НаСервере
Процедура УпорядочиваниеРезультатаЗапросаПоАгрегатнойФункцииГруппировки ()// Выборка материалов с минимальным весом и группировкой по сроку использования
// Запрос отсортируем по минимальному весу
Запрос = Новый Запрос ( "ВЫБРАТЬ
| СрокИспользования,
| МИНИМУМ(Вес)
|ИЗ
| Справочник.Материалы
|СГРУППИРОВАТЬ ПО
| СрокИспользования
|УПОРЯДОЧИТЬ ПО
| МИНИМУМ(Вес) ВОЗР" );РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;&НаСервере
Процедура АвтоупорядочиваниеРезультатаЗапроса ()// Сортировка по полю Ссылка и использование ключевого слова АВТОУПОРЯДОЧИВАНИЕ. При этом
// поле Ссылка в секции УПОРЯДОЧИТЬ ПО будет автоматически изменена на дату документа.
Запрос = Новый Запрос ( "ВЫБРАТЬ
| ПоступлениеМатериалов.Ссылка
|ИЗ
| Документ.ПоступлениеМатериалов КАК ПоступлениеМатериалов
|УПОРЯДОЧИТЬ ПО
| ПоступлениеМатериалов.Ссылка
|АВТОУПОРЯДОЧИВАНИЕ" );РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;Читайте также: