Русско-английские возможности языка запросов

Для специалистов, работавших с запросами в различных СУБД, будет приятно узнать что система 1С:Предприятие 8 поддерживает возможность написания текста запросов на английском языке:

^{Рисунок 3.1}

Но обязательно нужно учитывать, что те прикладные объекты конфигурации и реквизиты (не стандартные), которые мы создаем при описании модели прикладного решения на русском языке, в тексте запроса будут также на русском языке (а в таком случае, согласитесь, не очень красивым получается запрос в русско-английском исполнении).

Основная настройка конфигурации "Вариант встроенного языка" позволяет определить язык синтаксических конструкций, используемый в конструкторе запроса (русский либо английский):

^{Рисунок 3.2}

 

Язык самого конструктора определяется используемым дополнительным интерфейсом платформы, например, если выбран Английский, то внешний вид конструктора будет следующим:

^{Рисунок 3.3}

Такие возможности позволяют, во-первых, вести разработку не только на русском языке и во-вторых, разработчики, привыкшие писать запросы на английском языке, смогут быстрее адаптироваться к данному инструментарию.

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

Структура и синтаксис запроса

Текст запроса составляется на языке запросов и его возможную структуру можно описать следующим синтаксическим правилом:

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

Как мы видим из структуры, наличие полей выборки является обязательным условием любого запроса:

^{Рисунок 3.4}

В тексте запроса можно писать комментарии, которые оформляются таким же образом, как и во встроенном языке "1С:Предприятие 8", с помощью //:

^{Рисунок 3.5}

Пройдемся по всем основным конструкциям запросов.

Секция ВЫБРАТЬ

Текст запроса всегда начинается с обязательного ключевого слова ВЫБРАТЬ. Данное ключевое слово позволяет указать:

· Список полей для выборки²

· либо, используя символ звездочка (*) выбрать все именованные поля из источника

· либо через имена полей выбрать определенные именованные поля из таблиц-источников данных через запятую

· В качестве полей могут использоваться вычисляемые выражения, например, параметры запроса, арифметические, логические, строковые выражения и выражения с оператором выбора.

^{Рисунок 3.6}

· В качестве полей выборки можно использовать вложенные таблицы:

^{Рисунок 3.7}

При обращении к такому полю, мы получим результат запроса для каждой строки данных:

^{Рисунок 3.8}

При выгрузке результата можно увидеть содержимое данного поля:

^{Рисунок 3.9}

· Виртуальные поля. Виртуальные поля – это поля, которые не хранятся в базе данных, а генерируются системой "на лету", например, поле "Представление" для таблицы справочника и документа, или поле "МоментВремени" для документа:

· Без виртуальных полей:

^{Рисунок 3.10}

· С виртуальными полями:

^{Рисунок 3.11}

Важно: Условия появления ВИРТУАЛЬНЫХ ПОЛЕЙ При использовании в тексте запроса в качестве списка полей выборки символа ЗВЕЗДОЧКА (*) виртуальные поля не подставляются автоматически программно и конструктором в список выбранных полей. Их использование надо задать в явном виде, т.е. указанием их имен из списка возможных полей.

 

При описании полей выборки можно присваивать им различные имена для использования в запросе. Такие имена называются псевдонимами. Псевдонимы полей задаются в соответствии с правилами назначения идентификаторов переменных. Псевдонимы в запросе не могут совпадать. Назначение псевдонимов полям само по себе никак не влияет на выборку данных в запросе.

Псевдонимы полей выборки присваиваются с помощью (необязательного) ключевого слова КАК, либо в конструкторе на закладке "Объединения и Псевдонимы":

^{Рисунок 3.12}

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

^{Рисунок 3.13}

В предложениях УПОРЯДОЧИТЬ ПО и ИТОГИ можно использовать псевдонимы.

^{Рисунок 3.14}

Хотя язык запросов в системе 1С:Предприятие 8 и основан на SQL, но при этом содержит значительное количество расширений, ориентированных на отражение специфики финансово-экономических задач и на максимальное сокращение усилий по разработке прикладных решений, например одно из таких расширений:

обращение к полям через точку ³ (".") - если поля какой-либо таблицы имеют ссылочный тип (хранят ссылки на объекты другой таблицы), разработчик может в тексте запроса ссылаться на них через ".", как во встроенном языке (для упрощения запроса), при этом количество уровней вложенности таких ссылок система не ограничивает:

^{Рисунок 3.15}

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

^{Рисунок 3.16}

Для сворачивания таких записей (показа их только один раз) существует ключевое слово РАЗЛИЧНЫЕ:

Уберем лишние (повторяющиеся) записи:

^{Рисунок 3.17}

Ключевое слово РАЗРЕШЕННЫЕ означает, что запрос выберет только те записи, на которые у текущего пользователя есть права. Если данное слово не указать, то в случае, когда запрос выберет записи, на которые у пользователя нет прав, запрос отработает с ошибкой и пользователь получит сообщение:

^{Рисунок 3.18}

При отсутствии такой конструкции пользователю потребуется задать условия на выбираемые значения, чтобы получить результат, а это не очень удобно. Например, в отчете "Остатки взаиморасчетов" данные только по своим клиентам, а такой список может быть большим. При указании конструкции РАЗРЕШЕННЫЕ система сама выдаст информацию только по требуемым клиентам.

Данное ключевое слово идет сразу за конструкцией ВЫБРАТЬ и может задаваться как вручную в тексте запроса, либо в конструкторе запроса на закладке "Дополнительно":

^{Рисунок 3.19}

Важно: Условия применения конструкции РАЗРЕШЕННЫЕ Может присутствовать предложение ВЫБРАТЬ только верхнего уровня, и распространяется на весь запрос, включая вложенные запросы

 

Конструкция ПЕРВЫЕ <Количество> позволяет задать предельное количество строк в результате запроса. Будут отобраны самые первые (в соответствии с правилами упорядочивания результатов запроса) строки. <Количество> задается целым числом:

^{Рисунок 3.20}

· Если упорядочить список по возрастанию, то получим "Первые"

· Если упорядочить по убыванию, то – "Последние"

Какой результат получим, если используются одновременно конструкции "РАЗЛИЧНЫЕ" и "ПЕРВЫЕ 3" из таблицы следующего содержания:

Наименование

Ручки шариковые

Линейки

Ручки шариковые

Карандаши

Ластики

Варианты:

· записи (Ручки шариковые, Линейки)

· записи (Ручки шариковые, Линейки, Карандаши)

· записи (Ручки шариковые, Линейки, Ластики)

· записи (Карандаши, Линейки, Ластики)

Секция ИЗ

В языке запросов источники данных можно определять двумя способами:

· Упрощенный вариант - в имени поля выборки (применяется при запросах с одним источником и небольшим количеством выбираемых полей 1-2)

^{Рисунок 3.21}

· Стандартный вариант ⁴– используется конструкция ИЗ <Список источников>. Описание источников может быть опущено только в том случае, если они полностью определены в списке полей выборки:

^{Рисунок 3.22}

Для таблиц, как и для полей, могут применяться псевдонимы через ключевое слово КАК (тоже не обязательное, но желательное):

^{Рисунок 3.23}

При использовании псевдонимов таблиц, обращение к полям осуществляется через заданные псевдонимы.

Конструктор запроса всегда указываем псевдоним таблицы, если он не задан явно. Имя псевдонима определяется системой по имени таблицы:

^{Рисунок 3.24}

Задать псевдоним можно для любой таблицы. В конструкторе запроса данное действие можно выполнить использую контекстное меню в поле Таблицы применительно к выбранной курсором таблице:

^{Рисунок 3.25}

И так, теперь мы знаем, что псевдонимы можно задавать полям и таблицам.

Зачем же такое разнообразие спросите Вы? Почему бы не остановиться только на полях или только на таблицах?

· Если мы будем использовать только псевдонимы таблиц, то мы не сможем определить имена колонок результирующей выборки, а значит про автозаполнение таблиц и форм придется забыть. Мы же не хотим терять такую возможность?

· Отсутствие псевдонимов таблиц может привести нас к неразрешимым ошибкам в тексте запроса. Например, имя поля совпадает с именем таблицы.

^{Рисунок 3.26}

В реальности такие ситуации могут быть. Допустим, менять имя поля нам нельзя из соображений первого пункта. Остается поменять имя таблицы (теперь мы знаем, как его поменять). Без определения псевдонима будет выдано такое сообщение:

^{Рисунок 3.27}

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

Получается, псевдонимы позволяют решить нам множество проблем. Но и не только проблемы можно решать с их помощью. Используя псевдонимы можно создавать шаблоны запросов, например:

^{Рисунок 3.28}

За счет псевдонима таблицы нам не пришлось менять весь запрос, а только часть, отвечающую за имя источника.

В конструкции ИЗ можно определять несколько таблиц (что является основной задачей исходя из назначения табличной модели):

^{Рисунок 3.29}

О правилах использования нескольких источников мы поговорим позже.

Секция ГДЕ

Условие на выбор конкретных записей задается с помощью конструкции ГДЕ <Условие отбора>. В конструкторе запроса условие определяется на закладке "Условия":

^{Рисунок 3.30}

В результат попадают только те записи, для которых указанное условие оказывается истинным. Условие, определяемое после ключевого слова ГДЕ, действует после того, как из исходной таблицы – источника выбраны все записи. Как мы видим из картинки, поля условия можно не указывать в полях выборки, а задействовать только в условиях.

В условиях можно использовать логические операторы И, ИЛИ, НЕ, а так же операторные скобки, при помощи которых можно задавать более сложные условия отбора. Приоритеты выполнения операторов: НЕ, потом И, потом ИЛИ.

Произвольные условия в конструкторе отмечаются флажком в колонке "П.."(Произвольное):

^{Рисунок 3.31}

Для создания произвольных условий в конструкторе существует конструктор произвольных выражений:

^{Рисунок 3.32}

Данный конструктор позволяет быстро и без синтаксических ошибок создать условие. Если в условии будет обнаружена ошибка, то будет выдано предупреждение. Например:

^{Рисунок 3.33}

Полное описание всех функций языка запроса и примеры использования определены в документации и электронной справке⁵ (F1):

^{Рисунок 3.34}

Мы же в данном курсе рассмотрим только конструкции, определяющие структуру запроса и формирующие представление о возможностях механизма запросов.

Секция СГРУППИРОВАТЬ ПО

Ранее мы с Вами рассмотрели механизм сворачивания одинаковых записей. Но он хорош только в том случае, если записи идентичны по всем полям. А как же быть в следующем случае:

Товар	Сумма продаж
Ручки шариковые	1000
Ластики	500
Ручки шариковые	2000
Карандаши	4000

Нам надо сгруппировать записи по ручкам в одну и в колонке Сумма продаж показать сумму по сгруппированным записям:

Товар	Сумма продаж
Ручки шариковые	3000
Ластики	500
Карандаши	4000

В нашем случае конструкция РАЗЛИЧНЫЕ уже не подходит из-за колонки Сумма продаж, т.к. данные в ней надо суммировать. Для решения таких задач создана конструкция СГРУППИРОВАТЬ ПО <Поля группировки>, которая в конструкторе запроса отображается на закладке "Группировка":

^{Рисунок 3.35}

Нераспределенные поля конструктор автоматически помещает в групповое поле при создании запроса (если это возможно).

Важно помнить, что в большинстве случаев все поля выборки запроса должны делиться на две группы:

· агрегатные функции (конструктор предлагает быстрый выбор допустимых агрегатных функций):

^{Рисунок 3.36}

· поля, по которым ведется группировка.

^{Рисунок 3.37}

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

^{Рисунок 3.38}

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

Секция ИМЕЮЩИЕ

Предложение "ИМЕЮЩИЕ" позволяет накладывать условия на значения агрегатных функций и результаты группировок. В других конструкциях языка запросов, например, в предложении ГДЕ, использовать агрегатные функции в условиях нельзя.

В условии отбора ИМЕЮЩИЕ можно использовать только агрегатные функции и поля, по которым осуществляется группировка:

^{Рисунок 3.39}

Настройка данного условия в конструкторе запроса осуществляется на закладке Условия (как и в случае с конструкцией ГДЕ). Обратите внимание на то, что в данном варианте, оно всегда произвольное, т.к. использует агрегатную функцию.

Значение условия может не присутствовать в списке полей выборки:

^{Рисунок 3.40}

Секция ДЛЯ ИЗМЕНЕНИЯ

Предложение ДЛЯ ИЗМЕНЕНИЯ предназначено для указания необходимости блокировки считываемых в транзакции данных. В конструкторе запроса данная настройка осуществляется на закладке "Дополнительно":

с установкой флажка "Блокировать получаемые данные для последующего изменения" появится конструкция "ДЛЯ ИЗМЕНЕНИЯ"

в область "Таблицы для изменения" переносим необходимые для блокировки таблицы из области "Таблицы"

^{Рисунок 3.41}

Считанные данные становятся недоступными для чтения в других сессиях:

· Для файлового варианта блокируются указанные таблицы

· Для клиент – серверного варианта – только выбранные записи.

Блокировка снимается после завершения транзакции.

Секция УПОРЯДОЧИТЬ ПО

Как правило, данные, полученные с помощью запроса, нуждаются в какой-либо сортировке, либо по алфавиту, либо по числовому полю. Данная возможность реализуется с помощью ключевого слова УПОРЯДОЧИТЬ ПО. Список полей упорядочивания может состоять из одного или нескольких имен полей, разделенных запятыми. Настройка упорядочивания в конструкторе запроса производится на закладке "Порядок":

^{Рисунок 3.42}

Условие упорядочивания в общем случае может представлять собой некоторое выражение. Строки результата запроса будут упорядочены по значениям этого выражения, рассчитанным для каждой строки.

Существует несколько вариантов упорядочивания:

^{Рисунок 3.43}

· Возрастание - упорядочивание по указанному полю в порядке возрастания значений. Является вариантом по умолчанию. Если в запросе не указан способ, но можно указать явно – "ВОЗР". (Например, для случаев программной модификации текста запроса Возрастание/Убывание)

· Убывание - упорядочивание по указанному полю в порядке убывания значений (указание в тексте запроса требует явного указания – "УБЫВ")

^{Рисунок 3.44}

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

Следующие настройки работают только для иерархических (по своему происхождению) таблиц:

Иерархия (по возрастанию) – добавляет в результат группы элементов. Сортировка элементов по возрастанию.

^{Рисунок 3.45}

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

Иерархия по убыванию – добавляет в результат группы элементов. Сортировка элементов по убыванию.

Рассмотрим на примере отличия иерархии от иерархии по убыванию:

^{Рисунок 3.46}

И так, мы видим, что сортировка по возрастанию и убыванию работает как для групп, так и для элементов внутри этих групп.

Рассмотренные выше примеры использования ключевого слова УПОРЯДОЧИТЬ ПО могут использовать автоматический способ упорядочивания с использованием ключевого слова АВТОУПОРЯДОЧИВАНИЕ.

При использовании ключевого слова АВТОУПОРЯДОЧИВАНИЕ, поля для упорядочивания результата запроса будут формироваться автоматически. Использование автоматического упорядочивания облегчает процесс написания отчетов и запросов.

Для автоматического упорядочивания существуют следующие правила автоматического упорядочивания:

АВТОУПОРЯДОЧИВАНИЕ используется автономно

(в тексте запроса отсутствуют предложения: УПОРЯДОЧИТЬ ПО, ИТОГИ, СГРУППИРОВАТЬ ПО)

Результат запроса будет упорядочен по полям сортировки по умолчанию⁸ для таблиц, выбранных в качестве источников данных:

^{Рисунок 3.47}

АВТОУПОРЯДОЧИВАНИЕ + УПОРЯДОЧИТЬ ПО.

Каждая ссылка на таблицу, перечисленная после предложения УПОРЯДОЧИТЬ ПО, будет заменена полями, по которым по умолчанию⁹ сортируется таблица.

Если поле для упорядочивания ссылается на иерархический справочник, то будет применена иерархическая сортировка по этому справочнику.

^{Рисунок 3.48}

АВТОУПОРЯДОЧИВАНИЕ + СГРУППИРОВАТЬ ПО

(в тексте запроса отсутствуют предложения УПОРЯДОЧИТЬ ПО и ИТОГИ)

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

^{Рисунок 3.49}

АВТОУПОРЯДОЧИВАНИЕ + ИТОГИ

(в тексте запроса отсутствует предложение УПОРЯДОЧИТЬ ПО)

Результат запроса будет упорядочен по полям, перечисленным после предложения ИТОГИ ПО. А если вывод итогов осуществлялся по ссылочным полям, то и по полям сортировки по умолчанию¹¹ для этих таблиц.

В случае если запрос содержит предложение ИТОГИ, каждый уровень итогов упорядочивается отдельно.

^{Рисунок 3.50}

Секция ИТОГИ

С помощью ключевого слова ИТОГИ¹² можно включить в результат запроса дополнительные записи, в которых содержатся данные об общих и промежуточных итогах по полям и группировкам, с использованием агрегатных функций.

Существует несколько вариантов вывода итогов:

· Общие итоги;

· Итоги по группировкам;

· Итоги по иерархии;

· Комбинация вышеперечисленных вариантов;

Настройка секции ИТОГИ в конструкторе запросе осуществляется на закладке "Итоги":

^{Рисунок 3.51}

В некоторых случаях закладки Итоги может не быть, это означает, что использовать итоги нельзя, например, при создании вложенного запроса:

^{Рисунок 3.52}

Таким образом, конструктор оберегает разработчика от неправильных действий при создании запросов, чего редактор текстов не делает при ручном создании текстов запроса.

Итоги чем-то похожи на уже рассмотренные нами группировки в части отражения сгруппированных записей.

Основное отличие ИТОГОВ от ГРУППИРОВОК на уровне конечного результата в том, что:

ГРУППИРОВКИ – превращают детальные записи в группировочные:

^{Рисунок 3.53}

ИТОГИ – добавляют группировочные записи к детальным:

^{Рисунок 3.54}

В части текстов запроса также присутствуют различия:

^{Рисунок 3.55}

в ИТОГАХ можем использовать Псевдонимы полей

в ИТОГАХ агрегатная функция указывается в секции ИТОГИ

в запросе могут присутствовать поля выборки, не используемые в ИТОГАХ (в нашем примере - "Остатки.Товар.Поставщик")

И третье, ключевое различие ИТОГОВ и ГРУППИРОВОК, рассмотрим в конструкторе запросов:

^{Рисунок 3.56}

Как мы видим, в группировках всего две области используемых полей, а в ИТОГАХ три:

Область группировочных полей – по которым будет производиться группировка (сворачивание)

Область итоговых полей – значения, которых, будут обрабатываться агрегатными функциями

Область полей выборки – для итогов может содержать поля выборки, при группировках конструктор перенесет поля в группировочные.

Для области итоговых полей, как мы видим, есть дополнительная настройка "Общие итоги". Она позволяет получить одну итоговую запись по всем записям выборки без учета группировочных полей:

В части настройки группировочных полей, как мы видим, тоже появились изменения (сравниваем с закладкой Группировка):

· Определение псевдонимов для группировочных полей

^{Рисунок 3.57}

· Настройка уровня использования итоговых записей (список возможных вариантов зависит от типа значения)

^{Рисунок 3.58}

^{Рисунок 3.59}

· Настройка для группировочных полей типа Дата – дополнение результатов датами в заданном периоде:

^{Рисунок 3.60}

В тексте запроса определяется при помощи ключевого слова ПЕРИОДАМИ, после которого в скобках указывается вид периода (одно из СЕКУНДА, МИНУТА, ЧАС, ДЕНЬ, НЕДЕЛЯ, МЕСЯЦ, КВАРТАЛ, ГОД, ДЕКАДА, ПОЛУГОДИЕ), начальная и конечные даты интересуемого периода. В случае если начальные и конечные даты не указаны, будут использованы первая и последние даты, участвующие в результате

 

¹ Практическое освоение языка и механизмов запросов осуществляется в соответствующих курсах.

² Допускается одновременное использование имен полей и символа звездочка

³ Разыменование ссылочных полей

⁴ Стандартный, потому что конструктор запроса преобразует простой вариант запроса к данному.

⁵ Не в синтакс-помощнике

⁶ Замечание. Запрос, в котором указано ключевое слово РАЗЛИЧНЫЕ и в предложении УПОРЯДОЧИТЬ ПО указано выражение, отсутствующее в списке выборки, считается некорректным, и при исполнении такого запроса будет выдана ошибка. В режиме совместимости с версией 8.1 ошибка выдаваться не будет.

⁷ Соединение источников рассмотрим в разделе, посвященному использованию нескольких источников

⁸ Поля сортировки по умолчанию для справочников это код или наименование, для документов – дата документа

⁹ Поля сортировки по умолчанию для справочников это код или наименование, для документов – дата документа

¹⁰ Поля сортировки по умолчанию для справочников это код или наименование, для документов – дата документа

¹¹ Поля сортировки по умолчанию для справочников это код или наименование, для документов – дата документа

¹² В настоящей версии программы не поддерживается расчет итогов по вложенным таблицам.