Язык запросов 1С — одно из основных отличий между версиями 7.7 и 8. Одним из важнейших пунктов в изучении 1С-программирования является язык запросов. В 1С 8.3 запросы — самый мощный и эффективный инструмент получения данных. Язык запросов позволяет в удобном виде получать информацию из базы данных.

Сам синтаксис очень сильно напоминает классический T-SQL, за исключением того, что в 1С с помощью языка запросов можно только получать данные, используя конструкцию Выбрать(select). Язык поддерживает и более сложные конструкции, например, (запрос в запросе). Запросы в 1С 8 можно составлять как на кириллице, так и на латинице.

В статье я постараюсь рассказать про основные ключевые слова в языке запросов 1С:

• выбрать
• разрешенные
• различные
• выразить
• первые
• для изменения
• значение
• тип значения (и оператор ССЫЛКА)
• выбор
• сгруппировать по
• имеющие
• ЕСТЬNULL
• Есть NULL
• соединения — правые, левые, внутренние, полные .

А так же небольшие хитрости языка 1С, применяя которые вы сможете оптимально построить текст запроса.

Для отладки запросов в системе 1С 8.2 предусмотрен специальный инструмент — консоль запросов. Увидеть описание и скачать его можно по ссылке — .

Рассмотрим самые важные и интересные операторы языка запросов 1С.

ВЫБРАТЬ (SELECT)

В языке запросов 1С предприятия 8 любой запрос начинается с ключевого слова ВЫБРАТЬ . В языке 1С нет конструкций UPDATE, DELETE, CREATE TABLE, INSERT, эти манипуляции производятся в объектной технике. Предназначение его — только чтение данных.

Например:

ВЫБРАТЬ
ТекущийСправочник.Наименование
ИЗ
Справочник.Номенклатура КАК ТекущийСправочник

Запрос вернёт таблицу с наименованиями номенклатуры.

Рядом с конструкцией ВЫБРАТЬ можно встретить ключевые слова ДЛЯ ИЗМЕНЕНИЯ , РАЗРЕШЕННЫЕ , РАЗЛИЧНЫЕ , ПЕРВЫЕ …

РАЗРЕШЕННЫЕ — выбирает только записи из таблицы, на которые есть права у текущего пользователя.

РАЗЛИЧНЫЕ — означает, что в результат не попадет дублирующих строк.

ВЫБОР (CASE)

Очень часто данная конструкция недооценивается программистами. Пример её использования:

ТекущийСправочник.Наименование,

КОГДА ТекущийСправочник.Услуга ТОГДА

«Услуга»

КОНЕЦ КАК ВидНоменклатуры

Справочник.Номенклатура КАК ТекущийСправочник

Пример возвратит в поле «ВидНоменклатуры» текстовое значение — «Товар» или «Услуга».

ГДЕ (WHERE)

Конструкция языка запросов 1C, позволяющая наложить отбор на получаемые данные. Учтите, что от сервера система получает все данные, а только потом они отбираются по данному параметру.

ВЫБРАТЬ
Справочник.Наименование
ИЗ
ТекущийСправочник.Номенклатура КАК ТекущийСправочник
ГДЕ ТекущийСправочник.Услуга = ИСТИНА

В примере мы отбираем записи, у которых значение реквизита «Услуга» установлено в положение «Истина». В данном примере можно было бы обойтись и таким условием:

«ГДЕ Услуга»

По сути, мы отбираем строки, у которых выражение после ключевого слова равно «Истина».

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

ГДЕ Код = «005215»

С помощью оператора «ЗНАЧЕНИЕ()» в условиях использовать обращение к предопределенным элементам и перечислениям в запросе 1С:

ГДЕ ТипНоменклатуры= Значение(Перечисление.ТипыНоменклатуры.Товар)

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

ГДЕ ДатаПоступления > ДАТАВРЕМЯ(2012,01,01):

Чаще всего условия указываются в виде параметров, передаваемых в запрос:

Получите 267 видеоуроков по 1С бесплатно:

ГДЕ НоменклатурнаяГруппа= &НоменклатурнаяГруппа

Условие можно наложить на тип реквизита, если он составного типа:

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

ГДЕ РегистрНакопления.Регистратор В (&СписокДокументовДляОтбора)

Условие может быть и сложное, состоящее из нескольких условий:

ГДЕ ДатаПоступления > ДАТАВРЕМЯ(2012,01,01) И НоменклатурнаяГруппа= &НоменклатурнаяГруппа И НЕ Услуга

СГРУППИРОВАТЬ ПО (GROUP BY)

Конструкция языка запросов 1С 8.2, используемая для группировки результата.

Например:

ВЫБРАТЬ
ПоступлениеТоваровУслугTовары.Товар,
СУММА(ПоступлениеТоваровУслугTовары.Количество) КАК Количество,
СУММА(ПоступлениеТоваровУслугTовары.Сумма) КАК Сумма
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТоваровУслугTовары

СГРУППИРОВАТЬ ПО
ПоступлениеТоваровУслугTовары.Товар

Данный запрос просуммирует все поступления по сумме и количеству в разрезе номенклатуры.

Помимо ключевого слова СУММА можно использовать другие агрегатные функции: КОЛИЧЕСТВО , КОЛИЧЕСТВО РАЗНЫХ , МАКСИМУМ , МИНИМУМ , СРЕДНЕЕ .

ИМЕЮЩИЕ (HAVING)

Конструкция, о которой часто забывают, но она очень важна и полезна. Она позволяет указать отбор в виде агрегатной функции, этого нельзя сделать в конструкции ГДЕ .

Пример использования ИМЕЮЩИЕ в запросе 1С:

ВЫБРАТЬ
ПоступлениеТоваровУслугТовары.Товар,
СУММА(ПоступлениеТоваровУслугТовары.Количество) КАК Количество,
СУММА(ПоступлениеТоваровУслугТовары.Сумма) КАК Сумма
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТоваровУслугТовары

СГРУППИРОВАТЬ ПО
ПоступлениеТоваровУслугТовары.товар

СУММА(ПоступлениеТоваровУслугТовары.Количество) > 5

Так мы отберем количество товаров, которых поступило более 5 штук.

ЗНАЧЕНИЕ()

Например:

ГДЕ Банк = Значение(Справочник.Банки.ПустаяСсылка)

ГДЕ ВидНоменклатуры = Значение(Справочник.ВидыНоменклатуры.Товар)

ГДЕ ТипНоменклатуры= Значение(Перечисление.ТипыНоменклатуры.Услуга)

ТИП в запросе

Тип данных можно проверить следующим образом: с помощью функций ТИП() и ТИПЗНАЧЕНИЯ() или с помощью логического оператора ССЫЛКА.

ВЫРАЗИТЬ()

Оператор Выразить в запросах 1С служит для преобразования типов данных.

Синтаксис: ВЫРАЗИТЬ(<Выражение> КАК <Тип значения>)

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

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

ВЫБРАТЬ
КонтактнаяИнформация.Объект,
ВЫРАЗИТЬ(КонтактнаяИнформация.Представление КАК СТРОКА(150)) КАК Представление
ИЗ
РегистрСведений.КонтактнаяИнформация КАК КонтактнаяИнформация

СГРУППИРОВАТЬ ПО
ВЫРАЗИТЬ(КонтактнаяИнформация.Представление КАК СТРОКА(150)),
КонтактнаяИнформация.Объект

ЕСТЬNULL (ISNULL)

Достаточно полезная функция языка запросов 1С, которая проверяет значение в записи, и если оно равно NULL, то позволяет заменить на своё значение. Чаще всего используется при получении виртуальных таблиц остатков и оборотов, чтобы скрыть NULL и поставить понятный 0 (ноль).

ЕСТЬNULL(НалогиПредМесяца.ПримененнаяЛьготаФСС, 0)

Такая функция языка запросов 1С ЕСТЬNULL при отсутствии значения вернет ноль, что позволит избежать ошибки.

СОЕДИНЕНИЕ (JOIN)

Соединения бывают 4 типов: ЛЕВОЕ , ПРАВОЕ , ПОЛНОЕ, ВНУТРЕННЕЕ .

ЛЕВОЕ и ПРАВОЕ СОЕДИНЕНИЕ

Соединения используются для связи двух таблиц по определенному условию. Особенность при ЛЕВОМ СОЕДИНЕНИИ в том, что мы берём первую указанную таблицу полностью и привязываем по условию вторую таблицу. Поля второй таблицы, которые не удалось привязать по условию, заполняются значением NULL .

Пример левого соединения в запросе 1С:

Вернет всю таблицу и заполнит поле «Банк» лишь в тех местах, где будет соблюдаться условие «Контрагенты.Наименование = Банки.Наименование». Если условие не соблюдается, в поле Банк будет установлено NULL .

ПРАВОЕ СОЕДИНЕНИЕ в языке 1С 8.3 абсолютно аналогично ЛЕВОМУ соединению , за исключением одного отличия: в ПРАВОМ СОЕДИНЕНИИ «главная» таблица — вторая, а не первая.

ПОЛНОЕ СОЕДИНЕНИЕ

ПОЛНОЕ СОЕДИНЕНИЕ отличается от левого и правого тем, что выводит все записи из двух таблиц, соединяет лишь те, которые может соединить по условию.

Например:

ПОЛНОЕ СОЕДИНЕНИЕ
Справочник.Банки КАК Банки

ПО

Язык запросов вернет обе таблицы полностью лишь по выполненному условию Соединить записи. В отличие от левого/правого соединения возможно появления NULL в двух полях.

ВНУТРЕННЕЕ СОЕДИНЕНИЕ

ВНУТРЕННЕЕ СОЕДИНЕНИЕ отличается от полного тем, что выводит только те записи, которые смогли соединить по заданному условию.

Например:

ИЗ
Справочник.Контрагенты КАК Клиенты

ВНУТРЕННЕЕ СОЕДИНЕНИЕ
Справочник.Банки КАК Банки

ПО
Клиенты.Наименование = Банки.Наименование

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

Заключение

Это лишь небольшая часть синтаксиса из языка запросов 1С 8, в дальнейшем я попробую рассмотреть более подробно некоторые моменты, показать и многое другое!

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

• левое;
• правое,
• внутреннее;
• полное.

Каждый тип мы рассмотрим на абстрактном примере. Имеется 2 таблицы, в первой храним описательную информацию о номенклатуре, во второй о ее остатках:

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

Левое соединение

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

Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Товар,
| Номенклатура.Цвет КАК ЦветНоменклатура,
| Остатки.Цвет КАК ЦветОстатки,
| Остатки.Количество
|ИЗ

";

Для стула не нашлось сопоставлений из таблицы остатков, поэтому поля заполнились значениями NULL, которые обязательно нужно обработать функцией ЕСТЬNULL, см. Функции языка запросов 1С 8.

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

Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Товар,
| Номенклатура.Цвет,
| ЕСТЬNULL(Остатки.Количество, 0) КАК Количество
|ИЗ
| Номенклатура КАК Номенклатура
| ЛЕВОЕ СОЕДИНЕНИЕ Остатки КАК Остатки
| ПО Номенклатура.Товар = Остатки.Товар

Правое соединение

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

Внутреннее соединение

Используя внутреннее соединение мы говорим системе, что в результате хотим видеть только те записи, которые удовлетворяют условию связи как из правой таблицы, так и из левой. Таким образом, количество результирующих записей будет меньше или равно количеству записей самой "короткой таблицы", участвующей в соединении. Применим внутреннее соединение к полям Товар и Цвет наших таблиц:

Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Товар,
| Номенклатура.Цвет,
| Остатки.Количество КАК Количество
|ИЗ
| Номенклатура КАК Номенклатура
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Остатки КАК Остатки
| ПО Номенклатура.Товар = Остатки.Товар
| И Номенклатура.Цвет = Остатки.Цвет";

Полное соединение

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

Задач на эту тему может быть много, давайте попробуем решить одну из них. Наша организация является дилером 2 мебельных фабрик: "Заря" и "Рассвет". Ассортимент со стоимостью каждой из фабрик хранится в разных таблицах. Необходимо составить единый прайс лист, причем брать в него продукцию по минимальной цене:

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

Запрос.Текст =
"ВЫБРАТЬ
| НоменклатураЗаря.Товар КАК ТоварЗаря,
| НоменклатураЗаря.Цена КАК ЦенаЗаря,
| НоменклатураРассвет.Товар КАК ТоварРассвет,
| НоменклатураРассвет.Цена КАК ЦенаРассвет
|ИЗ

Это не совсем то, что нам нужно, давайте соединим поле товар в одно и обработаем NULLы:

Запрос.Текст =
"ВЫБРАТЬ
//конструкция ЕСТЬNULL рассматривалась в разделе функции языка запросов
//если цена не определена, то инициализируем ее
//почему 1000000 см. пояснения ниже
| ЕСТЬNULL(НоменклатураЗаря.Цена, 1000000) КАК ЦенаЗаря,
| ЕСТЬNULL(НоменклатураРассвет.Цена, 1000000) КАК ЦенаРассвет
|ИЗ
| НоменклатураЗаря КАК НоменклатураЗаря
| ПОЛНОЕ СОЕДИНЕНИЕ НоменклатураРассвет КАК НоменклатураРассвет
| ПО НоменклатураЗаря.Товар = НоменклатураРассвет.Товар";

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

Запрос.Текст =
"ВЫБРАТЬ
| ЕСТЬNULL(НоменклатураЗаря.Товар, НоменклатураРассвет.Товар) КАК Товар,
| ВЫБОР
| КОГДА ЕСТЬNULL(НоменклатураЗаря.Цена, 1000000) > ЕСТЬNULL(НоменклатураРассвет.Цена, 1000000)
| ТОГДА ЕСТЬNULL(НоменклатураРассвет.Цена, 1000000)
| ИНАЧЕ ЕСТЬNULL(НоменклатураЗаря.Цена, 1000000)
| КОНЕЦ КАК Цена
|ИЗ
| НоменклатураЗаря КАК НоменклатураЗаря
| ПОЛНОЕ СОЕДИНЕНИЕ НоменклатураРассвет КАК НоменклатураРассвет
| ПО НоменклатураЗаря.Товар = НоменклатураРассвет.Товар";

Если цена не определена (NULL), то ее необходимо инициализировать каким либо значением, иначе операция сравнения на больше/меньше вывалится с ошибкой. инициализируем цену нереально большой суммой, чтобы она "проиграла" в операции сравнения, ведь по условию задачи мы подбираем наименьшую цену.

← Функции языка запросов 1C 8 | Объединения в запросах 1С 8 →

Язык запросов является одним из основополагающих механизмов 1С 8.3 для разработчиков. При помощи запросов можно быстро получить любые данные, хранящиеся в базе. Его синтаксис очень похож на SQL, но есть и отличия.

Основные достоинства языка запросов 1С 8.3 (8.2) перед SQL:

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

Недостатки языка запросов в 1С:

• в отличие от SQL, в 1С запросы не позволяют изменять данные;
• отсутствие хранимых процедур;
• невозможность преобразования строки в число.

Рассмотрим наш мини учебник по основным конструкциям языка запросов 1С.

В связи с тем, что запросы в 1С позволяют лишь получать данные, любой запрос должен начинаться со слова «ВЫБРАТЬ». После этой команды указываются поля, данные из которых нужно получить. Если указать «*», то будут выбраны все доступные поля. Место, откуда будут выбираться данные (документы, регистры, справочники и прочее) указывается после слова «ИЗ».

В рассмотренном ниже примере выбираются наименования всей номенклатуры из справочника «Номенклатура». После слова «КАК» указываются псевдонимы (имена) для таблиц и полей.

ВЫБРАТЬ
Номенклатура.Наименование КАК НаименованиеНоменклатуры
ИЗ
Справочник.Номенклатура КАК Номенклатура

Рядом с командой «ВЫБРАТЬ» можно указать ключевые слова:

• РАЗЛИЧНЫЕ . Запрос будет отбирать только отличающиеся хотя бы по одному полю строки (без дублей).
• ПЕРВЫЕ n , где n – количество строк с начала результата, которые необходимо отобрать. Чаще всего такая конструкция используется совместно с сортировкой (УПОРЯДОЧИТЬ ПО). Например, когда нужно отобрать определенное количество последних по дате документов.
• РАЗРЕШЕННЫЕ . Данная конструкция позволяет выбирать из базы только те записи, которые доступны текущему пользователю. Баз использования этого ключевого слова пользователю будет выведено сообщение об ошибке при попытке обращения запроса к тем записям, доступа к которым у него нет.

Эти ключевые слова могут использоваться как все вместе, так и по отдельности.

ДЛЯ ИЗМЕНЕНИЯ

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

Чаще всего предложение «ДЛЯ ИЗМЕНЕНИЯ» используется при получении остатков. Ведь при одновременной работе нескольких пользователей в программе, пока один получает остатки, другой может их изменить. В таком случае полученный остаток будет уже не верен. Если же заблокировать данные этим предложением, то пока первый сотрудник не получит корректный остаток и не совершит с ним все необходимые манипуляции, второй сотрудник будет вынужден ждать.

ВЫБРАТЬ
Взаиморасчеты.Сотрудник,
Взаиморасчеты.СуммаВзаиморасчетовОстаток
ИЗ
РегистрНакопления.ВзаиморасчетыССотрудниками.Остатки КАК Взаиморасчеты
ДЛЯ ИЗМЕНЕНИЯ

ГДЕ (WHERE)

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

Ниже приведен пример запроса получения контактных лиц с определенной должностью. Параметр отбора имеет формат: &ИмяПараметра (имя параметра произвольное).

ВЫБОР (CASE)

Конструкция позволяет указывать условия непосредственно в теле запроса.

В приведенном ниже примере «ДополнительноеПоле» будет содержать текст в зависимости от того проведен документ или нет:

ВЫБРАТЬ
ПоступлениеТиУ.Ссылка,
ВЫБОР
КОГДА ПоступлениеТиУ.Проведен
ТОГДА «Документ проведен!»
ИНАЧЕ «Документ не проведен…»
КОНЕЦ КАК ДополнительноеПоле
ИЗ
Документ.ПоступлениеТоваровУслуг КАК ПоступлениеТиУ

СОЕДИНЕНИЕ (JOIN)

Соединения связывают две таблицы по определенному условию связи.

ЛЕВОЕ/ПРАВОЕ СОЕДИНЕНИЕ

Суть ЛЕВОГО соединения заключается в том, что полностью берется первая указанная таблица и к ней по условию связи привязывается вторая. Если записей, соответствующих первой таблице во второй не нашлось, то в качестве их значений подставляется NULL. Проще говоря, главной является первая указанная таблица и к её данным уже подставляются данные второй таблицы (если они есть).

Например, необходимо получить номенклатурные позиции из документов «Поступление товаров и услуг» и цены из регистра сведений «Цены номенклатуры». В данном случае, если цена у какой-либо позиции не найдена, вместо нее подставиться NULL. Из документа все позиции будут выбраны вне зависимости от того, есть ли на них цена или нет.

ВЫБРАТЬ
ПоступлениеТиУ.Номенклатура,
Цены.Цена
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТиУ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ПО ПоступлениеТиУ.Номенклатура = Цены.Номенклатура

В ПРАВОМ все в точности да наоборот.

ПОЛНОЕ СОЕДИНЕНИЕ

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

При использовании в предыдущем примере полного соединения будут выбраны все позиции номенклатуры из документа «Поступление товаров и услуг» и все последние цены из регистра «Цены номенклатуры». Значения не найденных записей, как в первой, так и во второй таблице будут равняться NULL.

ВНУТРЕННЕЕ СОЕДИНЕНИЕ

Отличием ВНУТРЕННЕГО соединения от ПОЛНОГО является то, что если хотя бы в одной из таблиц не найдена запись, то запрос не выведет ее вообще. В результате будут выбраны только те номенклатурные позиции из документа «Поступление товаров и услуг», для которых в регистре сведений «Цены номенклатуры» есть записи, если в предыдущем примере заменить «ПОЛНОЕ» на «ВНУТРЕННЕЕ».

СГРУППИРОВАТЬ ПО (GROUP BY)

Группировка в запросах 1С позволяет сворачивать строки таблицы (группировочные поля) по определенному общему признаку (группируемым полям). Группировочные поля могут выводиться только с применением агрегатных функций.

Результатом следующего запроса будет список видов номенклатуры с максимальными ценами по ним.

ВЫБРАТЬ
,
МАКСИМУМ(Цены.Цена) КАК Цена
ИЗ

СГРУППИРОВАТЬ ПО
Цены.Номенклатура.ВидНоменклатуры

ИТОГИ

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

Итоги можно подводить по всей таблице целиком (с использованием ключевого слова «ОБЩИЕ»), по нескольким полям, по полям с иерархической структурой (ключевые слова «ИЕРАРХИЯ», «ТОЛЬКО ИЕРАРХИЯ»). При подведении итогов не обязательно использовать агрегатные функции.

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

ВЫБРАТЬ
Цены.Номенклатура.ВидНоменклатуры КАК ВидНоменклатуры,
Цены.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ИТОГИ
МАКСИМУМ(Цена)
ПО
ВидНоменклатуры

ИМЕЮЩИЕ (HAVING)

Данный оператор схож с оператором «ГДЕ», но используется только для агрегатных функций. Остальные поля, кроме используемых этим оператором, должны быть сгруппированы. Оператор «ГДЕ» не применим для агрегатных функций.

В рассмотренном ниже примере отбираются максимальные цены номенклатуры, если они превышают 1000, сгруппированные по виду номенклатуры.

ВЫБРАТЬ

МАКСИМУМ(Цены.Цена) КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
СГРУППИРОВАТЬ ПО
Цены.Номенклатура.ВидНоменклатуры
ИМЕЮЩИЕ
МАКСИМУМ(Цены.Цена) > 1000

УПОРЯДОЧИТЬ ПО

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

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

ВЫБРАТЬ
Сотрудники.Наименование КАК Наименование
ИЗ
Справочник.Сотрудники КАК Сотрудники
УПОРЯДОЧИТЬ ПО
Наименование
АВТОУПОРЯДОЧИВАНИЕ

Прочие конструкции языка запросов 1С

• ОБЪЕДИНИТЬ – результаты двух запросов в один.
• ОБЪЕДИНИТЬ ВСЕ – аналог ОБЪЕДИНИТЬ, но без группировки одинаковых строк.
• ПУСТАЯ ТАБЛИЦА – иногда используется при объединении запросов для указания пустой вложенной таблицы.
• ПОМЕСТИТЬ – создает временную таблицу для оптимизации сложных запросов 1С. Такие запросы называются пакетными.

Функции языка запросов

• ПОДСТРОКА обрезает строку с определенной позиции на указанное количество символов.
• ГОД…СЕКУНДА позволяют получить выбранное значение числового типа. Входным параметром является дата.
• НАЧАЛОПЕРИОДА и КОНЕЦПЕРИОДА используются при работе с датами. В качестве дополнительного параметра указывается тип периода (ДЕНЬ, МЕСЯЦ, ГОД и т. п.).
• ДОБАВИТЬКДАТЕ позволяет прибавить или отнять от даты указанное время определенного типа (СЕКУНДА, МИНУТА, ДЕНЬ и т. п.).
• РАЗНОСТЬДАТ определяет разницу между двумя датами с указанием типа выходного значения (ДЕНЬ, ГОД, МЕСЯЦ и т. п.).
• ЕСТЬNULL заменяет отсутствующее значение на указанное выражение.
• ПРЕДСТАВЛЕНИЕ и ПРЕДСТАВЛЕНИЕССЫЛКИ получают строковое представление указанного поля. Применяются для любых значений и только ссылочных соответственно.
• ТИП, ТИПЗНАЧЕНИЯ используются для определения типа входного параметра.
• ССЫЛКА является логическим оператором сравнения для типа значения реквизита.
• ВЫРАЗИТЬ используется для преобразования значения к нужному типу.
• ДАТАВРЕМЯ получает значение типа «Дата» из числовых значений (Год, Месяц, День, Час, Минута, Секунда).
• ЗНАЧЕНИЕ в запросе 1С используется для указания предопределенных значений — справочников, перечислений, планов видов характеристик. Пример использования: «Где ЮрФизЛицо = Значение(Перечисление.ЮрФизЛица.ФизЛицо) «.

Конструктор запросов

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

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

Текст самого запроса можно просмотреть, нажав в левом нижнем углу на кнопку «Запрос». В данной форме его можно откорректировать вручную или скопировать.

Консоль запросов

Для быстрого просмотра результата запроса в режиме «Предприятие», либо отладки сложных запросов используется . В ней пишется текст запроса, устанавливаются параметры, и показывается его результат.

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

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

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

Существует четыре вида соединений таблиц, которые обрабатывает механизм запросов 1С:Прсдприятия: Внутреннее, Левое внешнее. Правое внешнее, Полное Внешнее (или. проще. Внутреннее, Левое, Правое, Полное).

Внутреннее соединение

[ВНУТРЕННЕЕ] СОЕДИНЕНИЕ означает, что из обеих исходных таблиц – источников данных в результат запроса необходимо включить только те комбинации записей, которые соответствуют указанному условию. Остальные записи в результат не попадают.Ключевое слова ВНУТРЕННЕЕ можно не указывать вообще, оно повышает наглядность и удобочитаемость текста запроса.

Самый простейший вид соединения — внутреннее. В этом случае запрос просто находит пары строк с совпадающим значением» ключа (в данном примере, как и во всех последующих, в качестве ключевого поля используется поле «Контрагент»).

Левое (Правое) соединение

ЛЕВОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ означает, что в результат запроса надо включить комбинации записей из обеих исходных таблиц, которые соответствуют указанному условию. Но, в отличие от внутреннего соединения, в результат запроса надо включить также еще и записи из первого (указанного слева от слова СОЕДИНЕНИЕ) источника, для которых не найдено соответствующих условию записей из второго источника. Таким образом, в результат запроса будут включены все записи из первого источника; они будут соединены с записями из второго источника при выполнении указанного условия. Строки результата запроса, для которых не найдено соответствующих условию записей из второго источника, будут содержать NULL в полях, формируемых на основании записей из этого источника.

Ситуация осложняется, когда источники совпадают не полностью. ‘Го есть, в одной таблице есть запись с некоторым значением ключа, а в другой такой записи нет. На схеме показана ситуация, когда запись есть в таблице контрагентов, но нет в таблице продаж. Это означает, что некий контрагент ничего у нас не покупал, хотя в справочнике контрагентов он у нас имеется (например, попросил человек выписать ему счёт на оплату, а покупать передумал; вполне жизненная ситуация). В этом случае в выборке на месте отсутствующей записи появится значение Null.

Исходные таблицы:

Полное соединение

ПРАВОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ означает, что в результат запроса надо включить комбинации записей из обеих исходных таблиц, которые соответствуют указанному условию. Кроме того, в результат запроса надо включить также еще и записи из второго (указанного справа от слова СОЕДИНЕНИЕ) источника, для которых не найдено соответствующих условию записей из первого источника.Таким образом, в результат запроса будут включены все записи из второго источника; они будут соединены с записями из первого источника при выполнении указанного условия. Строки результата запроса, для которых не найдено соответствующих условию записей из первого источника, будут содержать NULL в полях, формируемых на основании записей из этого источника.

Полное внешнее соединение, это, как ясно уже из названия, дальнейшее развитие Левого (или Правого) соединений. При организации полного соединения важно учитывать такое обстоятельство: при соединении данного вида в запрос попадают все записи из обеих таблиц. Иначе говоря, значение ключевого ноля нам нужно будет получать и из левой, и из правой таблиц. Обратите внимание: обязательно из обеих таблиц! Один из вариантов решения такой: создать для этого дополнительное поле, в котором делать проверку на Null. Если ключ в одной из таблиц равен Null, тогда нужно брать его значение из другой таблицы.

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

Исходные таблицы:

Закупки
Контрагент	Сумма
Иванов	4000
Андреев	3500

Продажи
Контрагент	Сумма
Иванов	5000
Петров	7500
Сидоров	15000

Таблица после полного соединения, со всеми полями исходных таблиц:

Действующие контрагенты
КонтрагентЗакупки	СуммаЗакупки	КонтрагентПродажи	СуммаПродажи
Иванов	4000	Иванов	5000
Null	Null	Петров	7500
Null	Null	Сидоров	15000
Андреев	3500	Null	Null

Окончательный вид таблицы, в которой контрагенты сведены в одно поле:

Действующие контрагенты
Контрагент	СуммаЗакупки	СуммаПродажи
Иванов	4000	5000
Петров		7500
Сидоров		15000
Андреев	3500

Ниже приводится вариант соответствующего запроса. Обратите внимание, что данные по контрагентам мы получаем не просто из документов, а из подзапросов. В них мы выполняем группировку по контрагентам таким образом, что бы каждый контрагент был представлен в выборке но одному разу. И, естественно, не забываем про Null:

ВЫБРАТЬ ЕСТЬNULL(Приход.Контрагент. Расход.Контрагент) КАК Контрагент, ЕСТЬNULL(Приход.Сумма, 0) КАК СуммаЗакупки, ECTЬNULL(Pacxoд.Сумма, 0) КАК СуммаПродажи ИЗ (ВЫБРАТЬ Приходная.Контрагент КАК Контрагент, СУММА(Приходная.Сумма) КАК Сумма ИЗ Документ.Приходная КАК Приходная СГРУППИРОВАТЬ ПО Приходная.Контрагент) КАК Приход ПОЛНОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ Расходная.Контрагент КАК Контрагент, СУММА(Расходная.Сумма) КАК Сумма ИЗ Документ.Расходная КАК Расходная СГРУППИРОВАТЬ ПО Расходная.Контрагент) КАК Расход ПО Приход.Контрагент = Расход.Контрагент

Внимание! Перед вами ознакомительная версия урока, материалы которого могут быть неполными.

Войдите на сайт как ученик

Войдите как ученик, чтобы получить доступ к материалам школы

Язык запросов 1С 8.3 для начинающих программистов: соединения

Соединения в запросах

Соединение - одна из самых важных и нужных операций, выполняемых реляционными системами управления базами данных.

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

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

У нас в базе есть справочник Клиенты :

Справочник Цвета:

И справочник Ассоциации :

Наша задача вывести любимые ассоциации клиентов, основываясь на цвете.

Таким образом для Наташи любимой ассоциацией будет трава, так как её любимый цвет зелёный. А для Петра - солнце. Вы читаете ознакомительную версию урока, полноценные уроки находятся .

Для Андрея вообще нет подходящей ассоциации, так его любимый цвет красный, а ассоциаций красного цвета в базе нет.

Будем решать задачу постепенно.

Сначала запросим всех клиентов и их любимые цвета :

Затем запросим все ассоциации и их цвета :

Если мы попробуем выполнить этот запрос, то получим ошибку:

Причина ошибки в том, что поле Наименование присутствует сразу в обеих таблицах (Клиенты и Ассоциации ) и система просто не знает поле из какой именно таблицы имеется в виду.

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

Таким образом полное название поля Наименование из таблицы Клиенты будет Справочник.Клиенты.Наименование .

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

Перекрёстное соединение

Перепишем предыдущий запрос с полными именами полей:

Только что мы произвели перекрёстное соединение двух таблиц. Обратите внимание на то, каким образом сформировался результат:

Внутреннее соединение

Очевидно, что результат перекрестного соединения двух таблиц не есть решение нашей задачи. Нам нужны не все записи из перекрёстного соединения, а только те у которых поля ЛюбимыйЦвет и Цвет имеют одинаковое значение:

Чтобы получить эти записи добавим к предыдущему запросу секцию ГДЕ :

Это то, что нужно - мы решили, поставленную задачу!

В последнем запросе мы использовали перекрёстное соединение с дополнительным условием (в секции ГДЕ ). Вы читаете ознакомительную версию урока, полноценные уроки находятся . Такое соединение называется внутренним .

Есть ещё один вариант написания того же самого внутреннего соединения :

Сравните этот и предыдущий запрос. Они совершенно одинаковы с точки зрения платформы, просто имеют разный синтаксис. И этот и предыдущий запросы содержат внутреннее соединение таблицы Клиенты с таблицей Ассоциации по полям ЛюбимыйЦвет и Цвет соответственно.

Левое соединение

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

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

Перепишем запрос так, чтобы в результат попадали в том числе те записи из первой таблицы, для которых не нашлось ни одной пары из второй таблицы (в данном случае Андрей):

Такое соединение называется левым соединением .

Р езультат левого соединения представляет из себя: все записи из внутреннего соединения ПЛЮС все записи из первой таблицы , не попавшие во внутреннее

Правое соединение

Но давайте снова вернёмся к внутреннему соединению:

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

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

Такое соединение называется правым соединением .

Результат правого соединения представляет из себя: ПЛЮС все записи из второй таблицы , не попавшие во внутреннее соединение (для которых не нашлось пары).

Полное соединение

А что если нам нужно, чтобы в результат запроса попадали помимо внутреннего соединения Андрей и Снег одновременно?

Для этого потребуется совместить результаты левого и правого соединений. Такой вид соединения уже придуман и называется полным соединением:

Результат полного соединения представляет из себя: все записи из внутреннего соединения ПЛЮС все записи из первой таблицы, не попавшие во внутреннее соединение (для которых не нашлось пары) ПЛЮС все записи из второй таблицы, не попавшие во внутреннее соединение (для которых не нашлось пары).

Псевдонимы таблиц

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

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

Давайте перепишем последний запрос так, чтобы при формировании полных имён полей вместо Справочник.Клиенты можно было использовать псевдоним К , а вместо Справочник.Ассоциации - псевдоним А :

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

Обработка NULL

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

Чему равны значения полей Ассоциация и ЕёЦвет для первой строчки? А что вы скажете насчет полей Клиент и ЕгоЦвет для последней строки?

Они равны NULL, которое как мы уже знаем означает отсутствие какого либо значения:

А так как NULL означает отсутствие значения, то любая попытка выполнить с ним какую-либо операцию (сравнение, сложение...) вызовет неопределенное поведение базы данных, непредсказуемую ошибку.

Поэтому обязательной считается обработка значений NULL всегда, когда они могут возникнуть.

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

В данном случае для полей Клиент и Ассоциация в случае обнаружения NULL мы будем подставлять пустую строку "".

А вот поля ЕгоЦвет и ЕёЦвет являются ссылками на элементы справочника Цвета , поэтому в них можно подставлять только значения являющиеся ссылками указанных типов. Каждый ссылочный тип (например, Справочник или Документ) имеет предопределенный элемент ПустаяСсылка . Чтобы указать его значение в запросе воспользуемся функцией ЗНАЧЕНИЕ .

Для определения того, что в поле попало NULL будем использовать уже знакомую нам по прошлым урокам функцию ЕСТЬNULL :

ВЫБРАТЬ ЕСТЬNULL( К. Наименование, "" ) КАК Клиент, ЕСТЬNULL( К. ЛюбимыйЦвет, ЗНАЧЕНИЕ(Справочник. Цвета. ПустаяСсылка) ) КАК ЕгоЦвет, ЕСТЬNULL( А. Наименование, "" ) КАК Ассоциация, ЕСТЬNULL( А. Цвет, ЗНАЧЕНИЕ(Справочник. Цвета. ПустаяСсылка) ) КАК ЕёЦвет ИЗ Справочник. Клиенты КАК К ПОЛНОЕ СОЕДИНЕНИЕ Справочник. Ассоциации КАК А ПО К. ЛюбимыйЦвет = А. Цвет

С виду (из консоли запросов) результат не изменился. Мы по-прежнему видим пустые поля. Но это только потому, что строковые представления у NULL и у пустых полей всех типов совпадают и равны пустой строке.

На самом же деле эти пустые поля уже не есть NULL (отсутствие значения), теперь в них появились значения (пустые), с которыми уже можно работать (совершать операции).

Запомните пустое значение и отсутствие значение - это две большие разницы.

Соединение более двух таблиц

Можно последовательно соединять сколько угодно таблиц.

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

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

Пройдите тест

Начать тест

1. Соединения используются для того, чтобы