Язык запросов «1С:Предприятия 8»,
O-е стереотипное издание
Электронная книга в формате pdf; fpBN 978-R-9S77-3OMN-M.
Электронный аналог печатного издания
«Язык запросов "NС:Предприятия 8"I O-е стереотипное издание»
(fpBN 978-R-9S77-3NOM-4I М.: ООО «NС-Паблишинг»I OMON;
артикул печатной книги по прайс-листу фирмы «NС»: 4SMNR4SN4RSR9;
по вопросам приобретения печатных изданий издательства «NС-Паблишинг» обращайтесь к партнеру
«NС»I обслуживающему вашу организациюI или к другим партнерам фирмы «NС»I в магазины
«NС Интерес»I а также в книжные и интернет-магазины).
Запросы – это один из базовых механизмов «NС:Предприятия» наряду со
встроенным языкомI который позволяет читать и обрабатывать данныеI хранящиеся
в базе. Для составления запросов «NС:Предприятие» использует собственный языкI
основанный на pni.
Эта книга поможет начинающим разработчикамI не знакомым с pniI освоить язык
запросов «NС:Предприятия». Книга также будет полезна и темI кто имеет опыт
составления pni-запросов в других средах разработки: язык запросов
«NС:Предприятия» содержит значительное количество расширенийI
ориентированных на специфику финансово-экономических задач.
В книге рассматривается значительное количество практических примеров. Для
создания примеров использована версия 8.P.P.S8T платформы «NС:Предприятие».
Книга выпущена под редакцией Максима Радченко.
Приложение к книге включает демонстрационные конфигурацииI иллюстрирующие
примеры книгиI и учебную версию платформы "NС:Предприятие 8.P".
Скачайте материалы и учебную версию на странице http:LLits.Nc.ruLbook_demoLI
раскройте архив и следуйте инструкциям по установке.
Данное O-е издание является стереотипнымI не отличается по содержанию от N-го
издания книги с тем же названиемI выпущенного в OMNP году издательством
«NС-Паблишинг» (fp_k 9T8-5-9STT-N98T-5I артикул «NС» QSMN5QSNM8MO9).
Интернет-конференция для начинающих разработчиков
http:LLdevtrainingforum.v8.Nc.ruLforum.
Оглавление
Глава 1. Механизм запросов................................................................................7
Как хранятся данные в «1С:Предприятии»...................................................................................7
Исходные таблицы для запросов.................................................................................................17
Реальные таблицы.....................................................................................................................19
Виртуальные таблицы...............................................................................................................20
Язык запросов «1С:Предприятия»................................................................................................21
Общая схема выполнения запросов.........................................................................................22
Синтаксис текста запросов........................................................................................................24
Примеры использования языка запросов для получения данных из одной таблицы.......27
Как получить все данные из таблицы..................................................................................................... 27
Как получить только определенные поля для всех записей из таблицы............................................ 29
Как расположить полученные записи в нужном порядке..................................................................... 33
Как упорядочить записи таблицы по ссылочному полю........................................................................ 36
Как получить текстовое представление ссылочного поля.................................................................... 38
Как получить только первые несколько записей
с наибольшими значениями некоторого поля........................................................................................ 41
Как получить записи, в которых определенные поля не содержат одинаковых значений .............. 42
Как получить общее количество записей в таблице
и количество записей с различным значением некоторого поля......................................................... 44
Как получить записи из таблицы, отобранные по некоторому условию.............................................. 45
Как получить записи таблицы, содержащие строки, соответствующие заданному шаблону............ 47
Как задать произвольное значение отбора записей из таблицы......................................................... 49
Как получить данные из табличной части некоторого документа........................................................ 52
Как получить данные из табличной части документа в качестве вложенной таблицы..................... 55
Как получить записи иерархической таблицы и расположить их в порядке иерархии..................... 57
Как отобрать записи иерархической таблицы по условию................................................................... 62
Как узнать среднюю цену, по которой продавался товар..................................................................... 68
4
Язык запросов «1С:Предприятия 8»
Как узнать общее количество и сумму продаж каждого товара в разрезе покупателей................... 71
Как узнать среднюю цену поступления товара, не группируя сами записи......................................... 74
Расчет итогов для иерархического справочника................................................................................... 76
Расчет итогов по нескольким полям....................................................................................................... 78
Расчет общих итогов................................................................................................................................ 80
Примеры использования выражений в списке полей выборки запроса............................................. 82
Примеры использования языка запросов
для получения данных из нескольких таблиц..........................................................................87
Как использовать данные одного запроса внутри другого запроса..................................................... 87
Как получить данные из разных таблиц для одного и того же поля.................................................... 91
Как получить данные из разных таблиц, связанных несколькими соединениями............................ 100
Как получить данные из таблицы, на которую ссылается поле другой таблицы............................. 110
Как получить данные из разных таблиц, не связывая, а дополняя их............................................... 114
Временные таблицы и пакетные запросы............................................................................................ 117
Глава 2. Работа с запросами во встроенном языке....................................125
Конструктор запроса................................................................................................................125
Создание простого запроса.....................................................................................................127
Связи источников запроса.......................................................................................................132
Объединение запросов............................................................................................................134
Создание пакетного запроса, использующего временную таблицу....................................140
Выполнение запросов из встроенного языка.............................................................................146
Создание запроса....................................................................................................................147
Передача параметров в запрос.............................................................................................148
Получение выборки из результата запроса...........................................................................151
Обход выборки из результата запроса..................................................................................152
Линейный (прямой) порядок обхода..................................................................................................... 155
Иерархический порядок обхода............................................................................................................ 156
Обход по группировкам.......................................................................................................................... 160
Обход выборки результата запроса, содержащего данные табличной части.................................. 162
Обработка результатов запроса с помощью конструктора запроса....................................164
Обход выборки........................................................................................................................................ 164
Вывод в табличный документ.............................................................................................................. 167
Вывод в диаграмму............................................................................................................................... 172
Выгрузка результата запроса в таблицу или дерево значений..........................................176
Использование временных таблиц с помощью встроенного языка....................................180
Использование таблицы значений в качестве источника временной таблицы............................... 184
Примеры решения различных задач с использованием запросов......................................189
Поиск всех родителей для элемента иерархического справочника................................................... 189
Создание запроса из произвольного источника.................................................................................. 192
Создание кросс-отчета........................................................................................................................... 196
Вывод итогов по периодам с заданной периодичностью.................................................................... 201
Оглавление
5
Глава 3. Решение прикладных задач.............................................................207
Хранение информации................................................................................................................208
Регистры сведений...................................................................................................................208
Получение данных из независимых непериодических регистров сведений..................................... 209
Получение данных из периодических регистров сведений................................................................ 212
Получение данных из регистров сведений, подчиненных регистратору........................................... 219
Планы видов характеристик....................................................................................................221
Пример 1................................................................................................................................................. 222
Пример 2................................................................................................................................................. 223
Пример 3................................................................................................................................................. 225
Получение значений характеристик из регистра сведений................................................................ 226
Учет движения средств................................................................................................................229
Регистры накопления...............................................................................................................229
Бухгалтерский учет.......................................................................................................................265
Планы счетов............................................................................................................................265
Планы видов характеристик – виды субконто.......................................................................270
Регистры бухгалтерии..............................................................................................................273
Получение движений регистра бухгалтерии........................................................................................ 276
Получение остатков................................................................................................................................ 277
Получение оборотов............................................................................................................................... 291
Получение оборотов между корреспондирующими счетами.............................................................. 298
Получение остатков и оборотов............................................................................................................ 305
Получение движений с субконто........................................................................................................... 313
Сложные периодические расчеты..............................................................................................316
Планы видов расчета...............................................................................................................316
Регистры расчета.....................................................................................................................322
Получение данных из регистра расчета............................................................................................... 324
Получение данных о фактическом периоде действия записи для расчета...................................... 327
Получение данных графика для расчета записи................................................................................. 330
Получение базы для расчета записей.................................................................................................. 331
Перерасчеты........................................................................................................................................... 337
Глава 4. Оптимизация запросов.....................................................................339
Индексирование таблиц...............................................................................................................340
Способы индексирования таблиц...........................................................................................344
Эффективное использование индексов.................................................................................348
Пример 1................................................................................................................................................. 349
Пример 2................................................................................................................................................. 350
Пример 3................................................................................................................................................. 351
Пример 4................................................................................................................................................. 352
6
Язык запросов «1С:Предприятия 8»
Пример 5................................................................................................................................................. 353
Общие рекомендации............................................................................................................................ 354
Причины неоптимальной работы запросов и основные направления их оптимизации.........355
Общие рекомендации..............................................................................................................355
Не использовать запросы в цикле.........................................................................................356
Не использовать в запросе функции от параметров...........................................................358
Использовать параметры виртуальных таблиц....................................................................359
Соответствие индексов и условий запроса............................................................................360
Не использовать соединения с вложенными запросами и с виртуальными таблицами...360
Соединения с вложенными запросами................................................................................................. 360
Соединения с виртуальными таблицами............................................................................................. 363
Не использовать вложенные запросы в условиях соединения..........................................365
Исключить получение поля «Ссылка» через точку...............................................................365
Ограничить получение данных через точку от полей составного ссылочного типа...........366
Исключить вывод ссылочных полей в отчет........................................................................368
Глава 1.
Механизм запросов
Как хранятся данные в «1С:Предприятии»
Прикладные решения, разработанные на платформе «1С:Предприятие»,
работают с данными, которые интерактивно вводит пользователь, заполняя
различные формы ввода справочников, документов и т. д. Из этих форм данные
с помощью встроенного языка записываются в базу данных и хранятся в ней.
На основе введенных данных пользователю обычно требуется получить некоторую обобщенную информацию, необходимые ему итоговые данные, отчеты
и т. д. Для этого используется механизм запросов, который рассматривается
в данной книге.
Поэтому прежде чем начинать осваивать язык запросов, важно понять,
как хранятся данные в «1С:Предприятии». Для хранения данных
«1С:Предприятие» использует реляционные базы данных. Реляционная база
данных представляет собой совокупность различной информации, представленной в виде двумерных таблиц. Таблица базы данных состоит из набора
строк и столбцов. Каждая строка (запись) этой таблицы характеризуется
рядом значений, содержащихся в ее столбцах (полях).
Для примера можно привести данные о клиентах компании, хранящиеся
в справочнике клиентов. В самом упрощенном виде данные о клиентах
хранятся в одной – основной таблице, где каждому клиенту соответствует
одна запись, имеющая один и тот же набор полей, например, Код, Наименование, Адрес, Телефон и т. п. (табл. 1.1).
8
Язык запросов «1С:Предприятия 8»
Таблица 1.1. Справочник клиентов в информационной базе (основная таблица)
Ссылка Код
Ref1
Ref2
Ref3
Наименование
Адрес
Телефон
000000001 Соколов Иван Андреевич
Москва, …
8-916-222-33-55
000000002 Орлов Сергей Иванович
Москва, …
8-926-555-66-77
000000003 Маслова Ирина Николаевна Санкт-Петербург, … 8-915-4447799
Рассмотрим подробнее, как формируется структура таблицы справочника
в информационной базе «1С:Предприятия».
Например, при создании в конфигураторе справочника Клиенты платформа
«1С:Предприятие» автоматически создает в информационной базе основную
таблицу этого справочника с полями Ссылка, Код, Наименование, ПометкаУдаления, Предопределенный и ВерсияДанных.
ПРИМЕЧАНИЕ
Имена полей Код, Наименование и т. п. мы используем для простоты изложения. На самом деле имена полей таблиц в базе данных будут техногенными, например, _Code, _Description и т. п.
Затем пользователь в режиме 1С:Предприятие заполняет справочник данными,
которые сохраняются в базе данных (рис. 1.1).
Рис. 1.1. Структура простого справочника в конфигураторе,
в «1С:Предприятии» и в информационной базе
Глава 1. Механизм запросов
9
Поле Ссылка является уникальным идентификатором записи о клиенте, поля
Код, Наименование, ПометкаУдаления, Предопределенный и ВерсияДанных
являются стандартными реквизитами, которые платформа добавляет в любой
справочник.
На рис. 1.1 также показаны три понятия, которые важно отличать. В конфигураторе создается объект конфигурации Справочник Клиенты. Данные
этого объекта конфигурации (все записи справочника) вводятся в режиме
1С:Предприятие, затем эти данные записываются в таблицу справочника
Клиенты в информационной базе. В этой таблице объектом базы данных
является одна запись (данные одного элемента справочника), однозначно
идентифицирующаяся значением поля Ссылка.
Каждый элемент справочника, как правило, содержит некоторую дополнительную информацию, которая подробнее описывает этот элемент. Например,
все элементы справочника Клиенты могут содержать дополнительную информацию об адресе и телефоне каждого клиента. Для описания этой информации
используются реквизиты справочника.
При добавлении реквизитов справочника в конфигураторе платформа создает
поля соответствующего типа в основной таблице справочника (рис. 1.2).
Рис. 1.2. Структура справочника, имеющего реквизиты,
в конфигураторе, в «1С:Предприятии» и в информационной базе
10
Язык запросов «1С:Предприятия 8»
Если справочник является иерархическим, то платформа добавляет в структуру основной таблицы справочника в информационной базе поле Родитель
и поле ЭтоГруппа (в случае, если справочник имеет тип иерархии Иерархия
групп и элементов), рис. 1.3.
Рис. 1.3. Структура иерархического справочника
в конфигураторе и в информационной базе
Например, справочник Товары является иерархическим с иерархией групп
и элементов. При этом в таблице базы данных для записей, являющихся
группой, поле ЭтоГруппа принимает значение Истина; для записей, не являющихся группой, поле ЭтоГруппа принимает значение Ложь, а поле Родитель
является ссылкой на родительскую запись. Благодаря этому можно получить
информацию о дочерних записях и родителях для каждого элемента справочника (рис. 1.4).
ПРИМЕЧАНИЕ
Поле Родитель на самом деле хранит ссылку на родительскую запись, но для
большей ясности в таблице на рис. 1.4 в этом поле отражено представление ссылки в виде наименования.
Если справочник является подчиненным, например справочник РасчетныеСчета подчинен справочнику Поставщики, то в основную таблицу
подчиненного справочника платформа добавляет поле Владелец, которое
ссылается на элемент справочника-владельца (рис. 1.5).
Глава 1. Механизм запросов
11
Рис. 1.4. Содержимое иерархического справочника
в «1С:Предприятии» и в информационной базе
Благодаря этому можно получить информацию, какие элементы справочника-владельца владеют какими элементами подчиненного справочника,
например, какие расчетные счета относятся к конкретному поставщику
(рис. 1.6).
12
Язык запросов «1С:Предприятия 8»
Рис. 1.5. Структура подчиненного справочника
в конфигураторе и в информационной базе
Рис. 1.6. Содержимое подчиненного справочника и справочника-владельца
в «1С:Предприятии» и в информационной базе
Глава 1. Механизм запросов
13
ПРИМЕЧАНИЕ
Поле Владелец подчиненного справочника на самом деле хранит ссылку на запись справочника-владельца, но для большей ясности в таблице
на рис. 1.6 в этом поле отражено представление ссылки в виде наименования.
Кроме реквизитов каждый элемент справочника может содержать некоторый
набор информации, которая одинакова по своей структуре, но различна
по количеству и относится к разным элементам справочника. Например,
каждый элемент справочника Поставщики может содержать информацию
о договорах, заключенных с этим поставщиком. Для каждого поставщика
состав информации и количество записей в ней будет разным, а структура
информации (например, дата начала и окончания действия договоров) –
одинакова. Для описания подобной информации могут быть использованы
табличные части справочника.
При добавлении в справочник табличной части в информационной
базе создается подчиненная таблица со стандартными полями Ссылка
и НомерСтроки и реквизитами табличной части, заданными в конфигураторе.
Таблица, содержащая табличную часть, связана по полю Ссылка с основной
таблицей. Благодаря этому можно получить информацию из табличной части,
относящуюся к конкретному элементу справочника (рис. 1.7).
ПРИМЕЧАНИЕ
В поле Ссылка подчиненной таблицы, содержащей табличную часть,
на самом деле хранится ссылка на запись основной таблицы, но для большей ясности в таблице на рис. 1.7 в этом поле отражено представление
ссылки в виде наименования.
В информационной базе создается столько подчиненных таблиц, сколько
табличных частей задано у справочника.
Таким образом, на примере справочника, имеющего табличную часть, мы
видим, что одному объекту конфигурации в информационной базе могут
соответствовать несколько таблиц – основная и одна или несколько подчиненных ей по полю Ссылка таблиц. При этом одному объекту базы данных
соответствует одна запись в основной таблице и одна или несколько записей
в подчиненных таблицах, содержащих табличные части (см. рис. 1.7).
14
Язык запросов «1С:Предприятия 8»
Рис. 1.7. Справочник с табличной частью в конфигураторе,
в «1С:Предприятии» и в информационной базе
Теперь рассмотрим другой пример, когда поле ссылочного типа служит для
связи данных двух разных объектов конфигурации. Важно понимать, что
ссылочные типы данных не существуют изначально в конфигурации, а появляются при создании соответствующих объектов конфигурации. Причем
для каждого объекта конфигурации во встроенном языке создается свой тип
ссылки. То есть при создании справочника Товары появляется ссылочный тип
данных СправочникСсылка.Товары, при создании справочника Клиенты – тип
СправочникСсылка.Клиенты, при создании документа Событие – тип ДокументСсылка.Событие и т. д. Поля, содержащие данные такого типа, мы будем
для краткости называть иногда ссылочными полями.
Например, в конфигурации существует документ, имеющий поле, ссылающееся на справочник Клиенты (рис. 1.8).
Глава 1. Механизм запросов
15
Рис. 1.8. Документ, имеющий поле ссылочного типа,
в конфигураторе, в «1С:Предприятии» и в информационной базе
ПРИМЕЧАНИЕ
Поле Клиент документа Событие на самом деле хранит ссылку на запись
справочника Клиенты, но для большей ясности в таблице на рис. 1.8 в этом
поле отражено представление ссылки в виде наименования.
В приведенном примере поле Клиент документа Событие имеет ссылочный
тип СправочникСсылка.Клиенты, а значениями этого поля являются ссылки
на конкретные элементы справочника Клиенты. В этом случае, обращаясь
к полю Клиент в документе, мы можем получить любые данные о клиенте,
на которого ссылается данное поле.
Таким образом, мы рассмотрели примеры хранения в информационной базе
«1С:Предприятия» ссылочных типов данных, таких как справочники, документы, планы видов характеристик и т. д.
В заключение рассмотрим, как хранятся в информационной базе
«1С:Предприятия» нессылочные данные, доступ к которым нельзя получить
через поле Ссылка.
16
Язык запросов «1С:Предприятия 8»
Например, периодический регистр сведений предназначен для хранения
данных (ресурсов) в разрезе измерений с привязкой ко времени. Благодаря
стандартному полю Период, регистр сведений может хранить не только актуальные значения данных, но и историю их изменения во времени.
Рис. 1.9. Периодический регистр сведений в конфигураторе,
в «1С:Предприятии» и в информационной базе
Глава 1. Механизм запросов
17
На основе объекта конфигурации Регистр сведений платформа создает
в информационной базе таблицу, в которой может храниться произвольная
информация, «привязанная» к набору измерений и периоду. Например,
периодический регистр сведений Цены, имеющий измерение Товар и ресурс
Цена, хранит изменяющуюся во времени информацию о ценах на товары
(рис. 1.9).
ПРИМЕЧАНИЕ
Поле Товар в таблице регистра на самом деле хранит ссылку на запись
справочника Товары, но для большей ясности на рис. 1.9 в этом поле отражено представление ссылки в виде наименования.
Помимо измерений, ресурсов и реквизитов, заданных в конфигураторе,
у таблицы периодического регистра сведений в информационной базе
создается стандартное поле Период, благодаря этому регистр может хранить
одинаковую информацию для одних и тех же измерений, но для различных
периодов.
Как мы видим, в таблице регистра нет поля Ссылка, посредством которого мы
можем сослаться на конкретную запись регистра. Ключом записи, однозначно
идентифицирующим запись, является в случае периодического регистра
сведений совокупность значений измерений регистра и периода.
В целом для нессылочных данных мы не можем получить какую-то
конкретную запись из таблицы. Но мы можем получить некоторый набор
записей по какому-либо условию (например, отобрать данные регистра
сведений по периоду) и затем перебирать его в цикле.
Исходные таблицы для запросов
Прежде чем переходить к конкретным примерам использования языка
запросов, остановимся также на составе таблиц базы данных, являющихся
источниками запросов. Состав таблиц, доступных для запроса, и их описание
мы можем увидеть в синтакс-помощнике в разделе Работа с запросами ►
Таблицы запросов.
Важно понимать, что прямого доступа к физическим таблицам, в которых
хранится информация в базе данных, из «1С:Предприятия» получить нельзя.
Это связано с тем, что в прикладном решении могут использоваться разные
СУБД, имеющие свою специфику, а текст запроса должен быть универсальным и одинаково работать на любой используемой СУБД. Поэтому при
выполнении запроса платформа автоматически транслирует текст запроса
в набор инструкций, которые «понимает» конкретная СУБД. Кроме того,
18
Язык запросов «1С:Предприятия 8»
физические таблицы и поля в них имеют техногенные имена, из которых
непонятно, что именно хранится в данном поле.
Поэтому с помощью запросов мы обращаемся к данным не напрямую, а через
специальную «прослойку» в виде таблиц языка запросов. Этот процесс
можно представить на следующей схеме (рис. 1.10).
Рис. 1.10. Доступ к данным в «1С:Предприятии»
Таким образом, все таблицы, к которым можно обратиться с помощью
языка запросов, являются придуманными, воображаемыми, в большей или
меньшей степени соответствующими реальным физическим таблицам СУБД.
Однако по степени похожести на физические таблицы их принято разделять
на реальные и виртуальные таблицы.
Глава 1. Механизм запросов
19
Реальные таблицы
Отличительной особенностью реальных таблиц является то, что они содержат
данные какой-либо одной физической таблицы, хранящейся в базе данных,
и то, что реальная таблица очень похожа на свою физическую таблицу.
Например, реальной является таблица Справочник.Клиенты, соответствующая
справочнику Клиенты, или таблица РегистрСведений.Цены, соответствующая
регистру сведений Цены.
Для примера сравним структуру реальной и физической таблицы, хранящей
данные справочника (табл. 1.2).
Таблица 1.2. Реальная и физическая таблица справочника «Клиенты»
Реальная таблица
Физическая таблица
Справочник.Клиенты
Ссылка
ВерсияДанных
ПометкаУдаления
Предопределенный
Родитель
Владелец
ЭтоГруппа
Код
Наименование
Как мы видим, не все поля реальной и физической таблицы соответствуют
друг другу. Например, поле Представление – виртуальное, то есть оно не
хранится в физической таблице базы данных, а генерируется в момент
выполнения запроса.
подробнее
О поле Представление рассказано в разделе «Как получить текстовое представление ссылочного поля», стр. 38.
О поле Имя табличной части рассказано в разделе «Как получить данные
из табличной части документа в качестве вложенной таблицы», стр. 55.
Но в целом реальная таблица очень похожа на физическую по набору полей,
а также количество записей в обеих таблицах одинаково.
20
Язык запросов «1С:Предприятия 8»
Реальные таблицы подразделяются на объектные (ссылочные) и необъектные
(нессылочные).
В объектных (ссылочных) таблицах представлена информация ссылочных
типов данных (справочники, документы, планы видов характеристик и т. д.).
А в необъектных (нессылочных) – всех остальных типов данных (константы,
регистры и т. д.).
Отличительной особенностью объектных (ссылочных) таблиц является то,
что они включают в себя стандартное поле Ссылка, которое позволяет однозначно идентифицировать каждую запись (данные об объекте базы данных).
Эти таблицы могут быть иерархическими, подчиненными, и поля таких
таблиц могут содержать вложенные таблицы (табличные части).
Виртуальные таблицы
Виртуальные таблицы формируются в момент выполнения запроса
на основе реальных таблиц базы данных. Например, виртуальная таблица
РегистрСведений.Цены.СрезПоследних формируется на основе таблицы регистра сведений Цены, рис. 1.11.
ПРИМЕЧАНИЕ
Поле Товар в таблице регистра на самом деле хранит ссылку на запись
справочника Товары, но для большей ясности на рис. 1.11 в этом поле отражено представление ссылки в виде наименования.
Как мы видим из рисунка 1.11, при заполнении цен товаров в регистре
сведений Цены в «1С:Предприятии» данные за период по каждому товару
сохраняются в физической таблице регистра сведений в базе данных.
Мы уже рассказывали выше про периодический регистр сведений (см. рис.
1.9). Благодаря стандартному полю Период регистр может хранить изменяющуюся во времени информацию для одних и тех же измерений, но для
различных периодов.
При обращении запросом к виртуальной таблице РегистрСведений.Цены.СрезПоследних мы получим не все записи физической таблицы, а только
последние по времени данные о ценах на товары. То есть срез последних
записей регистра сведений возвращает по каждому значению измерения
(Товары) одну наиболее позднюю (по времени, по значению поля Период)
запись.
Таким образом, виртуальные таблицы мало похожи на какую-то физическую
таблицу и содержат совсем иной состав записей, чем реальные таблицы.
Глава 1. Механизм запросов
21
Рис. 1.11. Физическая и виртуальная таблица регистра сведений
Язык запросов «1С:Предприятия»
Механизм запросов позволяет получить доступ к разнообразной информации,
хранящейся в базе данных «1С:Предприятия». Путем выполнения запроса
к информационной базе из всей совокупности информации можно получить
различные выборки данных из одной или нескольких взаимосвязанных таблиц,
отобранных по определенному условию, отсортированных определенным
22
Язык запросов «1С:Предприятия 8»
образом и пр. Далее полученные данные могут быть проанализированы для
решения различных прикладных задач, построения отчетов и т. п.
Однако следует иметь в виду, что с помощью запросов можно только
прочитать нужную информацию из базы данных, но изменить ее и записать
обратно при помощи запроса нельзя – для этого нужно использовать средства
встроенного языка.
Общая схема выполнения запросов
Запрос формируется и выполняется разработчиком из встроенного языка.
Для этого предназначены следующие программные объекты:
■■ Запрос,
■■ РезультатЗапроса,
■■ ВыборкаИзРезультатаЗапроса.
Не углубляясь в детали, рассмотрим самую распространенную и простейшую
схему выполнения запроса.
подробнее
Другие варианты выполнения запросов и обработки их результатов будут
рассмотрены позднее в разделе «Выполнение запросов из встроенного
языка», стр. 146.
1. Сначала во встроенном языке создается объект Запрос (листинг 1.1).
Листинг 1.1. Создание запроса
Запрос = Новый Запрос;
2. У объекта Запрос есть свойство Текст, в которое нужно поместить текст
запроса, написанный на языке запросов. В тексте запроса описывается,
какие данные, из каких таблиц нужно получить и как эти данные представить (листинг 1.2).
Листинг 1.2. Заполнение текста запроса
Запрос.Текст =
"ВЫБРАТЬ
|
Наименование
|ИЗ
|
Справочник.Товары";
3. Далее запрос выполняется с помощью метода Выполнить() объекта
Запрос. Именно в этот момент и происходит чтение данных из базы
данных. Прочитанные данные возвращаются в виде объекта РезультатЗапроса, содержащего выбранные данные из базы данных (листинг 1.3).
Глава 1. Механизм запросов
23
Листинг 1.3. Выполнение запроса
РезультатЗапроса = Запрос.Выполнить();
4. Чтобы обработать данные, содержащиеся в объекте РезультатЗапроса,
из результата запроса получается выборка с помощью метода Выбрать(),
который возвращает новый объект ВыборкаИзРезультатаЗапроса, то есть
коллекцию данных, предназначенную для последовательного обхода ее
элементов (листинг 1.4).
Листинг 1.4. Получение выборки из результата запроса
Выборка = РезультатЗапроса.Выбрать();
5. Далее выборка обходится с помощью цикла Пока Выборка.Следующий()
Цикл, а в теле цикла производятся какие-то действия над данными, полученными с помощью запроса (листинг 1.5).
Листинг 1.5. Обход выборки
Пока ВыборкаЗапроса.Следующий() Цикл
Сообщение = Новый СообщениеПользователю;
Сообщение.Текст = ВыборкаЗапроса.Наименование;
Сообщение.Сообщить();
КонецЦикла;
В результате, если соединить вместе текст листингов 1.1–1.5, мы получим
процедуру встроенного языка, в которой создается и выполняется запрос,
в данном примере выводящий наименование всех товаров из справочника
Товары в окно сообщений (листинг 1.6).
Листинг 1.6. Вывод наименований всех товаров в окно сообщений
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
|
Наименование
|ИЗ
|
Справочник.Товары";
РезультатЗапроса = Запрос.Выполнить();
ВыборкаЗапроса = РезультатЗапроса.Выбрать();
Пока ВыборкаЗапроса.Следующий() Цикл
Сообщение = Новый СообщениеПользователю;
Сообщение.Текст = ВыборкаЗапроса.Наименование;
Сообщение.Сообщить();
КонецЦикла;
Пример этой процедуры находится в демонстрационной конфигурации
«Язык запросов», прилагающейся к книге, в обработке Работа с запросами.
Результат выполнения данной процедуры представлен на рис. 1.12.
24
Язык запросов «1С:Предприятия 8»
Рис. 1.12. Вывод наименований всех товаров в окно сообщений
Чтобы не усложнять восприятие материала, пока мы не будем подробно
останавливаться на выводе и обработке результатов запросов. Сначала мы
начнем изучать сам язык запросов, то есть научимся правильно составлять
текст запросов. Для выполнения запросов и просмотра результатов мы будем
использовать специальную обработку Консоль запросов. Данная обработка
помогает отлаживать и просматривать результаты выполнения запросов
в режиме 1С:Предприятие.
Обработка Консоль запросов добавлена в демонстрационную конфигурацию «Язык запросов», прилагающуюся к книге на компакт-диске. Самая
последняя версия этой обработки опубликована на ИТС (http://its.1c.ru/db/
metod81#content:4500:1).
Синтаксис текста запросов
Язык запросов «1С:Предприятия» основан на стандартном SQL, но при
этом содержит значительное количество расширений, ориентированных
на финансово-экономические задачи, и значительно облегчает разработку
бизнес-приложений.
Из определения следует, что язык запросов – мощный инструмент, предоставляющий разнообразные возможности получения данных. Но мы начнем его
изучение с самых простых примеров, основанных на реальных небольших
задачах, расположенных от простого к сложному. Но сначала немного теории.
Текст запроса состоит из нескольких частей (секций):
■■
■■
■■
■■
■■
описание запроса,
объединение запросов,
упорядочивание результатов,
автоупорядочивание,
описание итогов.
Глава 1. Механизм запросов
25
Обязательной частью запроса является только первая – описание запроса. Все
остальные присутствуют в запросе по необходимости. Назначение каждой
секции запроса будет рассмотрено ниже на конкретных примерах.
Для ознакомления приведем запрос, в котором присутствуют все указанные
секции (рис. 1.13).
Рис. 1.13. Секции запроса
26
Язык запросов «1С:Предприятия 8»
Синтаксически текст запроса состоит из набора секций, имеющих определенное назначение, например, выбрать записи из базы данных, отсортировать
их, подсчитать итоги и т. д. Секции состоят из предложений, которые, в свою
очередь, содержат ключевые слова (например, ВЫБРАТЬ, ИЗ, ГДЕ и т. п.),
обозначающие определенное действие, которое нужно выполнить с базой
данных. Ключевое слово, с которого начинается предложение, обычно дает
название предложению языка запросов.
подробнее
Применение различных синтаксических конструкций языка запросов
подробно описано во встроенной справке Справка ► Содержание справки ►
1С:Предприятие ► Встроенный язык ► Работа с запросами ► Синтаксис текста
запросов.
Одной из существенных особенностей языка запросов «1С:Предприятия»
является то, что все ключевые слова имеют два варианта написания: на
русском и английском языках. Поэтому язык написания запроса – дело
привычки и вкуса, а результат выполнения запроса будет одинаков в обоих
случаях.
В книге мы будем использовать ключевые слова языка запросов на русском
языке, а для тех, кто хочет использовать англоязычный вариант написания
ключевых слов, они подробно описаны во встроенной справке.
подробнее
Встроенная справка: Справка ► Содержание справки ► 1С:Предприятие ►
Встроенный язык ► Работа с запросами ► Синтаксис текста запросов ► Двуязычное представление ключевых слов.
Итак, начнем изучать язык запросов на конкретных примерах, от самых
простых к более сложным. В процессе изучения мы узнаем, как и для чего
используются различные ключевые слова языка запросов.
Для составления примеров будем использовать демонстрационную конфигурацию «Язык запросов», прилагающуюся к книге на компакт-диске.
Глава 1. Механизм запросов
27
Примеры использования языка запросов
для получения данных из одной таблицы
Как получить все данные из таблицы
В нашей демонстрационной конфигурации существует справочник Клиенты.
Предположим, нам нужно получить все данные из таблицы базы данных,
соответствующей этому справочнику. Это можно сделать с помощью простейшего запроса, а результат посмотреть в консоли запросов.
Обработка Консоль запросов добавлена в демонстрационную конфигурацию
«Язык запросов», и ее можно вызвать из группы команд Сервис. Но для того
чтобы полностью использовать все функциональные возможности консоли
запросов, ее нужно запускать в режиме Толстый клиент.
Запустим демонстрационную конфигурацию в этом режиме. Для этого
в диалоге запуска «1С:Предприятия» нужно создать новую информационную
базу с нашей конфигурацией, добавив базу из шаблона, установленного
с диска, и указать в качестве основного режима запуска информационной
базы Толстый клиент (рис. 1.14).
Рис. 1.14. Основной режим запуска демонстрационной конфигурации –
«Толстый клиент»
Вызовем консоль запросов из группы команд Сервис и в среднем окне Текст
запроса введем следующий текст (листинг 1.7).
Листинг 1.7. Вывод всех данных из таблицы
ВЫБРАТЬ
Справочник.Клиенты.*
28
Язык запросов «1С:Предприятия 8»
Нажмем кнопку Выполнить и в нижнем окне Результат запроса увидим
результат выполнения нашего запроса (рис. 1.15).
Рис. 1.15. Вывод всех данных из таблицы
После выполнения запроса в заголовке окна Результат запроса появится
также количество строк в результате запроса и время выполнения запроса.
Теперь рассмотрим подробно текст нашего запроса (см. листинг 1.7).
Текст любого запроса всегда содержит секцию описания запроса, в которой
определяются источники данных для запроса, список полей выборки и т. д.
Все секции запроса приведены на рис. 1.13, стр. 25.
Описание запроса начинается с ключевого слова ВЫБРАТЬ. За ним следует
список имен полей выборки запроса, перечисленных через запятую. Таким
образом, с помощью ключевого слова ВЫБРАТЬ определяются требуемые поля
результата запроса.
Справочник.Клиенты – это имя одной из исходных таблиц запроса, описанных
выше. В языке запросов имена таблиц формируются по принципу: .. Полное имя поля содержит
имя таблицы и имя поля. Например, в строке Справочник.Клиенты.Код
Справочник.Клиенты – это имя таблицы, а Код – это имя поля.
Подробнее
Посмотреть состав таблиц, доступных для запроса, и их описание можно
в синтакс-помощнике в разделе Работа с запросами ► Таблицы запросов.
Глава 1. Механизм запросов
29
В данном случае нам нужны все поля таблицы, поэтому вместо перечисления
имен полей можно использовать звездочку «*».
Обратите внимание на структуру написания запроса. Правила оформления
запросов предписывают все ключевые слова выделять заглавными буквами,
каждое поле из списка выборки начинать с новой строки, со сдвигом относительно слова ВЫБРАТЬ.
Если вы напишите в одну строку – «выбрать справочник.клиенты.*», то
платформа вас поймет и так, и результат запроса не изменится (см. рис. 1.15).
Но так писать – это моветон.
С остальными правилами оформления запросов мы будем знакомиться
по ходу следующих примеров.
Итак, в данном примере мы получили все данные (стандартные и созданные
разработчиком) из объектной таблицы, кроме виртуальных полей. Такие поля
нельзя выбрать с помощью символа «*», их имена нужно указывать явно. Эта
возможность рассматривается в следующем примере.
Как получить только определенные поля
для всех записей из таблицы
В реальных задачах обычно требуется получить не все, а только некоторые
конкретные поля из таблицы. В данном примере для всех записей справочника Клиенты получим только поля Наименование, Телефон и Представление.
Это можно сделать с помощью следующего запроса (листинг 1.8).
Листинг 1.8.Вывод определенных полей для всех записей из таблицы
ВЫБРАТЬ
Справочник.Клиенты.Наименование,
Справочник.Клиенты.Телефон,
Справочник.Клиенты.Представление
В данном запросе после ключевого слова ВЫБРАТЬ перечислены полные
имена требуемых полей результата запроса.
Результат выполнения запроса будет выглядеть следующим образом
(рис. 1.16).
Рис. 1.16. Вывод определенных полей для всех записей из таблицы
30
Язык запросов «1С:Предприятия 8»
Итак, в данном примере мы получили из таблицы справочника Клиенты два
реальных поля (Наименование и Телефон) и одно виртуальное (Представление).
Поле Представление – это текстовое представление объекта. При получении
данного поля из базы данных запрос получает несколько полей, а при
получении значения поля из результата запроса преобразовывает полученные значения в строку.
Мы видим, что в данном примере поле Представление совпадает с наименованием. Так произошло потому, что в свойствах справочника Основное
представление установлено в значение В виде наименования (рис. 1.17).
Рис. 1.17. Основное представление элементов справочника
Если мы установим свойство Основное представление в значение В виде
кода, то результат выполнения запроса будет другим (рис. 1.18).
Рис. 1.18. Вывод отдельных полей и всех записей из таблицы
подробнее
О поле Представление рассказано в разделе «Как получить текстовое представление ссылочного поля», стр. 38.
Глава 1. Механизм запросов
31
Итак, мы получили требуемые поля из таблицы с помощью ключевого слова
ВЫБРАТЬ. Однако неудобно то, что для каждого поля нужно писать полный
путь к нему Справочник.Клиенты. Имя таблицы справочника, служащей
источником для запроса, можно написать один раз после ключевого слова ИЗ
(листинг 1.9).
Листинг 1.9. Вывод определенных полей для всех записей из таблицы
ВЫБРАТЬ
Наименование,
Телефон,
Представление
ИЗ
Справочник.Клиенты
Результат запроса будет аналогичен выполнению предыдущего запроса (см.
рис. 1.16), то есть результат выполнения двух следующих запросов будет
одинаковым (листинг 1.10).
Листинг 1.10. Вывод определенных полей для всех записей из таблицы
// Первый вариант
ВЫБРАТЬ
Справочник.Клиенты.Наименование,
Справочник.Клиенты.Телефон,
Справочник.Клиенты.Представление
// Второй вариант
ВЫБРАТЬ
Наименование,
Телефон,
Представление
ИЗ
Справочник.Клиенты
Также с помощью ключевого слова ИЗ можно переписать текст запроса,
используемого нами в первом примере, то есть результат выполнения двух
следующих запросов будет одинаковым (листинг 1.11).
Листинг 1.11. Вывод всех полей и всех записей из таблицы
// Первый вариант
ВЫБРАТЬ
Справочник.Клиенты.*
// Второй вариант
ВЫБРАТЬ
*
ИЗ
Справочник.Клиенты
Таким образом, после ключевого слова ИЗ через запятую перечисляются
таблицы (реальные или виртуальные), являющиеся источниками для запроса.
32
Язык запросов «1С:Предприятия 8»
В реальных задачах запросы могут быть сложными, и источников в них
может быть несколько. Поэтому, следуя правилам оформления запросов,
желательно давать псевдонимы источникам и использовать их затем в других
частях запроса. В некоторых случаях это является обязательным, например,
когда из разных таблиц выбираются поля с одинаковыми именами.
Псевдонимы задаются с помощью ключевого слова КАК, после которого
следует имя псевдонима. Имя псевдонима может писаться сразу после имени
таблицы (например, Справочник.Клиенты Клиенты), но наличие ключевого
слова КАК повышает наглядность и удобочитаемость текста запроса.
Таким образом, запрос, представленный в листинге 1.9, может быть изменен
следующим образом (листинг 1.12).
Листинг 1.12. Вывод определенных полей для всех записей из таблицы
// Первый вариант без псевдонимов
ВЫБРАТЬ
Наименование,
Телефон,
Представление
ИЗ
Справочник.Клиенты
// Рекомендуемый вариант с псевдонимом таблицы-источника
ВЫБРАТЬ
Клиенты.Наименование,
Клиенты.Телефон,
Клиенты.Представление
ИЗ
Справочник.Клиенты КАК Клиенты
Также псевдонимы рекомендуется присваивать полям выборки. Дело в том,
что на имена полей выборки могут быть завязаны алгоритмы обработки
результатов запроса.
Если не присваивать псевдонимы полям выборки, то поля выборки будут
иметь «стандартные» имена, соответствующие их именам в таблице
запросов. В этом случае при выборе другого поля в запросе у поля выборки
будет другое имя. А если использовать псевдонимы полей выборки, то имя
поля выборки в результате запроса можно оставить без изменений, только
оно уже будет указывать на другое поле в таблице запроса. Таким образом,
при изменении полей выборки запроса алгоритм обработки результатов будет
работать так же, как и раньше.
Для примера сравним две процедуры, в которых выполняется запрос и обрабатываются его результаты (листинги 1.13, 1.14).
Глава 1. Механизм запросов
33
Листинг 1.13. Вывод наименований всех товаров
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
|
Наименование КАК Название
|ИЗ
|
Справочник.Товары";
РезультатЗапроса = Запрос.Выполнить();
ВыборкаЗапроса = РезультатЗапроса.Выбрать();
Пока ВыборкаЗапроса.Следующий() Цикл
Сообщение = Новый СообщениеПользователю;
Сообщение.Текст = ВыборкаЗапроса.Название;
Сообщение.Сообщить();
КонецЦикла;
Листинг 1.14. Вывод англоязычных наименований всех товаров
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
|
АнглоязычноеНаименование КАК Название
|ИЗ
|
Справочник.Товары";
РезультатЗапроса = Запрос.Выполнить();
ВыборкаЗапроса = РезультатЗапроса.Выбрать();
Пока ВыборкаЗапроса.Следующий() Цикл
Сообщение = Новый СообщениеПользователю;
Сообщение.Текст = ВыборкаЗапроса.Название;
Сообщение.Сообщить();
КонецЦикла;
То есть в алгоритме обработки результатов запроса ничего не меняется,
меняется только строка (или строки) в тексте запроса. И в результате в окно
сообщений выводятся разные поля таблицы.
Как расположить полученные записи в нужном порядке
Следующий пример, который мы рассмотрим, показывает простую, но очень
важную возможность языка запросов – возможность упорядочивания записей
в результате запроса.
Важно понимать, что упорядочивание результата запроса – это обязательное правило хорошего тона при написании работоспособных запросов
в «1С:Предприятии». Если не использовать упорядочивание, то порядок
записей в результате запроса будет не определен. Предугадать его заранее не
может ни пользователь, ни даже разработчик.
Например, один и тот же запрос, выполненный на разных СУБД, может дать
разный порядок записей. С точки зрения пользователя, это может показаться
34
Язык запросов «1С:Предприятия 8»
странным, а с точки зрения разработчика, вообще может привести к ошибкам
при обработке результатов запроса, если алгоритм обработки рассчитан
на определенный порядок записей.
Поэтому в запросе очень важно всегда явно указывать, как должны быть
упорядочены записи. Даже тогда, когда, на первый взгляд, порядок записей
не влияет на обработку результата. Потому что заранее неизвестно, как и кем
будет дорабатываться дальше прикладное решение.
Порядок расположения записей в результате запроса определяется в секции
Упорядочивание результатов текста запроса. Все секции запроса приведены
на рис. 1.13, стр. 25.
Рассмотрим реальную задачу. Часто требуется выводить записи из регистра
сведений Цены не в случайном порядке, а в порядке возрастания даты изменения цен товаров.
Для этого в языке запросов предназначено ключевое слово УПОРЯДОЧИТЬ
ПО, после которого следует список полей упорядочивания и тип упорядочивания (по возрастанию, по убыванию, по иерархии) для каждого поля.
Например, чтобы вывести записи регистра сведений Цены в порядке возрастания поля Период, нужно выполнить следующий запрос (листинг 1.15).
Листинг 1.15. Упорядочивание записей регистра сведений
по возрастанию поля «Период»
ВЫБРАТЬ
Цены.Период КАК Период,
Цены.Товар КАК Товар,
Цены.Цена КАК Цена
ИЗ
РегистрСведений.Цены КАК Цены
УПОРЯДОЧИТЬ ПО
Период
Обратите внимание, предложение УПОРЯДОЧИТЬ ПО относится к секции
упорядочивания результатов запроса, а предложения ВЫБРАТЬ и ИЗ – к секции
описания запроса. В правилах оформления запросов принято отделять
одну секцию запроса от другой, поэтому между секциями находится пустая
строка.
Результат выполнения запроса будет следующим (рис. 1.19).
Мы видим, что записи таблицы в результате запроса расположены по
возрастанию поля, указанного в предложении УПОРЯДОЧИТЬ ПО, хотя тип
упорядочивания не задан. Дело в том, что упорядочивание записей результата
запроса по возрастанию выполняется по умолчанию, хотя можно указать явно
Глава 1. Механизм запросов
35
тип упорядочивания. Для этого после имени поля нужно указать ключевое
слово ВОЗР (листинг 1.16).
Рис. 1.19. Вывод записей из таблицы в определенном порядке
Листинг 1.16. Упорядочивание записей регистра сведений
по возрастанию поля «Период»
ВЫБРАТЬ
Цены.Период КАК Период,
Цены.Товар КАК Товар,
Цены.Цена КАК Цена
ИЗ
РегистрСведений.Цены КАК Цены
УПОРЯДОЧИТЬ ПО
Период ВОЗР
При этом поле, по которому производится упорядочивание, необязательно
должно входить в список выборки запроса (листинг 1.17).
Листинг 1.17. Упорядочивание записей регистра сведений
по возрастанию поля «Период»
ВЫБРАТЬ
Цены.Товар КАК Товар,
Цены.Цена КАК Цена
ИЗ
РегистрСведений.Цены КАК Цены
УПОРЯДОЧИТЬ ПО
Цены.Период ВОЗР
При выполнении данного запроса записи регистра сведений Цены будут
расположены в порядке возрастания поля Период, но само это поле в результате запроса отражено не будет.
Чтобы расположить записи результата запроса по убыванию какого-либо поля,
нужно в предложении упорядочивания указать после имени поля ключевое
слово УБЫВ. Можно также упорядочивать иерархические данные по иерархии
(этот пример будет рассмотрен позже на стр. 47).
36
Язык запросов «1С:Предприятия 8»
В предложении УПОРЯДОЧИТЬ ПО могут участвовать несколько полей,
каждое из этих полей может использовать разный способ упорядочивания
(листинг 1.18). В этом случае записи результата запроса будут упорядочены
сначала по первому полю, затем – по второму полю (в случае, если существует несколько записей с одинаковым значением первого поля) и т. д.
Листинг 1.18. Упорядочивание записей регистра сведений по нескольким полям
ВЫБРАТЬ
Цены.Период КАК Период,
Цены.Товар КАК Товар,
Цены.Цена КАК Цена
ИЗ
РегистрСведений.Цены КАК Цены
УПОРЯДОЧИТЬ ПО
Период УБЫВ,
Цена
Обратите внимание, что в тексте запроса принято не только поля в списке
выборки, но и поля в списке упорядочивания располагать на разных строках
со смещением относительно ключевого слова УПОРЯДОЧИТЬ ПО.
В результате мы видим, что записи регистра сведений расположены в порядке
убывания поля Период, а внутри одного периода – в порядке возрастания
поля Цена (рис. 1.20).
Рис. 1.20. Вывод записей из таблицы в определенном порядке
Как упорядочить записи таблицы по ссылочному полю
В предыдущем примере мы рассматривали, как упорядочить данные результата запроса по полям примитивных типов – Дата, Число и т. п. Но как упорядочить данные по ссылочному полю?
Ведь ссылка на самом деле представляет собой некоторый произвольный
уникальный идентификатор, никак не связанный с прикладной сущностью
данных (набором символов), которые содержатся в этом поле. У элементов
справочников – одна прикладная сущность, которая может выражаться кодом
или наименованием. У документов – совсем другая прикладная сущность,
которая выражается датой и номером документа и т. д.
Глава 1. Механизм запросов
37
Чтобы расположить объекты базы данных (записи ссылочных таблиц)
в порядке, соответствующем их прикладной сущности, используется режим
автоматического упорядочивания записей результата запроса.
В тексте запроса этот режим задается в секции Автоупорядочивание. Все
секции запроса приведены на рис. 1.13, стр. 25.
С помощью автоупорядочивания можно вывести записи таблицы в наиболее
естественном (ожидаемом пользователем) порядке. Для этого нужно упорядочить записи таблицы непосредственно по ссылочному полю, а затем использовать конструкцию АВТОУПОРЯДОЧИВАНИЕ (листинг 1.19).
Листинг 1.19. Автоматическое упорядочивание записей таблицы
ВЫБРАТЬ
ЗаказТовара.Дата,
ЗаказТовара.Номер,
ЗаказТовара.Клиент,
ЗаказТовара.СуммаЗаказа
ИЗ
Документ.ЗаказТовара КАК ЗаказТовара
УПОРЯДОЧИТЬ ПО
ЗаказТовара.Ссылка
АВТОУПОРЯДОЧИВАНИЕ
Мы видим, что при автоупорядочивании списка документов ЗаказТовара
записи результата запроса расположены в порядке возрастания поля Дата,
а в случае, если даты у документов одинаковые, записи упорядочиваются
в порядке возрастания поля Номер (рис. 1.21).
Рис. 1.21. Вывод записей из таблицы в автоматическом порядке
Так произошло потому, что при выполнении данного запроса для каждого
ссылочного поля были получены реальные поля, по которым его необходимо
упорядочить (для документа – это дата и номер, для справочника – основное
представление), и произведено упорядочивание по ним.
Таким образом, такого же результата можно было добиться, явно указав поля
Дата и Номер при упорядочивании таблицы документа (листинг 1.20).
38
Язык запросов «1С:Предприятия 8»
Листинг 1.20. Упорядочивание списка документов по полям «Дата» и «Номер»
ВЫБРАТЬ
ЗаказТовара.Дата,
ЗаказТовара.Номер,
ЗаказТовара.Клиент,
ЗаказТовара.СуммаЗаказа
ИЗ
Документ.ЗаказТовара КАК ЗаказТовара
УПОРЯДОЧИТЬ ПО
ЗаказТовара.Дата,
ЗаказТовара.Номер
Но в общем случае, чтобы расположить записи результата запроса в наиболее
ожидаемом (естественном) порядке, рекомендуется более универсальный
первый вариант – упорядочить записи таблицы непосредственно по ссылочному полю, а затем использовать конструкцию АВТОУПОРЯДОЧИВАНИЕ.
В остальных случаях использовать автоупорядочивание записей результата
запроса не рекомендуется.
Как получить текстовое представление ссылочного поля
Часто в отчетах (а также в тех местах конфигурации, где требуется вывести
представление ссылочного поля в виде сообщения) бывает нужно отобразить
значения полей ссылочного типа, например, вывести значение поля Клиент
(являющееся ссылкой на справочник Клиенты) или поля Товар (являющееся
ссылкой на справочник Товары).
При этом нужно понимать, что при выводе значения ссылочного поля для
получения его представления будет выполняться дополнительный запрос к
той таблице, на которую ссылается это ссылочное поле. В результате процесс
вывода будет замедляться. Чтобы этого избежать, следует в запросе сразу
получать текстовое представление ссылочного поля и затем уже его, а не саму
ссылку, выводить в отчет или сообщение.
Например, рассмотрим простой запрос, выводящий записи регистра накопления ОстаткиТоваров (листинг 1.21).
Листинг 1.21. Получение ссылочных полей в запросе
ВЫБРАТЬ
ОстаткиТоваров.Период,
ОстаткиТоваров.Регистратор КАК Регистратор,
ОстаткиТоваров.Товар КАК Товар,
ОстаткиТоваров.Количество,
ОстаткиТоваров.Сумма
ИЗ
РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров
Глава 1. Механизм запросов
39
Поля выборки запроса включают два ссылочных поля – Регистратор и Товар.
Заменим значения этих полей их текстовыми представлениями. Это можно
сделать с помощью полей Представление и функции ПРЕДСТАВЛЕНИЕ().
Рассмотрим их подробнее.
Каждая объектная таблица в информационной базе имеет виртуальное
поле – Представление. Это текстовое представление объекта. Поскольку это
поле – виртуальное, то при получении данного поля из базы данных запрос
получает несколько полей, которые соответствуют прикладной сущности
объекта, а при получении значения поля из результата запроса преобразовывает полученные значения в строку.
Для большинства объектов конфигурации (справочников, планов видов
характеристик, планов счетов и т. п.) представление ссылок задается разработчиком в свойстве Основное представление. А для документов и бизнеспроцессов система предоставляет единственное неизменяемое представление
ссылочных значений в виде совокупности синонима документа или бизнеспроцесса, его номера и даты. Например: «Приходная накладная 000000003
от 03.11.2012 15:35:27».
В версии платформы 8.3 появилась возможность во встроенном языке
задавать произвольные представления для ссылочных полей. Для этого
у менеджеров справочников, документов и т. п. добавлены два обработчика:
ОбработкаПолученияПолейПредставления и ОбработкаПолученияПредставления, в которых можно задать собственный алгоритм, формирующий
представление.
При получении запросом поля Представление из базы данных будут получаться поля, которые указаны в обработчике ОбработкаПолученияПолейПредставления, а при получении значения поля из результата запроса они
будут объединяться в строку в обработчике ОбработкаПолученияПредставления.
Функция ПРЕДСТАВЛЕНИЕ() предназначена для получения текстового представления любого значения, которое может быть получено при помощи
языка запросов. В качестве параметра в функцию могут передаваться как
ссылочные, так и примитивных типы данных.
Для ссылочных типов результат функции полностью аналогичен получению
поля Представление от ссылки, переданной в качестве параметра функции.
Например, ПРЕДСТАВЛЕНИЕ(Товар) в данном случае аналогично Товар.Представление, но первый вариант предпочтительнее.
Поэтому предыдущий запрос можно переписать в следующем виде (листинг
1.22).
40
Язык запросов «1С:Предприятия 8»
Листинг 1.22. Получение ссылочных полей в запросе
// Получение самого ссылочного поля
ВЫБРАТЬ
ОстаткиТоваров.Период,
ОстаткиТоваров.Регистратор КАК Регистратор,
ОстаткиТоваров.Товар КАК Товар,
ОстаткиТоваров.Количество,
ОстаткиТоваров.Сумма
ИЗ
РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров
// Получение текстового представления ссылочного поля
ВЫБРАТЬ
ОстаткиТоваров.Период,
ПРЕДСТАВЛЕНИЕ(ОстаткиТоваров.Регистратор) КАК Регистратор,
ОстаткиТоваров.Товар.Представление КАК Товар,
ОстаткиТоваров.Количество,
ОстаткиТоваров.Сумма
ИЗ
РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров
В первом варианте запроса из базы данных будут получены сами ссылочные
значения, а во втором варианте – их текстовые представления. В обоих
случаях результат выполнения запросов будет одинаковым, но в случае, если
нужно только показать результат запроса пользователю, рекомендуется
использовать второй вариант. Если ссылочные значения нужны не для показа
пользователю, а для того, чтобы, например, поместить их в ячейку расшифровки отчета, то следует получать в одном запросе как сами ссылочные
значения, так и их текстовые представления.
Практически в каждом примере мы выводим в консоли запросов значения
ссылочных полей. В них мы не используем представления этих полей потому,
что консоль запросов сама занимается выводом данных, а также для упрощения текста запросов.
Особенностью функции ПРЕДСТАВЛЕНИЕ() является то, что ее результат не
может быть использован в выражении языка запросов, например, следующие
два запроса ошибочны (листинги 1.23, 1.24).
Функцию ПРЕДСТАВЛЕНИЕ() нельзя использовать в условиях сравнения
в предложении ГДЕ (листинг 1.23).
Листинг 1.23. Ошибочный запрос
ВЫБРАТЬ
ЗаказТовара.Номер,
ЗаказТовара.Клиент,
ЗаказТовара.СуммаЗаказа,
ЗаказТовара.Состав
ИЗ
Документ.ЗаказТовара КАК ЗаказТовара
ГДЕ
ПРЕДСТАВЛЕНИЕ(ЗаказТовара.Состав.Товар) = "Туфли".
Глава 1. Механизм запросов
41
Функцию ПРЕДСТАВЛЕНИЕ() нельзя использовать в различных выражениях в списке полей выборки, например, нельзя складывать в одну строку
текстовые представления ссылочных полей (листинг 1.24).
Листинг 1.24. Ошибочный запрос
ВЫБРАТЬ
ПРЕДСТАВЛЕНИЕ(ЗаказТовара.Ссылка) + ПРЕДСТАВЛЕНИЕ(ЗаказТовара.Клиент),
ЗаказТовара.СуммаЗаказа,
ИЗ
Документ.ЗаказТовара КАК ЗаказТовара
Аналогично с полем Представление также нельзя выполнять никакие
операции.
Как получить только первые несколько записей
с наибольшими значениями некоторого поля
Часто требуется получить из большой таблицы только определенное количество записей с наибольшим или наименьшим значением какого-то поля.
Например, нам нужно получить три заказа клиентов с наибольшей суммой
заказа.
В нашей демонстрационной конфигурации существует список документов
ЗаказТовара, который показывает сумму заказа по клиентам (см. рис. 1.22).
Чтобы выбрать из таблицы три заказа с наибольшей суммой, зададим упорядочивание по убыванию поля СуммаЗаказа и затем воспользуемся конструкцией ВЫБРАТЬ ПЕРВЫЕ 3 (листинг 1.25).
Листинг 1.25. Вывод трех документов «ЗаказТовара» с наибольшей суммой заказа
ВЫБРАТЬ ПЕРВЫЕ 3
ЗаказТовара.Номер,
ЗаказТовара.Клиент,
ЗаказТовара.СуммаЗаказа КАК СуммаЗаказа
ИЗ
Документ.ЗаказТовара КАК ЗаказТовара
УПОРЯДОЧИТЬ ПО
СуммаЗаказа УБЫВ
Конструкция ВЫБРАТЬ ПЕРВЫЕ позволяет задать предельное
количество строк в результате запроса.
В результате выполнения запроса на рис. 1.22 в нижней таблице мы видим
три наибольших заказа в порядке убывания их суммы, а в верхней таблице
для сравнения приведен список всех заказов клиентов.
42
Язык запросов «1С:Предприятия 8»
Рис. 1.22. Вывод трех документов «ЗаказТовара» с наибольшей суммой заказа
Чтобы выбрать три заказа с наименьшей суммой заказа, нужно в этом же
примере поменять порядок сортировки по полю СуммаЗаказа.
Как получить записи, в которых определенные поля
не содержат одинаковых значений
Часто записи в таблицах имеют одинаковые значения какого-то поля (полей),
а нужно получить только записи с неповторяющимися значениями этого
поля.
Например, в списке документов ЗаказТовара повторяются значения поля
Клиент (см. ниже рис. 1.23), то есть существуют несколько заказов для одного
и того же клиента, а нас интересует, какими клиентами были сделаны заказы
вообще. Для этого следует использовать конструкцию ВЫБРАТЬ РАЗЛИЧНЫЕ
(листинг 1.26).
Листинг 1.26. Вывод различных клиентов из документов «ЗаказТовара»
ВЫБРАТЬ РАЗЛИЧНЫЕ
ЗаказТовара.Клиент
ИЗ
Документ.ЗаказТовара КАК ЗаказТовара
В результате выполнения запроса на рис. 1.23 справа мы видим трех
различных клиентов, сделавших заказ товара, а слева для сравнения приведен
список всех заказов клиентов.
Рис. 1.23. Вывод различных клиентов из документов «ЗаказТовара»
Глава 1. Механизм запросов
43
Если в списке выборки указано несколько полей, то при использовании
конструкции ВЫБРАТЬ РАЗЛИЧНЫЕ в результат запроса отбираются записи,
содержащие неповторяющиеся комбинации значений сразу по нескольким
полям (листинг 1.27).
Листинг 1.27. Вывод различных клиентов за различные даты
из документов «ЗаказТовара»
ВЫБРАТЬ РАЗЛИЧНЫЕ
ЗаказТовара.Дата,
ЗаказТовара.Клиент
ИЗ
Документ.ЗаказТовара КАК ЗаказТовара
В результате выполнения запроса на рис. 1.24 в нижней таблице мы видим
список различных клиентов, сделавших заказ товара за различные даты,
а в верхней таблице для сравнения приведен список всех заказов клиентов.
Рис. 1.24. Вывод различных клиентов за различные даты из документов «ЗаказТовара»
На рис. 1.24 в нижней таблице мы видим 6 записей, а всего в списке заказов
клиентов содержится 7 документов. Так произошло потому, что в верхней
таблице у одного из клиентов сделано два заказа за одну дату.
Необходимо помнить, что если в запросе указано ключевое слово РАЗЛИЧНЫЕ
и в предложении УПОРЯДОЧИТЬ ПО указано поле, отсутствующее в списке
выборки, то при выполнении такого запроса будет выдана ошибка. Например,
следующий запрос вызовет ошибку (листинг 1.28).
Листинг 1.28. Ошибочный запрос
ВЫБРАТЬ РАЗЛИЧНЫЕ
ЗаказТовара.Дата,
ЗаказТовара.Клиент
ИЗ
Документ.ЗаказТовара КАК ЗаказТовара
УПОРЯДОЧИТЬ ПО
ЗаказТовара.Номер
44
Язык запросов «1С:Предприятия 8»
Как получить общее количество записей в таблице
и количество записей с различным значением некоторого поля
Этот пример является продолжением предыдущего примера, но в данном
случае нам нужно получить не сами записи, а количество записей в результате запроса. Это делается с помощью агрегатной функции КОЛИЧЕСТВО().
Агрегатные функции предназначены для обобщения значений параметра,
переданного в функцию. Функция КОЛИЧЕСТВО() подсчитывает количество
значений поля, указанного в параметре.
При выполнении следующего запроса выводится общее количество записей
в таблице заказов товара клиентами и количество записей, содержащих
различные значения в поле Клиент (листинг 1.29).
Листинг 1.29. Вывод общего количества записей в таблице
и количества различных записей по полю «Клиент»
ВЫБРАТЬ
КОЛИЧЕСТВО(*) КАК Всего,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ЗаказТовара.Клиент) КАК РазныеКлиенты
ИЗ
Документ.ЗаказТовара КАК ЗаказТовара
В данном запросе во второй строке запроса подсчитывается общее количество записей в результате запроса (КОЛИЧЕСТВО(*)). В следующей строке
подсчитывается количество различных значений, отличных от NULL, для поля
Клиент.
В этом примере показывается, что в списке полей выборки, после ключевого слова ВЫБРАТЬ, можно использовать не только поля таблиц запроса,
но и различные выражения с их использованием, то есть агрегатные функции,
функции языка запросов и т. п.
подробнее
Об использовании выражений в списке полей выборки будет рассказано
в разделе «Примеры использования выражений в списке полей выборки
запроса», стр. 82.
О том, что такое значение NULL, будет рассказано на стр. 60, 96.
Результат выполнения запроса выводит соответственно количество записей
при выполнении запросов из предыдущего примера (рис. 1.25).
Если требуется узнать количество всевозможных значений, отличных
от NULL, для некоторого поля в таблице, то можно просто написать –
КОЛИЧЕСТВО().
Глава 1. Механизм запросов
45
Рис. 1.25. Вывод общего количества записей в таблице
и количества различных записей по полю «Клиент»
Как получить записи из таблицы,
отобранные по некоторому условию
Теперь рассмотрим распространенную задачу, когда из большой таблицы
нужно получить только некоторые записи, удовлетворяющие заданному
условию.
В нашей демонстрационной конфигурации существует документ ПриходнаяНакладная. Полностью список накладных представлен далее, на рис. 1.26,
в верхней таблице. Предположим, нам нужно отобрать из списка документов
только накладные за текущий месяц.
Условие отбора данных из таблицы задается после ключевого слова ГДЕ.
В результат запроса будут включены только те записи, которые удовлетворяют условию отбора.
Для того чтобы отобрать документы за текущий месяц, в условии отбора
сравним поле документа Дата с литералом даты ДАТАВРЕМЯ(2012, 11, 01),
листинг 1.30.
Листинг 1.30. Вывод документов «ПриходнаяНакладная» за текущий месяц
ВЫБРАТЬ
Накладная.Дата КАК Дата,
Накладная.Номер КАК Номер,
Накладная.Поставщик
ИЗ
Документ.ПриходнаяНакладная КАК Накладная
ГДЕ
Дата >= ДАТАВРЕМЯ(2012, 11, 01)
УПОРЯДОЧИТЬ ПО
Дата, Номер
46
Язык запросов «1С:Предприятия 8»
Литералы – это строковые константы, которые могут использоваться в тексте
запроса. Литералы могут быть различных примитивных типов – Булево
(ИСТИНА, ЛОЖЬ), Число (например, 222.77), Строка (например, «Мария»).
Использование этих литералов мы рассмотрим в следующих примерах.
В данном запросе в условии отбора мы используем литерал типа Дата,
значения которого задаются с помощью ключевого слова ДАТАВРЕМЯ, после
которого в скобках последовательно указываются год, месяц, день, час,
минута, секунда. Последние три значения указывать необязательно.
В результате выполнения запроса на рис. 1.26 в нижней таблице мы видим
список накладных за текущий месяц, а в верхней таблице для сравнения
приведен список всех приходных накладных.
Рис. 1.26. Отбор документов «ПриходнаяНакладная» по условию
В предложении ГДЕ можно использовать как имена полей, так и их псевдонимы. При этом совершенно необязательно, чтобы поле, фигурирующее
в предложении ГДЕ, входило в список выборки (листинг 1.31).
Листинг 1.31. Вывод документов «ПриходнаяНакладная» за текущий месяц
ВЫБРАТЬ
Накладная.Номер,
Накладная.Поставщик
ИЗ
Документ.ПриходнаяНакладная КАК Накладная
ГДЕ
Накладная.Дата >= ДАТАВРЕМЯ(2012, 11, 01)
Условие отбора может определяться и как простое логическое выражение,
и как более сложное, в котором простые логические выражения соединяются
между собой логическими операторами И, ИЛИ, НЕ (листинг 1.32).
Листинг 1.32. Вывод документов «ПриходнаяНакладная»
за прошлый месяц по сложному условию
ВЫБРАТЬ
Накладная.Дата КАК Дата,
Накладная.Номер,
Накладная.Поставщик
ИЗ
Документ.ПриходнаяНакладная КАК Накладная
ГДЕ
Дата >= ДАТАВРЕМЯ(2012, 10, 01)
И Дата < ДАТАВРЕМЯ(2012, 11, 01)
Глава 1. Механизм запросов
47
В условиях сначала вычисляются простые логические выражения, затем
операции НЕ, затем операции И, в последнюю очередь – операции ИЛИ. Для
того чтобы обеспечить другой порядок вычислений, можно использовать
круглые скобки.
Подробнее
Документация «1С:Предприятие 8.3. Руководство разработчика», раздел
8.2.17 «Условия в языке запросов».
В данном случае (листинг 1.32) мы получили накладные за прошлый месяц
с помощью сложного условия, где простые логические выражения соединены логическим союзом И. Но это сделано просто для примера. Такой же
результат можно получить, если в условии отбора использовать оператор
МЕЖДУ, который проверяет результат вхождения значения в диапазон, вместе
с границами диапазона (листинг 1.33).
Листинг 1.33. Вывод документов «ПриходнаяНакладная» за прошлый месяц
// Первый вариант
ВЫБРАТЬ
Накладная.Дата КАК Дата,
Накладная.Номер,
Накладная.Поставщик
ИЗ
Документ.ПриходнаяНакладная КАК Накладная
ГДЕ
Дата >= ДАТАВРЕМЯ(2012, 10, 01)
И Дата < ДАТАВРЕМЯ(2012, 11, 01)
// Второй вариант
ВЫБРАТЬ
Накладная.Дата КАК Дата,
Накладная.Номер,
Накладная.Поставщик
ИЗ
Документ.ПриходнаяНакладная КАК Накладная
ГДЕ
Дата МЕЖДУ ДАТАВРЕМЯ(2012, 10, 01) И ДАТАВРЕМЯ(2012, 10, 31)
Как получить записи таблицы, содержащие строки,
соответствующие заданному шаблону
Этот пример является продолжением предыдущего примера, но в данном
случае рассмотрим, как с помощью условия отбора в предложении ГДЕ
получить записи таблицы, содержащие строки, соответствующие заданному
шаблону.
В нашей демонстрационной конфигурации существует справочник Клиенты.
Полностью список всех элементов справочника представлен далее,
48
Язык запросов «1С:Предприятия 8»
на рис. 1.27, в верхней таблице. Предположим, нам нужно отобрать из справочника тех клиентов, в наименовании которых встречается подстрока
«Иван».
Для этого в тексте запроса после ключевого слова ГДЕ зададим условие
отбора, в котором сравним значение поля Наименование со строкой шаблона
«%Иван%» при помощи оператора ПОДОБНО (листинг 1.34).
Листинг 1.34. Отбор записей из справочника «Клиенты» по условию
ВЫБРАТЬ
Клиенты.Наименование КАК Наименование,
Клиенты.Адрес,
Клиенты.Телефон
ИЗ
Справочник.Клиенты КАК Клиенты
ГДЕ
Наименование ПОДОБНО "%Иван%"
Результатом выполнения оператора ПОДОБНО будет Истина или Ложь в зависимости от того, удовлетворяет шаблону значение выражения (в данном
случае поля Наименование) или нет.
В условии отбора мы используем литерал строкового типа ("%Иван%"),
который представляет собой набор символов, заключенных в кавычки.
Символ «%» (процент) заменяет в шаблоне строки любую последовательность символов.
В результате выполнения запроса на рис. 1.27 в нижней таблице мы видим
только тех клиентов, часть наименования которых совпадает с подстрокой
«Иван», а в верхней таблице для сравнения приведен список всех элементов
справочника Клиенты.
Рис. 1.27. Отбор записей из справочника «Клиенты» по условию
Теперь немного изменим условие, чтобы узнать, телефоны каких клиентов
не соответствуют шаблону «_-___-___-__-__». В заданном шаблоне символ
«-» (тире) означает наличие символа тире в строке. А символ «_» (подчеркивание) – это служебный символ, используемый в шаблонах. Он заменяет
в шаблоне строки один произвольный символ. Например, строка с номером
телефона, соответствующего шаблону, может иметь вид «8-916-222-33-55».
Глава 1. Механизм запросов
49
подробнее
Встроенная справка: Справка ► Содержание справки ► 1С:Предприятие ►
Встроенный язык ► Работа с запросами ► Синтаксис текста запросов ► Использование выражений в языке запросов ► Логические выражения ► Подобно.
Поставленная задача может быть решена с помощью следующего запроса
(листинг 1.35).
Листинг 1.35. Отбор записей из справочника «Клиенты» по условию
ВЫБРАТЬ
Клиенты.Наименование,
Клиенты.Адрес,
Клиенты.Телефон КАК Телефон
ИЗ
Справочник.Клиенты КАК Клиенты
ГДЕ
НЕ Телефон ПОДОБНО "_-___-___-__-__"
В результате выполнения запроса на рис. 1.28 в нижней таблице мы
видим только тех клиентов, телефоны которых не соответствуют шаблону
«_-___-___-__-__», а в верхней таблице для сравнения приведен список всех
элементов справочника Клиенты.
Рис. 1.28. Отбор записей из справочника «Клиенты» по условию
Таким образом, с помощью оператора НЕ … ПОДОБНО можно найти те строки,
которые не соответствуют некоторому шаблону, например, узнать, какие телефоны были введены в базу данных неправильно.
Как задать произвольное значение отбора записей из таблицы
Этот пример является продолжением предыдущего примера, но в данном
случае мы хотим показать, как задавать произвольные значения отбора
записей из таблицы.
Например, пользователь в режиме 1С:Предприятие может задать значение
условия отбора при формировании отчета, при просмотре списка справочника и т. д. Запросу, формирующему отчет или список, заранее не известно
значение отбора, которое введет пользователь. То есть в данном случае
требуется передавать в запрос произвольное значение отбора, а не задавать
его жестко в тексте запроса.
50
Язык запросов «1С:Предприятия 8»
Для этого в условии отбора, в предложении ГДЕ языка запросов, можно
использовать параметры. В тексте запроса параметры обозначаются символом
«&», после которого следует имя параметра (например, параметр &Клиент).
Сначала для простоты научимся использовать параметры запроса в консоли
запросов. Откроем консоль и в окно Текст запроса введем текст запроса из
примера, в котором мы отбирали из справочника тех клиентов, в наименовании которых встречается определенная подстрока. При помощи оператора
ПОДОБНО мы сравнивали значение поля Наименование со строкой шаблона
«%Иван%» (листинг 1.36).
Листинг 1.36. Отбор записей из справочника «Клиенты» по условию
ВЫБРАТЬ
Клиенты.Наименование КАК Наименование,
Клиенты.Адрес,
Клиенты.Телефон
ИЗ
Справочник.Клиенты КАК Клиенты
ГДЕ
Наименование ПОДОБНО "%Иван%"
Изменим в данном запросе условие отбора следующим образом (листинг
1.37).
Листинг 1.37. Отбор записей из справочника «Клиенты»
по параметризированному условию
ВЫБРАТЬ
Клиенты.Наименование КАК Наименование,
Клиенты.Адрес,
Клиенты.Телефон
ИЗ
Справочник.Клиенты КАК Клиенты
ГДЕ
Наименование ПОДОБНО "%" + &ЧастьНаименования + "%"
В этом запросе мы используем параметр ЧастьНаименования. Символ «%»
заменяет в шаблоне строки любую последовательность символов, а вместо
строки «Иван» в исходном запросе (см. листинг 1.36) мы используем
значение параметра &ЧастьНаименования.
В консоли запросов нажмем кнопку Заполнить параметры, и параметр
ЧастьНаименования типа Строка будет добавлен в окно параметров консоли
запросов. В поле Значение мы можем задавать произвольные значения параметра и смотреть на результат выполнения запроса.
Так, если мы зададим значение параметра равным строке «Иван», в результате выполнения запроса увидим только тех клиентов, часть наименования
которых совпадает с подстрокой «Иван» (рис. 1.29).
Глава 1. Механизм запросов
51
Рис. 1.29. Выполнение параметризированного запроса в консоли запросов
То есть результат запроса полностью совпадает с результатом при выполнении
запроса, не использующего параметры в условии отбора (см. рис. 1.27).
Но очевидно, что использование параметров делает запрос более гибким, так
как значение отбора не записывается жестко в тексте запроса, а может быть
задано пользователем в процессе работы и передано в запрос в качестве
параметра.
Помимо того, что нам не понадобилось создавать специальный интерфейс
для ввода значения параметра, в консоли запросов был скрыт от нас еще
один важный момент. А именно то, что при выполнении запроса значения
параметров должны быть переданы в запрос.
подробнее
Передача параметров в запрос с помощью встроенного языка рассматривается в разделе «Передача параметров в запрос», стр. 148.
Аналогично (с использованием параметров) мы можем переписать запрос
для отбора из справочника тех клиентов, телефоны которых не соответствуют
заданному шаблону (листинг 1.38).
52
Язык запросов «1С:Предприятия 8»
Листинг 1.38. Отбор записей справочника «Клиенты» по условию
// Условие без параметров
ВЫБРАТЬ
Клиенты.Наименование,
Клиенты.Адрес,
Клиенты.Телефон КАК Телефон
ИЗ
Справочник.Клиенты КАК Клиенты
ГДЕ
НЕ Телефон ПОДОБНО "_-___-___-__-__"
// Параметризированное условие
ВЫБРАТЬ
Клиенты.Наименование,
Клиенты.Адрес,
Клиенты.Телефон КАК Телефон
ИЗ
Справочник.Клиенты КАК Клиенты
ГДЕ
НЕ Телефон ПОДОБНО &ШаблонТелефона
Нажмем кнопку Заполнить параметры и зададим значение параметра
ШаблонТелефона как «_-___-___-__-__». Результат запроса (рис. 1.30) полностью совпадает с результатом при выполнении запроса, не использующего
параметры в условии отбора (см. рис. 1.28).
Рис. 1.30. Отбор записей справочника «Клиенты» по параметризированному условию
Как получить данные из табличной части некоторого документа
Часто может понадобиться вывести все данные из табличной части определенного документа или элемента справочника или другого объекта конфигурации, имеющего табличную часть.
Для этого в языке запросов существует удобная возможность – обращаться
к табличной части как к отдельной таблице. При этом в тексте запроса можно
использовать все ранее изученные нами конструкции: ВЫБРАТЬ, ИЗ и т. п.
Синтаксис обращения к таблице-источнику включает также имя табличной
части – .., например Документ.ЗаказТовара.Состав.
Следующий запрос выводит данные всех табличных частей из всех документов ЗаказТовара (листинг 1.39).
Листинг 1.39. Вывод данных всех табличных частей из всех документов «ЗаказТовара»
ВЫБРАТЬ
СоставЗаказа.Товар,
СоставЗаказа.Количество,
СоставЗаказа.Сумма
ИЗ
Документ.ЗаказТовара.Состав КАК СоставЗаказа
Глава 1. Механизм запросов
53
В начале главы, на стр. 13, мы рассматривали, как хранятся данные табличных
частей в информационной базе «1С:Предприятия». Поля выборки запроса
Товар, Количество, Сумма – это реквизиты табличной части документа,
находящиеся в отдельной таблице базы данных, к которой мы обращаемся
запросом. В этой таблице хранятся данные всех табличных частей документов
определенного вида.
Результат выполнения запроса представлен на рис. 1.31.
Рис. 1.31. Вывод данных всех табличных частей из всех документов «ЗаказТовара»
Однако в таком виде мы не можем сказать, к какому клиенту и к какому заказу
относится каждая из записей результата запроса. Для повышения информативности данных мы можем добавить в список полей выборки реквизиты
документа Номер и Клиент.
Мы знаем, что поля Номер и Клиент хранятся в основной таблице документа,
а поля Товар, Количество, Сумма из табличной части документа – в подчиненной таблице, связанной с основной таблицей по полю Ссылка.
Поскольку в нашем примере источником запроса является подчиненная
таблица документа Документ.ЗаказТовара.Состав, то, чтобы получить данные
из основной таблицы документа, нужно обращаться к полям таблицы через
точку от поля табличной части Ссылка (листинг 1.40).
Листинг 1.40. Вывод данных из основной и подчиненной таблицы
документов «ЗаказТовара»
ВЫБРАТЬ
СоставЗаказа.Ссылка.Номер,
СоставЗаказа.Ссылка.Клиент,
СоставЗаказа.Товар,
СоставЗаказа.Количество,
СоставЗаказа.Сумма
ИЗ
Документ.ЗаказТовара.Состав КАК СоставЗаказа
54
Язык запросов «1С:Предприятия 8»
Результат выполнения запроса представлен на рис. 1.32.
Рис. 1.32. Вывод данных из основной и подчиненной таблицы
документов «ЗаказТовара»
Конечно, когда данных много, обычно требуется отобрать информацию
из определенного документа. Для этого нужно добавить в запрос параметризированное условие, содержащее параметр, имеющий тип ссылки на
документ ЗаказТовара (листинг 1.41).
Листинг 1.41. Вывод данных из определенного документа
ВЫБРАТЬ
СоставЗаказа.Ссылка.Номер,
СоставЗаказа.Ссылка.Клиент,
СоставЗаказа.Товар,
СоставЗаказа.Количество,
СоставЗаказа.Сумма
ИЗ
Документ.ЗаказТовара.Состав КАК СоставЗаказа
ГДЕ
СоставЗаказа.Ссылка = &Документ
Нажмем кнопку Заполнить параметры, и параметр Документ будет добавлен
в окно параметров консоли запросов. Причем консоль запросов из текста
запроса автоматически определяет не только имя, но и тип параметра.
В предыдущем примере (см. рис. 1.29) мы использовали в условии отбора
параметр примитивного типа (Строка). В данном случае параметр запроса
имеет ссылочный тип – ДокументСсылка.ЗаказТовара.
В результате выполнения запроса мы увидим только те записи, которые
относятся к конкретному заказу товаров, указанному в параметре Документ
(рис. 1.33).
На самом деле данные для этого запроса выбирались из двух взаимосвязанных
таблиц (основной иподчиненной таблиц документа, связанных по полю
Ссылка), но подробнее мы остановимся на этом позже.
Глава 1. Механизм запросов
55
Рис. 1.33. Вывод данных из определенного документа
подробнее
Раздел «Как получить данные из таблицы, на которую ссылается поле
другой таблицы», стр. 110.
Как получить данные из табличной части документа
в качестве вложенной таблицы
Информацию из табличной части документа можно получить также, не
обращаясь к подчиненной таблице, сразу из основной таблицы документа.
В этом случае поле результата запроса, содержащее данные из табличной
части (например, ЗаказТовара.Состав), будет иметь тип РезультатЗапроса, то
есть содержать вложенный результат запроса, сформированный на основе
табличной части (листинг 1.42).
Листинг 1.42. Выбор данных из табличной части в качестве вложенной таблицы
ВЫБРАТЬ
ЗаказТовара.Номер,
ЗаказТовара.Клиент,
ЗаказТовара.СуммаЗаказа,
56
ИЗ
Язык запросов «1С:Предприятия 8»
ЗаказТовара.Состав
Документ.ЗаказТовара КАК ЗаказТовара
Вложенный результат запроса к табличной части состоит из стандартных
полей Ссылка, НомерСтроки и полей с именами, соответствующими именам
реквизитов табличной части документа (рис. 1.34).
Рис. 1.34. Поля табличной части, выбираемые запросом
Можно указать конкретные поля табличной части, которые нужно выбрать
запросом (листинг 1.43).
Листинг 1.43. Выбор данных из табличной части в качестве вложенной таблицы
// Первый вариант – одна вложенная таблица
ВЫБРАТЬ
ЗаказТовара.Номер,
ЗаказТовара.Клиент,
ЗаказТовара.СуммаЗаказа,
ЗаказТовара.Состав.(
Товар,
Количество,
Сумма
)
ИЗ
Документ.ЗаказТовара КАК ЗаказТовара
// Второй вариант – две вложенные таблицы
ВЫБРАТЬ
ЗаказТовара.Номер,
ЗаказТовара.Клиент,
ЗаказТовара.СуммаЗаказа,
ЗаказТовара.Состав.Товар,
ЗаказТовара.Состав.Сумма
ИЗ
Документ.ЗаказТовара КАК ЗаказТовара
Важно понимать, что при выполнении первого запроса будет получена одна
вложенная таблица Состав, содержащая поля Товар, Количество и Сумма.
А при выполнении второго запроса будет получено две вложенных таблицы:
таблица Состав, содержащая результат запроса, который включает един-
Глава 1. Механизм запросов
57
ственное поле Товар, и таблица Состав1, содержащая результат запроса,
который включает единственное поле Сумма (рис. 1.35).
Рис. 1.35. Вывод данных из документов «ЗаказТовара» в консоли запросов
При выполнении этих запросов в консоли запросов вместо данных вложенной
таблицы будет выведен текст ТаблицаЗначений. Это значит, что данные
табличной части, выбранные в качестве вложенного результата запроса,
могут быть обработаны только с помощью встроенного языка.
подробнее
Обход результата запроса, содержащего данные табличной части, рассматривается в разделе «Обход выборки результата запроса, содержащего
данные табличной части», стр. 162.
Как получить записи иерархической таблицы
и расположить их в порядке иерархии
В этом примере мы рассмотрим особенности получения данных из иерархического справочника.
В нашей демонстрационной конфигурации существует иерархический справочник Товары с типом иерархии Иерархия групп и элементов. Такой справочник состоит из групп и элементов. Группы – это элементы справочника,
которые имеют (или могут иметь) подчиненные себе элементы или другие
группы. В отличие от них, элементы не имеют (и не могут иметь) подчиненных себе записей.
Иерархия может быть многоуровневой, то есть внутри групп могут быть
другие группы и элементы, не являющиеся группами (рис. 1.36).
58
Язык запросов «1С:Предприятия 8»
Рис. 1.36. Структура иерархического справочника
У рассмотренного на рис. 1.36 иерархического справочника есть стандартные
поля: поле Родитель (ссылка на родительские записи у дочерних записей)
и поле ЭтоГруппа (ИСТИНА для записей, являющихся группой, и ЛОЖЬ для
записей, не являющихся группой).
В нашей демонстрационной конфигурации справочник Товары имеет трехуровневую иерархию и выглядит следующим образом (рис. 1.37).
Глава 1. Механизм запросов
59
Рис. 1.37. Справочник «Товары» в режиме «1С:Предприятие»
Выберем поля Код, Наименование, Родитель, ЭтоГруппа из справочника Товары
и отсортируем их по наименованию уже знакомым нам запросом (листинг
1.44).
Листинг 1.44. Вывод записей иерархического справочника «Товары»
ВЫБРАТЬ
Товары.Код,
Товары.Наименование КАК Наименование,
Товары.Родитель,
Товары.ЭтоГруппа
ИЗ
Справочник.Товары КАК Товары
УПОРЯДОЧИТЬ ПО
Наименование
Результат выполнения запроса представлен на рис. 1.38.
Мы видим, что записи справочника отсортированы по наименованию,
но элементы групп (Обувь, Детская обувь, Продукты) идут вперемешку.
60
Язык запросов «1С:Предприятия 8»
Поскольку справочник является иерархическим, гораздо привычнее видеть
записи этого справочника расположенными в иерархическом порядке.
По умолчанию так и сделано в «1С:Предприятии».
Для этого изменим текст запроса. В предложении УПОРЯДОЧИТЬ ПО после
имени поля Наименование напишем ключевое слово ИЕРАРХИЯ (листинг 1.45).
Листинг 1.45. Вывод записей справочника «Товары»,
расположенных в порядке иерархии
ВЫБРАТЬ
Товары.Код,
Товары.Наименование КАК Наименование,
Товары.Родитель,
Товары.ЭтоГруппа
ИЗ
Справочник.Товары КАК Товары
УПОРЯДОЧИТЬ ПО
Ссылка ИЕРАРХИЯ,
Наименование
В результате выполнения этого запроса записи справочника будут расположены в порядке иерархии – начиная от самых верхних корневых записей до
самых последних элементов в цепочке иерархии (рис. 1.39).
Рис. 1.39. Вывод записей справочника «Товары», расположенных в порядке иерархии
Теперь покажем, как получать запросом реквизиты иерархического справочника с иерархией групп и элементов. Справочник Товары в демонстрационной конфигурации имеет реквизит Производитель, свойство Использование
которого установлено в значение Для элемента.
Следует учитывать тот факт, что реквизиты, которые используются только
для элементов справочника, будут содержать значение NULL в записях,
которые являются группами. Аналогично реквизиты, у которых свойство
Использование установлено в значение Для группы, будут содержать NULL
в записях-элементах. Важно понимать, что значение NULL не является нулем
или пустой строкой. Значения данного типа обозначают отсутствующие
значения или значения, не имеющие смысла.
Глава 1. Механизм запросов
61
Для примера выполним запрос, в котором для записей, содержащих значение
NULL в поле Производитель, выводится строка «NULL». В противном случае
выводится содержимое поля Производитель (листинг 1.46).
Листинг 1.46. Вывод значения реквизита иерархического справочника
ВЫБРАТЬ
Товары.Наименование КАК Наименование,
Товары.ЭтоГруппа,
ВЫБОР
КОГДА (Товары.Производитель) ЕСТЬ NULL ТОГДА "NULL"
ИНАЧЕ Товары.Производитель
КОНЕЦ КАК Производитель
ИЗ
Справочник.Товары КАК Товары
УПОРЯДОЧИТЬ ПО
Наименование ИЕРАРХИЯ
Для формирования поля выборки Производитель используется операция
выбора (ВЫБОР (КОГДА … ТОГДА) ИНАЧЕ … КОНЕЦ). В этой операции после
ключевого слова КОГДА записывается условие выбора, после ключевого
слова ТОГДА следует значение поля выборки в случае, если условие истинно.
В общем случае в операции выбора может указываться неограниченное
количество альтернативных одиночных выборов КОГДА … ТОГДА. Значение
выражения, указанного после слова ИНАЧЕ, используется в качестве результата операции выбора в том случае, если ни одно из ранее указанных альтернативных условий выбора не было выполнено.
В результате выполнения запроса мы видим, что для записей, являющихся
группами (Обувь, Детская обувь, Продукты), в поле выборки запроса Производитель выводится строка «NULL», а для элементов справочника, не являющихся группой, выводится содержимое поля Производитель (рис. 1.40).
При этом значения этого поля для некоторых записей (Пинетки, Кроссовки,
Сметана) содержат пустую строку, но это не значение NULL – просто для этих
элементов справочника значение реквизита Производитель было не заполнено
(см. рис. 1.37).
62
Язык запросов «1С:Предприятия 8»
Как отобрать записи иерархической таблицы по условию
В этом примере мы рассмотрим типичные задачи получения записей из иерархической таблицы. Для начала посмотрим еще раз, как выглядит справочник
Товары в нашей демонстрационной конфигурации (рис. 1.41).
Рис. 1.41. Справочник «Товары» в режиме «1С:Предприятие»
На рис. 1.41 справочник представлен в виде дерева, на котором отображаются
все уровни иерархии. Мы видим, что элементы Обувь и Продукты являются
группами справочника (т. е. имеют подчиненные записи), а также они являются корневыми элементами, (т. е. не имеют родителей). Группа Продукты
является родителем элементов Масло, Молоко, Сметана. Группа Обувь является
родителем элементов Кроссовки, Сапоги и Туфли, а также имеет в подчинении
(в иерархии) группу товаров Детская обувь, которая, в свою очередь, является
родителем для элемента Пинетки. Все элементы справочника, кроме записей,
являющихся группами (Обувь, Детская обувь, Продукты), не имеют и не могут
иметь других подчиненных элементов.
Как получить записи иерархической таблицы, не являющиеся группой
Для решения поставленной задачи с помощью предложения ГДЕ зададим
условие отбора записей из таблицы так, чтобы выводились только записи
справочника, не являющиеся группой (листинг 1.47).
Листинг 1.47. Вывод записей справочника «Товары», не являющихся группой
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Родитель,
Товары.ЭтоГруппа
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.ЭтоГруппа = ЛОЖЬ
Глава 1. Механизм запросов
63
В данном запросе в условии отбора мы сравниваем значение поля ЭтоГруппа
с логическим литералом ЛОЖЬ. Условие выполняется для всех записей справочника, не являющихся группой, о чем говорилось выше.
Результат выполнения запроса представлен на рис. 1.42.
Рис. 1.42. Вывод записей справочника «Товары», не являющихся группой
Соответственно, для вывода только групп из справочника нужно задать
условие отбора Товары.ЭтоГруппа = ИСТИНА.
Теперь покажем, как сделать это же условие параметризированным и, следовательно, более гибким. Для этого перепишем предыдущий запрос с использованием параметров (листинг 1.48).
Листинг 1.48. Вывод записей справочника «Товары», не являющихся группой
// Условие без параметров
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Родитель,
Товары.ЭтоГруппа
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.ЭтоГруппа = ЛОЖЬ
// Параметризированное условие
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Родитель,
Товары.ЭтоГруппа
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.ЭтоГруппа = &ЭтоГруппа
Нажмем кнопку Заполнить параметры, и параметр ЭтоГруппа будет добавлен
в окно параметров консоли запросов. В данном случае тип параметра –
Булево, поэтому в поле Значение мы можем выбрать возможные логические
значения параметра (Да/Нет).
64
Язык запросов «1С:Предприятия 8»
Если мы зададим значение параметра как Нет, то в результате выполнения
запроса увидим только те записи иерархического справочника Товары,
которые не являются группой (рис. 1.43).
Рис. 1.43. Выполнение параметризированного запроса в консоли запросов
То есть результат запроса полностью совпадает с результатом при выполнении запроса, не использующего параметры в условии отбора (см. рис.
1.42). Изменив значение параметра ЭтоГруппа на Да, с помощью этого же
параметризированного запроса мы получим только те записи справочника,
которые являются группой.
Как получить записи иерархической таблицы,
находящиеся в выбранной группе
Для решения поставленной задачи зададим параметризированное условие
отбора записей из таблицы, родителем которых является выбранная группа.
Для этого нужно использовать параметр ссылочного типа, имеющий тип
ссылки на справочник Товары.
Добавим в условие отбора предыдущего запроса условие для выбора
из иерархического справочника тех записей, для которых родителем
является элемент справочника, указанный в параметре типа СправочникСсылка.Товары. Это можно сделать с помощью следующего запроса (листинг 1.49).
Глава 1. Механизм запросов
65
Листинг 1.49. Отбор записей справочника «Товары» по условию
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Родитель,
Товары.ЭтоГруппа
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.ЭтоГруппа = &ЭтоГруппа
И Товары.Родитель = &Родитель
В данном запросе условие отбора определено как сложное логическое выражение, в котором простые логические выражения соединяются между собой
логическим оператором И. В первом выражении проверяется, является ли
запись группой, во втором выражении проверяется, принадлежит ли запись
указанной группе. В целом условие отбора будет истинно, если оба этих
условия будут выполнены.
При нажатии кнопки Заполнить параметры параметр ЭтоГруппа и параметр
Родитель типа СправочникСсылка.Товары будут добавлены в окно параметров консоли запроса. Укажем в качестве значения параметра группу
Обувь справочника Товары, в качестве значения параметра ЭтоГруппа – Нет
и выполним запрос (рис. 1.44).
Рис. 1.44. Выполнение параметризированного запроса в консоли запросов
66
Язык запросов «1С:Предприятия 8»
Как мы видим, в результат запроса попадают только те записи справочника,
которые не являются группой и родителем которых является группа Обувь.
Изменив значение параметра ЭтоГруппа на Да, с помощью этого же параметризированного запроса мы получим только те записи справочника, которые
являются группой и родителем которых является группа Обувь (рис. 1.45).
Рис. 1.45. Вывод только групп из выбранной группы товаров
Как получить «корневые» записи иерархической таблицы
Для решения поставленной задачи нужно отобрать записи таблицы, которые
находятся в «корне» иерархического справочника, то есть те записи,
у которых нет родителя. Для этого зададим условие отбора записей из
таблицы так, чтобы выводились только записи справочника, у которых в поле
Родитель находится пустая ссылка на справочник Товары (листинг 1.50).
Листинг 1.50. Отбор корневых записей справочника «Товары»
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Родитель,
Товары.ЭтоГруппа
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.Родитель = ЗНАЧЕНИЕ(Справочник.Товары.ПустаяСсылка)
В условии отбора данного запроса используется литерал функционального
типа ЗНАЧЕНИЕ(), с помощью которого в языке запросов можно получить
доступ к предопределенным данным конфигурации и к значениям системных
перечислений. В данном случае проверяется, является ли пустой ссылкой на
справочник Товары значение поля справочника Родитель. Если это так, то это
корневые записи справочника.
Глава 1. Механизм запросов
67
Как получить записи иерархической таблицы,
находящиеся в иерархии выбранной группы
Для решения поставленной задачи нужно отобрать записи таблицы, которые
находятся в подчинении указанной группе. На первый взгляд это условие
отбора схоже с отбором записей справочника по родителю, который мы
рассматривали выше. Однако при таком условии мы увидим только те записи
иерархического справочника, непосредственным родителем которых является выбранная группа товаров. Построить всю иерархию подчиненности
элементов сверху вниз так не получится. Для этого в условии отбора нужно
использовать ключевое слово В ИЕРАРХИИ и в качестве параметра передать
в список иерархии группу товаров (листинг 1.51).
Листинг 1.51. Отбор записей справочника «Товары»,
находящихся в иерархии выбранной группы
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Родитель,
Товары.ЭтоГруппа
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.Ссылка В ИЕРАРХИИ (&ГруппаТоваров)
УПОРЯДОЧИТЬ ПО
Наименование ИЕРАРХИЯ
В результате выполнения данного запроса будут выбраны все записи справочника, которые иерархически принадлежат группе, указанной в параметре
ГруппаТоваров.
Укажем в качестве значения параметра группу Обувь и получим не только
записи, родителем которых является выбранная группа, но и все элементы,
находящиеся ниже по уровню иерархии (рис. 1.47).
Рис. 1.47. Отбор записей справочника «Товары»,
находящихся в иерархии выбранной группы товаров
68
Язык запросов «1С:Предприятия 8»
Мы видим, что сам элемент справочника, переданный в список иерархии
(неважно, является он группой справочника или нет), также включается
в результат запроса (первая запись в таблице). Этот момент связан с особенностью выполнения конструкции В ИЕРАРХИИ языка запросов.
Если мы укажем в качестве значения параметра группу Детская обувь, то
результат выполнения запроса будет следующим (рис. 1.48).
Рис. 1.48. Отбор записей справочника «Товары»,
находящихся в иерархии выбранной группы товаров
Как получить всех родителей для выбранного элемента
иерархической таблицы
Для решения поставленной задачи требуется построить всю цепочку вверх по
иерархии от выбранного элемента иерархической таблицы. Для этого нужно
использовать несколько запросов и уметь обходить результаты полученных
запросов с помощью встроенного языка. Поэтому такой пример мы рассмотрим позже на стр. 189.
Как узнать среднюю цену, по которой продавался товар
Часто значения некоторых полей в таблицах могут повторяться. Например,
вводится несколько заказов для одного и того же клиента, или один и тот же
товар продается в расходных накладных. Обычно в прикладных задачах
требуется узнать, какова максимальная сумма заказа для каждого клиента,
сколько и по каким ценам продали каждого товара и т. п.
Для решения подобных задач применяется группировка записей. Сущность
этой операции заключается в том, что вместо нескольких записей из исходной
таблицы, имеющих одинаковое значение некоторого поля, в результат запроса
включается одна запись, в которой от значений других полей вычисляется
некоторое обобщенное значение (рассчитывается агрегатная функция).
Например, значения другого поля для группируемых записей суммируются,
вычисляется максимальное, минимальное значение, количество и т. д.
Рассмотрим данную задачу на примере расходных накладных. Для начала
мы просто выведем все записи из состава расходных накладных, упорядочив
их по ссылкам товаров (листинг 1.52). Делается это простым запросом
к табличной части документа, который мы рассматривали в разделе «Как
получить данные из табличной части некоторого документа» на стр. 52.
Глава 1. Механизм запросов
69
Листинг 1.52. Вывод всех записей из состава расходных накладных,
упорядоченных по ссылкам товаров
ВЫБРАТЬ
НакладнаяСостав.Товар КАК Товар,
НакладнаяСостав.Количество,
НакладнаяСостав.Цена,
НакладнаяСостав.Сумма
ИЗ
Документ.РасходнаяНакладная.Состав КАК НакладнаяСостав
УПОРЯДОЧИТЬ ПО
Товар
Даже на нашей небольшой демонстрационной конфигурации получается
довольно большой список товаров, но с его помощью будет понятна сущность
группировки записей (рис. 1.49).
Рис. 1.49. Вывод всех записей из состава расходных накладных,
упорядоченных по ссылкам товаров
Итак, в результате запроса содержится 17 записей, из них 4 записи для
товара Туфли, 4 записи для товара Сапоги, 3 записи для товара Кроссовки и т. д.
При этом записи с одинаковым значением поля Товар расположены друг
за другом. Мы видим, что цена одного и того же товара может быть разной.
Так, цена товара Туфли колеблется от 6 000 до 8 000.
Допустим, нас интересует минимальная, максимальная и средняя цена, по
которой продавался каждый из товаров. Для решения поставленной задачи
нужно собрать вместе записи исходной таблицы с одинаковым значением
поля Товар и применить к каждому товару агрегатные функции МИНИМУМ(),
МАКСИМУМ() и СРЕДНЕЕ() со значением цены товара в качестве параметра
этих функций.
70
Язык запросов «1С:Предприятия 8»
Для этого в языке запросов предназначено ключевое слово СГРУППИРОВАТЬ
ПО, после которого следует список полей, по которым нужно сгруппировать
записи исходной таблицы (листинг 1.53).
Листинг 1.53. Вывод минимальной, максимальной
и средней цены каждого товара из состава расходных накладных
ВЫБРАТЬ
НакладнаяСостав.Товар КАК Товар,
МИНИМУМ(НакладнаяСостав.Цена) КАК Минимум,
МАКСИМУМ(НакладнаяСостав.Цена) КАК Максимум,
СРЕДНЕЕ(НакладнаяСостав.Цена) КАК Среднее
ИЗ
Документ.РасходнаяНакладная.Состав КАК НакладнаяСостав
СГРУППИРОВАТЬ ПО
НакладнаяСостав.Товар
Обратите внимание, что в списке полей выборки запроса, помимо полей,
по которым группируются записи исходной таблицы, могут присутствовать только агрегатные функции СУММА(), МИНИМУМ(), МАКСИМУМ(),
СРЕДНЕЕ(), КОЛИЧЕСТВО(), применяемые к исходным записям с одинаковым
значением поля группировки.
Агрегатные функции МИНИМУМ(), МАКСИМУМ() и СРЕДНЕЕ() вычисляют
соответственно минимальное, максимальное и среднее значение из всех
записей с одинаковым значением поля группировки.
В результате выполнения запроса выводится 5 записей вместо 17. То есть для
каждого товара вычисляется минимальная, максимальная и средняя цена его
продаж. В результате запроса записи исходной таблицы с одинаковым значением поля Товар «сворачиваются» в одну запись, в которой помимо самого
поля группировки выводятся значения агрегатных функций (рис. 1.50).
Рис. 1.50. Вывод минимальной, максимальной и средней цены
каждого товара из состава расходных накладных
В предложении СГРУППИРОВАТЬ ПО должны указываться именно имена
полей, а не их псевдонимы, определенные в запросе.
При использовании агрегатных функций предложение СГРУППИРОВАТЬ ПО
может быть и не указано совсем, при этом все записи исходной таблицы будут
сгруппированы в одну-единственную строку.
Глава 1. Механизм запросов
71
Как узнать общее количество и сумму продаж
каждого товара в разрезе покупателей
Сначала покажем простой запрос, с помощью которого можно узнать, сколько
всего было продано каждого из товаров.
Для этого записи из состава расходных накладных с одинаковым значением
поля Товар нужно собрать вместе, затем для каждого товара с помощью агрегатной функции СУММА() подсчитать суммарное значение поля Количество
и вывести в полях выборки запроса (листинг 1.54).
Листинг 1.54. Вывод общего количества единиц проданных товаров
из состава расходных накладных
ВЫБРАТЬ
НакладнаяСостав.Товар КАК Товар,
СУММА(НакладнаяСостав.Количество) КАК Количество
ИЗ
Документ.РасходнаяНакладная.Состав КАК НакладнаяСостав
СГРУППИРОВАТЬ ПО
НакладнаяСостав.Товар
Агрегатная функция СУММА() вычисляет арифметическую сумму всех
записей с одинаковым значением поля группировки.
В результате запроса мы видим общее количество единиц проданных товаров
из состава расходных накладных (рис. 1.51).
Рис. 1.51. Вывод общего количества единиц проданных товаров
из состава расходных накладных
Часто требуется сгруппировать записи исходной таблицы по значению
нескольких полей сразу. Для этого после ключевого слова СГРУППИРОВАТЬ
ПО нужно перечислить через запятую список полей группировки.
Например, нам нужно увидеть, сколько и на какую сумму продано каждого
товара по каждому покупателю. Это можно сделать с помощью следующего
запроса (листинг 1.55).
Листинг 1.55. Группировка записей по нескольким полям
ВЫБРАТЬ
НакладнаяСостав.Ссылка.Покупатель КАК Покупатель,
НакладнаяСостав.Товар КАК Товар,
СУММА(НакладнаяСостав.Количество) КАК Количество,
СУММА(НакладнаяСостав.Сумма) КАК Сумма
72
Язык запросов «1С:Предприятия 8»
ИЗ
Документ.РасходнаяНакладная.Состав КАК НакладнаяСостав
СГРУППИРОВАТЬ ПО
НакладнаяСостав.Ссылка.Покупатель,
НакладнаяСостав.Товар
В данном запросе записи исходной таблицы с одинаковыми значениями полей
Покупатель и Товар собираются вместе. Причем поскольку мы обращаемся к
табличной части документа РасходнаяНакладная, для выбора реквизита Покупатель мы обращаемся к нему через точку от поля табличной части Ссылка. Затем
для каждого товара по каждому покупателю с помощью агрегатной функции
СУММА() подсчитывается суммарное значение полей Количество и Сумма из
состава расходных накладных и выводится в полях выборки запроса.
Обратите внимание, что в тексте запроса имена полей в списке группировки
принято располагать на разных строках со смещением относительно ключевого слова СГРУППИРОВАТЬ ПО.
В результате в нижней таблице мы видим общее количество и сумму продаж
каждого товара по каждому покупателю, а в верхней таблице для сравнения
приведен состав всех расходных накладных, упорядоченный по покупателям
и товарам (рис. 1.52).
Рис. 1.52. Общее количество и сумма продаж каждого товара по каждому покупателю
Глава 1. Механизм запросов
73
Иногда требуется наложить условие на значения агрегатных функций, применяемых к исходным записям с одинаковым значением поля группировки.
Это можно сделать с помощью ключевого слова ИМЕЮЩИЕ, после которого
задается условие отбора аналогично условию в предложении ГДЕ, но только
оно накладывается не на исходные записи, а на записи, получившиеся
в результате группировки.
Например, нам нужно увидеть, какие товары и какому покупателю были
проданы на сумму свыше 50 000. Это можно сделать с помощью следующего
запроса (листинг 1.56).
Листинг 1.56. Отбор товаров, проданных на сумму более 50 000
по каждому покупателю
ВЫБРАТЬ
НакладнаяСостав.Ссылка.Покупатель КАК Покупатель,
НакладнаяСостав.Товар КАК Товар,
СУММА(НакладнаяСостав.Количество) КАК Количество,
СУММА(НакладнаяСостав.Сумма) КАК Сумма
ИЗ
Документ.РасходнаяНакладная.Состав КАК НакладнаяСостав
СГРУППИРОВАТЬ ПО
НакладнаяСостав.Ссылка.Покупатель,
НакладнаяСостав.Товар
ИМЕЮЩИЕ
СУММА(НакладнаяСостав.Сумма) > 50000
То есть мы просто добавили в текст предыдущего запроса конструкцию
ИМЕЮЩИЕ .
В итоге из общего списка продаж каждого товара по каждому покупателю
(верхняя таблица) в результат запроса попадут только те записи (нижняя
таблица), в которых сумма продаж товара по каждому покупателю, вычисленная в результате группировки, больше 50 000 (рис. 1.53).
Рис. 1.53. Отбор товаров, проданных на сумму более 50 000 по каждому покупателю
74
Язык запросов «1С:Предприятия 8»
Как узнать среднюю цену поступления товара,
не группируя сами записи
Очень часто требуется не только вывести определенные данные в результат
запроса, но и рассчитать по ним некоторые обобщенные данные (построить
итоги). Что значит рассчитать итоги и в чем отличие построения итогов от
группировки записей, поясним на конкретном примере.
Для наглядности проведем аналогию с примером «Как узнать среднюю
цену, по которой продавался товар», стр. 68. В нем мы группировали записи
исходной таблицы по полю Товар, чтобы узнать, например, среднюю цену
продаж каждого товара. Но при группировке записи, содержащие одинаковые
значения полей, по которым группируются исходные данные, сворачиваются
в одну, для каждой группы записей вычисляются значения агрегатных
функций и помещаются в результат запроса.
При построении итогов (так же, как и при группировке) записи, содержащие
одинаковые значения полей, по которым нужно рассчитать итоги, собираются вместе, для каждой группы записей вычисляются значения агрегатных
функций. При этом результат вычислений помещается в итоговые строки
и добавляется к записям исходной таблицы (детальным записям). То есть
детальные записи не сворачиваются, а, наоборот, достраиваются относительно исходной таблицы и служат для детализации итоговых строк.
На рис. 1.54 демонстрируется отличие группировки записей из состава
приходных накладных по полю Товар от расчета итогов по этому же полю.
В верхней таблице выводятся все записи из состава приходных накладных,
в которых записи с одинаковым значением поля Товар расположены друг за
другом.
В нижней левой таблице выводится результат построения итогов по полю
Товар, при этом для каждого товара рассчитывается средняя цена поступления,
а также общее количество и сумма поступления товара. Результат вычислений
помещается в итоговые строки и добавляется к детальным записям.
В нижней правой таблице выводится результат группировки записей
приходных накладных по полю Товар. При этом для каждого товара рассчитываются те же агрегатные функции, но в результате запроса для каждого
товара выводится одна запись, в которую помещается результат вычисления
агрегатных функций.
Нетрудно увидеть, что итоговые строки в левой таблице (18 = 12 + 6) совпадают со строками в правой таблице (6) и дополняются строками из верхней
таблицы (12).
Для построения итогов используется предложение ИТОГИ … ПО, которое
располагается в секции Описание итогов текста запроса. Все секции запроса
приведены на рис. 1.13, стр. 25.
Глава 1. Механизм запросов
75
Рис. 1.54. Отличие группировки записей таблицы
от построения итогов по одному и тому же полю
Получить результат запроса, показанный на рис. 1.54, содержащий итоги
по полю Товар, можно с помощью следующего запроса (листинг 1.57).
Листинг 1.57. Вывод всех записей из состава приходных накладных с итоговыми
данными для каждого товара
ВЫБРАТЬ
НакладнаяСостав.Товар КАК Товар,
НакладнаяСостав.Количество КАК Количество,
НакладнаяСостав.Цена КАК Цена,
НакладнаяСостав.Сумма КАК Сумма
ИЗ
Документ.ПриходнаяНакладная.Состав КАК НакладнаяСостав
ИТОГИ
СУММА(Количество),
СРЕДНЕЕ(Цена),
СУММА(Сумма)
ПО
Товар
76
Язык запросов «1С:Предприятия 8»
Описание итогов начинается с ключевого слова ИТОГИ. После него следует
список итоговых полей, перечисленных через запятую. В качестве итоговых
полей обычно выступает результат вычисления агрегатных функций, рассчитанный для детальных записей с одинаковыми значениями полей, по которым
требуется рассчитать итоги. Затем следует ключевое слово ПО, после которого
перечисляется список контрольных точек – полей, по которым необходимо
рассчитать итоги.
В данном случае Товар – это контрольная точка, а СУММА(Количество),
СРЕДНЕЕ(Цена), СУММА(Сумма) – это итоговые поля, которые требуется
рассчитать для каждой контрольной точки.
В результате выполнения запроса мы видим 18 записей – 12 записей из
исходной таблицы (см. рис. 1.54) и 6 итоговых записей, по одной на каждый
товар. Итоговая запись содержит представление самого товара, а также общее
количество, сумму и среднюю цену его поступления (рис. 1.55).
Рис. 1.55. Вывод всех записей из состава приходных накладных
с итоговыми данными для каждого товара
Заметьте, что итоговые поля могут рассчитываться необязательно для всех
полей выборки запроса, как в данном случае. Например, итог может рассчитываться только для поля Сумма или не рассчитываться вовсе.
Расчет итогов для иерархического справочника
В данном примере в качестве детальных записей для расчета итогов мы
также будем использовать записи из состава приходных накладных.
Поскольку поле Товар, для которого рассчитываются итоги, имеет тип ссылки
на иерархический справочник Товары, то в результат запроса можно включить также итоги по иерархии этого справочника. Для этого после имени поля
необходимо указать ключевое слово ИЕРАРХИЯ (листинг 1.58).
Глава 1. Механизм запросов
77
Листинг 1.58. Вывод итоговых данных по товарам с итогами по иерархии
ВЫБРАТЬ
НакладнаяСостав.Товар КАК Товар,
НакладнаяСостав.Количество КАК Количество,
НакладнаяСостав.Цена КАК Цена,
НакладнаяСостав.Сумма КАК Сумма
ИЗ
Документ.ПриходнаяНакладная.Состав КАК НакладнаяСостав
ИТОГИ
СУММА(Количество),
СУММА(Сумма)
ПО
Товар ИЕРАРХИЯ КАК ТоварИерархия
Обратите внимание, что для полей, по которым рассчитываются итоги, также
как и для полей выборки запроса, можно назначить псевдоним для последующего обращения к ним из встроенного языка.
При необходимости можно рассчитать только итоги значений по иерархии,
без расчета итогов в контрольных точках. Для этого перед ключевым словом
ИЕРАРХИЯ нужно указать ключевое слово ТОЛЬКО (листинг 1.59).
Листинг 1.59. Вывод итоговых данных только по иерархии товаров
ВЫБРАТЬ
НакладнаяСостав.Товар КАК Товар,
НакладнаяСостав.Количество КАК Количество,
НакладнаяСостав.Цена КАК Цена,
НакладнаяСостав.Сумма КАК Сумма
ИЗ
Документ.ПриходнаяНакладная.Состав КАК НакладнаяСостав
ИТОГИ
СУММА(Количество),
СУММА(Сумма)
ПО
Товар ТОЛЬКО ИЕРАРХИЯ КАК ТоварТолькоИерархия
В результате выполнения запросов, представленных в листингах 1.58 и 1.59,
на рис. 1.56 в левой нижней таблице мы видим записи из состава приходных
накладных с итогами по каждому товару, и дополнительно к этому рассчитываются итоги по иерархии для этих товаров. В правой нижней таблице
рассчитываются итоги только по иерархии, без итогов по каждому товару.
В верхней таблице для сравнения приведен состав приходных накладных
с итогами только по товарам, без иерархии.
78
Язык запросов «1С:Предприятия 8»
Рис. 1.56. Вывод итоговых данных по каждому товару с иерархией,
без иерархии и итогов только по иерархии товаров
Возможность построения иерархических итогов относится ко всем видам
иерархических данных, таких как справочники, планы видов характеристик,
планы видов расчета и т. п.
Расчет итогов по нескольким полям
Часто требуется рассчитать итоги результата запроса по значению нескольких
полей сразу. Для этого после ключевого слова ПО нужно перечислить через
запятую список полей – контрольных точек.
Например, нам нужно вывести все данные из состава приходных накладных,
в разрезе товаров и поставщиков, при этом рассчитать итоги по полям Товар
и Поставщик. Итоговые строки должны содержать для каждого товара общее
количество и сумму его поступления по каждому поставщику, а также показатели поступлений по каждому поставщику в целом. Это можно сделать
с помощью следующего запроса (листинг 1.60).
Глава 1. Механизм запросов
79
Листинг 1.60. Вывод всех записей из состава приходных накладных
с итоговыми данными для каждого товара по каждому поставщику
ВЫБРАТЬ
НакладнаяСостав.Ссылка.Поставщик КАК Поставщик,
НакладнаяСостав.Товар КАК Товар,
НакладнаяСостав.Количество КАК Количество,
НакладнаяСостав.Цена КАК Цена,
НакладнаяСостав.Сумма КАК Сумма
ИЗ
Документ.ПриходнаяНакладная.Состав КАК НакладнаяСостав
ИТОГИ
СУММА(Количество),
СУММА(Сумма)
ПО
Поставщик,
Товар
Обратите внимание, что в тексте запроса имена итоговых полей
и контрольных точек принято располагать на разных строках со смещением
относительно ключевых слов ИТОГИ и ПО.
В результате итоговые поля рассчитываются сначала для каждого товара
по каждому поставщику и затем отдельно для каждого поставщика по всем
товарам в целом (рис. 1.57).
Рис. 1.57. Вывод всех записей из состава приходных накладных
с итоговыми данными для каждого товара по каждому поставщику
80
Язык запросов «1С:Предприятия 8»
Расчет общих итогов
Помимо итогов по контрольным точкам, можно задать расчет общих итогов
результата запроса. Для этого после ключевого слова ПО необходимо указать
ключевое слово ОБЩИЕ.
Например, нам нужно вывести все данные из состава приходных накладных
для каждого поставщика и при этом рассчитать общие итоги и итоги по
полю Поставщик. Итоговые строки должны содержать для каждого поставщика общее количество и сумму поставленных им товаров, а также эти же
показатели по всем поступлениям в целом. Это можно сделать с помощью
следующего запроса (листинг 1.61).
Листинг 1.61. Вывод всех записей из состава приходных накладных с итоговыми
данными для каждого поставщика и в целом для всех поступлений
ВЫБРАТЬ
НакладнаяСостав.Ссылка.Поставщик КАК Поставщик,
НакладнаяСостав.Товар КАК Товар,
НакладнаяСостав.Количество КАК Количество,
НакладнаяСостав.Сумма КАК Сумма
ИЗ
Документ.ПриходнаяНакладная.Состав КАК НакладнаяСостав
ИТОГИ
СУММА(Количество),
СУММА(Сумма)
ПО
ОБЩИЕ,
Поставщик
В результате итоговые поля рассчитываются для каждого поставщика, и,
кроме того, в результат запроса добавляется самая верхняя итоговая строка,
содержащая итоги для всех поступлений в целом (рис. 1.58).
Рис. 1.58. Вывод всех записей из состава приходных накладных с итоговыми данными
для каждого поставщика и в целом для всех поступлений
Глава 1. Механизм запросов
81
Итоги часто используются совместно с группировкой. В этом случае для итогов
может быть не указан список агрегатных функций, так как он будет автоматически формироваться из агрегатных полей списка выборки (листинг 1.62).
Листинг 1.62. Совместное применение расчета итогов и группировки записей
ВЫБРАТЬ
НакладнаяСостав.Ссылка.Поставщик КАК Поставщик,
НакладнаяСостав.Товар КАК Товар,
СУММА(НакладнаяСостав.Количество) КАК Количество,
СУММА(НакладнаяСостав.Сумма) КАК Сумма
ИЗ
Документ.ПриходнаяНакладная.Состав КАК НакладнаяСостав
СГРУППИРОВАТЬ ПО
НакладнаяСостав.Ссылка.Поставщик,
НакладнаяСостав.Товар
ИТОГИ ПО
ОБЩИЕ,
Поставщик
В данном запросе после ключевого слова ИТОГИ не указаны итоговые поля,
но в результате запроса они рассчитываются, исходя из агрегатных функций
в списке полей выборки.
В целом результат запроса практически такой же, как при выполнении
предыдущего запроса (см. рис. 1.58), но количество записей в нем меньше,
так как строки с одинаковыми значениями полей группировки (Поставщик
и Товар) сворачиваются в одну запись (рис. 1.59).
Рис. 1.59. Совместное применение расчета итогов и группировки записей
82
Язык запросов «1С:Предприятия 8»
Примеры использования выражений
в списке полей выборки запроса
С помощью языка запросов можно решать широкий спектр задач благодаря
тому, что в тексте запросов можно использовать разнообразные выражения:
литералы, агрегатные функции, функции языка запросов, операции выбора
и т. п.
В данном разделе мы разберем несколько примеров использования выражений
в списке полей выборки запроса. Заметим сразу, что все эти примеры придуманы просто для демонстрации тех или иных возможностей языка запросов
и никакого прикладного значения не имеют.
Выражение языка запросов может содержать:
■■ Литералы типов: число, строка (в кавычках), булево (значения Истина
и Ложь), Null, Неопределено. Чтобы указать литерал типа Дата, можно
воспользоваться ключевым словом языка запросов ДАТАВРЕМЯ или передать дату через параметр запроса.
■■ Арифметические операции (+, -, /, *). Операция получения остатка %
в языке запросов не поддерживается.
■■ Операцию конкатенации строк (+). Операцию конкатенации нельзя
использовать для виртуальных полей.
■■ Встроенные функции языка запросов (ДЕНЬ, МЕСЯЦ, ГОД и т. д.).
■■ Агрегатные функции (СУММА, МИНИМУМ, МАКСИМУМ, СРЕДНЕЕ,
КОЛИЧЕСТВО).
■■ Операцию выбора ВЫБОР – позволяет получить одно из возможных
значений в соответствии с указанными условиями.
■■ Операцию приведения типов ВЫРАЗИТЬ – позволяет привести значение
составного типа к одному из составляющих это значение типов. А также
функцию ВЫРАЗИТЬ() используют для получения результатов нужной
длины и точности.
Рассмотрим несколько чисто демонстрационных задач, позволяющих показать применение выражений в списке выборки запросов.
Например, мы хотим вывести записи из иерархического справочника Товары.
При этом после поля Наименование в скобках мы хотим видеть Код товара.
Также для каждой записи справочника мы хотим видеть текстовое обозначение, является ли эта запись группой или нет («Это группа»/«Это элемент»).
Это можно сделать с помощью следующего запроса (листинг 1.63).
Глава 1. Механизм запросов
83
Листинг 1.63. Примеры использования выражений в списке полей выборки
ВЫБРАТЬ
Товары.Наименование + " (" + Товары.Код + ")" КАК Товар,
ВЫБОР
КОГДА Товары.ЭтоГруппа = ИСТИНА ТОГДА "Это группа"
ИНАЧЕ "Это элемент"
КОНЕЦ КАК ПризнакГруппы
ИЗ
Справочник.Товары КАК Товары
Результат выполнения запроса представлен на рис. 1.60.
Рис. 1.60. Примеры использования выражений в списке полей выборки
Для формирования поля выборки Товар в данном запросе используется
операция конкатенации (сложения) строк, в результате наименование и код
товара выводятся в одном текстовом поле выборки результата запроса.
Для формирования поля выборки ПризнакГруппы используется операция
выбора (ВЫБОР (КОГДА … ТОГДА) ИНАЧЕ … КОНЕЦ). В этой операции после
ключевого слова КОГДА записывается условие выбора, после ключевого
слова ТОГДА следует значение поля выборки в случае, если условие истинно.
В общем случае в операции выбора может указываться неограниченное
количество альтернативных одиночных выборов КОГДА … ТОГДА. Значение
выражения, указанного после слова ИНАЧЕ используется в качестве результата операции выбора в том случае, если ни одно из ранее указанных альтернативных условий выбора не было выполнено.
В операции выбора проверяется значение логического поля Товары.ЭтоГруппа,
и результат выбора выводится как строковой («Это группа»/«Это элемент»)
литерал.
Рассмотрим следующую задачу. Допустим, мы хотим вывести даты начала
и окончания поступлений товаров и узнать, сколько дней прошло между
ними. Это можно сделать с помощью следующего запроса (листинг 1.64).
84
Язык запросов «1С:Предприятия 8»
Листинг 1.64. Примеры использования выражений в списке полей выборки
ВЫБРАТЬ
МИНИМУМ(Накладная.Дата) КАК Начало,
МАКСИМУМ(Накладная.Дата) КАК Окончание,
РАЗНОСТЬДАТ(МИНИМУМ(Накладная.Дата), МАКСИМУМ(Накладная.Дата), ДЕНЬ) КАКПериод
ИЗ
Документ.ПриходнаяНакладная КАК Накладная
Результат выполнения запроса представлен на рис. 1.61.
Рис. 1.61. Примеры использования выражений в списке полей выборки
Для формирования полей выборки Начало и Окончание используются
агрегатные функции МИНИМУМ() и МАКСИМУМ(). В данном запросе группировка записей не используется, поэтому значения агрегатных функций
вычисляются для всех записей запроса, и результат запроса составляет одна
строка. В данном случае вычисляются минимальная и максимальные даты
приходных накладных.
Для
формирования поля выборки Период используется функция
РАЗНОСТЬДАТ() с параметром ДЕНЬ, которая вычисляет, сколько прошло дней
между началом и окончанием поступлений товаров.
Рассмотрим следующую задачу. Допустим, мы хотим вывести общее количество и сумму всех заказов товара и узнать среднюю сумму одного заказа. Это
можно сделать с помощью следующего запроса (листинг 1.65).
Листинг 1.65. Примеры использования выражений в списке полей выборки
ВЫБРАТЬ
КОЛИЧЕСТВО(*) КАК Количество,
СУММА(ЗаказТовара.СуммаЗаказа) КАК СуммаЗаказа,
ВЫРАЗИТЬ(СУММА(ЗаказТовара.СуммаЗаказа) / КОЛИЧЕСТВО(*) КАК ЧИСЛО(8,2)) КАК СреднийЗаказ
ИЗ
Документ.ЗаказТовара КАК ЗаказТовара
Результат выполнения запроса представлен на рис. 1.62.
Рис. 1.62. Примеры использования выражений в списке полей выборки
Глава 1. Механизм запросов
85
Для формирования поля выборки Количество используется агрегатная
функция КОЛИЧЕСТВО(). В данном запросе группировка записей не используется, поэтому значения агрегатных функций вычисляются для всех записей
запроса, и результат запроса составляет одна строка. В данном случае выводится общее количество всех заказов товара.
Для формирования поля выборки СуммаЗаказа используется агрегатная
функция СУММА(). В результате вычисляется общая сумма всех заказов.
Для формирования поля выборки СреднийЗаказ сумма всех заказов (значение
поля СуммаЗаказа) делится на общее количество заказов клиентов (значение
поля Количество). Затем к результату деления применяется функция ВЫРАЗИТЬ(), с помощью которой получается результат заданной длины и точности
(в данном случае результат деления округляется до двух десятых). Таким
образом, мы можем узнать среднюю сумму заказа.
Обратите внимание (см. листинг 1.64, 1.65), что в качестве параметров
функций должны указываться имена полей, а не их псевдонимы, и результат
предыдущих вычислений с помощью псевдонима нельзя использовать при
расчете других полей.
Рассмотрим следующую задачу. Допустим, мы хотим вывести движения
регистра накопления ОстаткиТоваров за определенный период. Причем дату
движений мы хотим видеть на начало текущей недели, а в качестве контрагента мы хотим вывести поставщиков из приходных накладных и покупателей
из расходных накладных. Это можно сделать с помощью следующего запроса
(листинг 1.66).
Листинг 1.66. Примеры использования выражений в списке полей выборки
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(ОстаткиТоваров.Период, НЕДЕЛЯ) КАК Период,
ВЫБОР
КОГДА (ОстаткиТоваров.Регистратор ССЫЛКА Документ.ПриходнаяНакладная)
ТОГДА ВЫРАЗИТЬ (ОстаткиТоваров.Регистратор КАК
Документ.ПриходнаяНакладная).Поставщик
КОГДА (ОстаткиТоваров.Регистратор ССЫЛКА Документ.РасходнаяНакладная)
ТОГДА ВЫРАЗИТЬ (ОстаткиТоваров.Регистратор КАК
Документ.РасходнаяНакладная).Покупатель
КОНЕЦ КАК Контрагент
ИЗ
РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров
ГДЕ
ОстаткиТоваров.Период МЕЖДУ &ДатаНачала И &ДатаОкончания
Результат выполнения запроса представлен на рис. 1.63.
86
Язык запросов «1С:Предприятия 8»
Рис. 1.63. Примеры использования выражений в списке полей выборки
В данном запросе накладывается условие на нахождение периода движений
регистра накопления ОстаткиТоваров в интервале между двумя датами.
В условии отбора используется оператор МЕЖДУ, который проверяет
результат вхождения значения в диапазон вместе с границами диапазона.
Для формирования поля выборки Период используется функция НАЧАЛОПЕРИОДА() с параметром НЕДЕЛЯ, позволяющая представить дату движений
регистра как начало недели, за которую производились эти движения.
Мы знаем, что регистраторами регистра накопления ОстаткиТоваров являются
документы ПриходнаяНакладная и РасходнаяНакладная, следовательно, поле
Регистратор регистра накопления является полем составного типа.
Для формирования поля выборки Контрагент используется операция выбора,
в условии которой при помощи оператора ССЫЛКА проверяется, ссылкой
на какой документ является поле Регистратор регистра накопления. Затем
с помощью функции ВЫРАЗИТЬ() значение поля составного типа приводится
к одному из составляющих это значение типов, но из приходных накладных
выводится поле Поставщик, а из расходных накладных – поле Покупатель.
Применение функции ВЫРАЗИТЬ() для приведения значений составного типа
к какому-либо определенному типу весьма желательно, так как позволяет
более оптимально выполнить запрос.
подробнее
Раздел «Ограничить получение данных через точку от полей составного
ссылочного типа», стр. 366.
Иногда функцию ВЫРАЗИТЬ() пытаются использовать не по назначению – для
преобразования типов, например строки, в число и т. п. Это неправильно.
Функция ВЫРАЗИТЬ() используется только для тех случаев, о которых было
рассказано выше (листинг 1.65, 1.66).
Глава 1. Механизм запросов
87
Выражения в языке запросов используются не только в списке полей
выборки, но и в условиях отбора, в предложении упорядочивания, при
описании итогов и т. д. Выражения в условиях отбора мы уже рассматривали
ранее (стр. 45), другие примеры использования выражений в языке запросов
будут рассмотрены нами позднее.
Подробнее
Документация «1С:Предприятие 8.3. Руководство разработчика», раздел 8.2
«Язык запросов», а также встроенная справка Справка ► Содержание справки
► 1С:Предприятие ► Встроенный язык ► Работа с запросами ► Синтаксис текста
запросов ► Использование выражений в языке запросов.
Примеры использования языка запросов
для получения данных из нескольких таблиц
Как использовать данные одного запроса внутри другого запроса
Иногда внутри одного запроса необходимо использовать данные другого
запроса. Например, требуется ограничить выборку значений в условии
отбора одного запроса данными другого запроса. Второй запрос по отношению к первому является вложенным, а первый запрос по отношению ко
второму является основным или внешним. Причем уровней вложенности
запросов друг в друга в общем случае может быть несколько.
Рассмотрим эту ситуацию на примере. Допустим, необходимо узнать,
в каких расходных накладных и каким покупателям продавались товары,
перечисленные в составе конкретной приходной накладной. Для этого нужно
выполнить следующий запрос (листинг 1.67).
Листинг 1.67. Использование вложенного запроса в условии отбора основного запроса
ВЫБРАТЬ
Расход.Ссылка КАК Документ,
Расход.Покупатель КАК Покупатель
ИЗ
Документ.РасходнаяНакладная КАК Расход
ГДЕ
Расход.Состав.Товар В
(
ВЫБРАТЬ
ПриходСостав.Товар КАК Товар
ИЗ
Документ.ПриходнаяНакладная.Состав КАК ПриходСостав
ГДЕ
ПриходСостав.Ссылка = &Документ
)
88
Язык запросов «1С:Предприятия 8»
В условии отбора (Расход.Состав.Товар В ()) значение поля Товар из табличной
части Состав расходной накладной проверяется на попадание в перечень
возможных значений. Для получения этого набора значений используется
вложенный запрос. Описание вложенного запроса начинается с предложения
ВЫБРАТЬ и ничем не отличается от обычного запроса.
Обратите внимание, что текст вложенного запроса принято располагать
со смещением относительно основного запроса для повышения наглядности
и структурированности запроса в целом.
Выполним в консоли запросов отдельно сам вложенный запрос. Для этого
необязательно писать заново текст запроса (листинг 1.68). Достаточно просто
выделить мышью текст вложенного запроса и нажать кнопку Выполнить.
Листинг 1.68. Описание вложенного запроса
ВЫБРАТЬ
ПриходСостав.Товар КАК Товар
ИЗ
Документ.ПриходнаяНакладная.Состав КАК ПриходСостав
ГДЕ
ПриходСостав.Ссылка = &Документ
Как мы видим, вложенный запрос является запросом к табличной части
документа, который мы рассматривали в разделе «Как получить данные из
табличной части некоторого документа» на стр. 52.
В результате будет получен перечень товаров из табличной части приходной
накладной, выбранной в качестве значения параметра Документ (см. рис.
1.64, верхняя таблица).
При выполнении внешнего запроса сначала выполняется вложенный запрос, и
результат его выполнения подставляется в условие отбора внешнего запроса.
В результате выполнения основного запроса (листинг 1.67) мы увидим
список расходных накладных, в которых продавались товары, перечисленные
в составе конкретной приходной накладной, указанной в параметре Документ (рис. 1.64).
Таким же образом вложенные запросы могут использоваться в условии
отбора, передаваемого в качестве параметра в виртуальную таблицу. В
этом случае при формировании виртуальной таблицы на основе исходной
(реальной) таблицы базы данных выборка значений из исходной таблицы
будет ограничена набором значений, полученным в результате выполнения
вложенного запроса.
Например, виртуальная таблица РегистрСведений.Цены.СрезПоследних возвращает срез последних записей регистра сведений Цены по каждому товару на
определенную дату. Эта таблица имеет параметры Период и Условие, которые
мы можем использовать в языке запросов.
Глава 1. Механизм запросов
89
Рис. 1.64. Использование вложенного запроса в условии отбора
Подробнее
Посмотреть состав таблиц запросов можно во встроенной справке Справка
► Содержание справки ► 1С:Предприятие ► Встроенный язык ► Работа с запросами ► Таблицы запросов.
В параметре Период мы можем задать дату, на которую должен быть выполнен
срез последних записей регистра сведений. В параметре Условие мы можем
задать условие отбора записей из исходной (реальной) таблицы регистра
сведений при формировании виртуальной таблицы.
90
Язык запросов «1С:Предприятия 8»
Допустим, нам нужно увидеть последние установленные цены товаров
на сегодняшнюю дату. Но не на все товары, а только на те, которые перечислены в составе конкретной расходной накладной. Для этого нужно выполнить
следующий запрос (листинг 1.69).
Листинг 1.69. Использование вложенного запроса в условии отбора,
передаваемого в параметр виртуальной таблицы
ВЫБРАТЬ
Цены.Период,
Цены.Товар,
Цены.Цена
ИЗ
РегистрСведений.Цены.СрезПоследних( , Товар В
(
ВЫБРАТЬ
РасходСостав.Товар КАК Товар
ИЗ
Документ.РасходнаяНакладная.Состав КАК РасходСостав
ГДЕ
РасходСостав.Ссылка = &Документ
)
) КАК Цены
УПОРЯДОЧИТЬ ПО
Период
В данном запросе нет ничего необычного, кроме того, что после имени
виртуальной таблицы (после ключевого слова ИЗ) в скобках мы можем задать
параметры для отбора записей регистра сведений в эту таблицу. Поскольку
мы хотим увидеть самые последние цены на товары из регистра сведений,
то параметр Период мы не указываем. В параметре Условие мы можем задать
произвольное условие на языке запросов, использующее любые поля регистра сведений Цены.
В этом условии (Товар В ()) значение поля регистра сведений Товар проверяется на попадание в перечень возможных значений. Для получения этого
набора значений используется вложенный запрос. Таким образом, при формировании виртуальной таблицы из исходной (реальной) таблицы регистра
сведений Цены будут отбираться записи об изменении цен только тех товаров,
которые перечислены в составе расходной накладной, выбранной в качестве
значения параметра Документ.
В результате выполнения запроса мы увидим последние установленные
цены на товары, перечисленные в составе конкретной расходной накладной
(рис. 1.65).
Глава 1. Механизм запросов
91
Рис. 1.65. Использование вложенного запроса в условии отбора,
передаваемого в параметр виртуальной таблицы
подробнее
В данном примере мы используем виртуальную таблицу СрезПоследних
регистра сведений Цены. Подробнее о назначении и использовании виртуальных таблиц регистра сведений будет рассказано в разделе «Получение
данных из периодических регистров сведений», стр. 212.
Заметим, что пример с использованием вложенного запроса в условии
отбора, передаваемого в параметр виртуальной таблицы, показан в чисто
демонстрационных целях, но на практике в таких ситуациях лучше использовать временные таблицы, о которых будет рассказано ниже.
подробнее
«Временные таблицы и пакетные запросы», стр. 117.
Как получить данные из разных таблиц для одного и того же поля
Часто в результате запроса требуется вывести данные из разных таблиц,
связав их по значению некоторого поля, то есть какие-то поля вывести из
одной таблицы, какие-то – из другой для одного и того же значения поля из
этих таблиц. Например, можно вывести для одного и того же товара количество его поступлений и продаж.
В этом случае используются несколько источников данных, которые перечисляются после ключевого слова ИЗ. В качестве источников запроса могут
выступать реальные и виртуальные таблицы, а также вложенные запросы,
но мы пока для упрощения примера будем рассматривать ситуацию, когда
в качестве источников запроса выступают две таблицы базы данных. Суть
примера от этого не изменится.
Исходные таблицы запроса обычно связываются (соединяются) между собой
по некоторому условию – условию связи. Поле, по которому производится
связь, обычно имеет ссылочный тип. При соединении данных из исходных
таблиц запроса для каждой записи из этих таблиц проверяется условие
равенства значений ссылочных полей этого типа. Условие связи источников
запроса задается в предложении ИЗ, после ключевого слова ПО. Например,
ПО Товары.Ссылка = Цены.Товар.
92
Язык запросов «1С:Предприятия 8»
В зависимости от того, должны ли записи каждой из таблиц удовлетворять
условию связи, таблицы соединяются внутренним, левым внешним, правым
внешним или полным внешним соединением. Ключевые слова, определяющие
тип соединения (например, ЛЕВОЕ СОЕДИНЕНИЕ), располагаются между
именами таблиц в предложении ИЗ.
Рассмотрим пример, который позволит понять сущность каждого типа соединения. Допустим, мы хотим вывести перечень товаров из справочника Товары
и при этом показать для каждого товара последнюю установленную на него
цену.
В нашей демонстрационной конфигурации существует регистр сведений
Цены, который хранит информацию об изменении цен товаров во времени.
Виртуальная таблица СрезПоследних этого регистра сведений позволяет получить информацию о последних ценах, установленных для каждого товара.
Поэтому для выполнения поставленной задачи нам нужно соединить
в запросе данные из таблиц Справочник.Товары и РегистрСведений.Цены.СрезПоследних.
подробнее
В данном примере для большей наглядности мы используем виртуальную
таблицу СрезПоследних регистра сведений Цены. Подробнее о назначении
и использовании виртуальных таблиц регистра сведений будет рассказано в разделе «Получение данных из периодических регистров сведений»,
стр. 212.
Регистр сведений Цены содержит поле Товар, ссылающееся на справочник
Товары. По полю этого типа и будет производиться связь таблиц в запросе, то
есть в условии связи для каждой записи из обеих таблиц будут сравниваться
значения уникального поля Ссылка из справочника и ссылочного поля Товар
из среза последних записей регистра сведений.
Исходные данные, используемые в примере о соединении таблиц в запросе,
представлены на рис. 1.66.
Рис. 1.66. Исходные записи справочника «Товары»
и среза последних записей регистра сведений «Цены»
Глава 1. Механизм запросов
93
Однако, как мы видим на рисунке, не для всех товаров из справочника Товары
существует соответствующая им цена в срезе последних записей регистра
сведений Цены. В частности, для товара Пинетки не найдено цены в регистре
сведений, так как товар – новый, и цена на него еще не устанавливалась.
Также для записей справочника Обувь, Продукты и Детская обувь не найдено
соответствий в регистре сведений, так как эти записи являются группами
товаров, и цены на них не имеют смысла.
Теперь посмотрим, что же произойдет при связывании данных двух этих
таблиц разными видами соединений.
Внутреннее соединение
При внутреннем соединении таблиц в результат запроса попадут только те
записи из таблиц-источников, которые удовлетворяют заданному условию
связи (после ключевого слова ПО).
Предположим, нам необходимо вывести из справочника Товары те товары, на
которые установлена цена в регистре сведений Цены, и показать актуальную
цену для каждого товара. Товары, на которые не установлена цена, выводить
не нужно, а также не нужно выводить последние установленные цены из регистра сведений для товаров, которых не существует в справочнике товаров.
Для этого нам нужно соединить в запросе данные из таблиц Справочник.Товары и РегистрСведений.Цены.СрезПоследних внутренним соединением. Из справочника Товары выведем поля Код, Наименование и Производитель, а из среза последних записей регистра сведений Цены – поле Цена
(листинг 1.70).
Листинг 1.70. Внутреннее соединение данных справочника «Товары»
и среза последних записей регистра сведений «Цены»
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Производитель,
Цены.Цена
ИЗ
Справочник.Товары КАК Товары
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.Цены.СрезПоследних КАК Цены
ПО Товары.Ссылка = Цены.Товар
Результат запроса представлен на рис. 1.67.
94
Язык запросов «1С:Предприятия 8»
Рис. 1.67. Внутреннее соединение данных справочника «Товары»
и среза последних записей регистра сведений «Цены»
ПРИМЕЧАНИЕ
При соединениях источников запроса в заголовке таблицы, содержащей
результат запроса, в консоли запросов выводится имя первой таблицы из
списка соединений.
Мы видим, что в результат запроса попали только те записи из обеих таблиц,
для которых было выполнено условие связи (Товары.Ссылка = Цены.Товар),
то есть для каждой записи из справочника Товары была найдена соответствующая запись из среза последних записей регистра сведений Цены, в которой
значение ссылочного поля Товар было равно значению поля Ссылка. Поэтому
в результате запроса содержатся 6 записей, а в исходной таблице справочника – 10 (см. рис. 1.68), так как для четырех записей справочника не было
найдено соответствий в регистре сведений.
В обратную сторону условие связи также должно выполняться, то есть если
в справочнике товаров не существует товара, для которого установлена цена
в регистре сведений, то такая запись также не попадет в результат запроса.
Но обычно разработчики программно поддерживают целостность базы
данных, и такие ситуации встречаются редко.
ПРИМЕЧАНИЕ
Ключевое слово ВНУТРЕННЕЕ в тексте запроса можно не указывать, но
оно повышает наглядность и читаемость текста запроса.
При соединениях чаще всего применяется условие на равенство, но в языке
запросов есть возможность использовать все операции сравнения и логические операции И, ИЛИ, НЕ.
Если в таблице будет найдено несколько записей, удовлетворяющих условию
соединения, то в результат запроса будут включены все эти записи. Например,
если в запросе использовать соединение таблицы справочника Товары
и реальной таблицы регистра сведений Цены (листинг 1.71), то в результате мы увидим несколько установленных цен для одного и того же товара
(рис. 1.68).
Глава 1. Механизм запросов
95
Листинг 1.71. Внутреннее соединение данных справочника «Товары»
и регистра сведений «Цены»
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Производитель,
Цены.Цена,
Цены.Период
ИЗ
Справочник.Товары КАК Товары
СОЕДИНЕНИЕ РегистрСведений.Цены КАК Цены
ПО Товары.Ссылка = Цены.Товар
УПОРЯДОЧИТЬ ПО
Товары.Наименование
Рис. 1.68. Внутреннее соединение данных справочника «Товары»
и регистра сведений «Цены»
Левое внешнее соединение
Если связать таблицы регистров накопления левым соединением, то
в результат запроса попадут записи из обеих таблиц, удовлетворяющие
условию связи, и, кроме того, записи из первой таблицы, расположенной
слева от ключевого слова СОЕДИНЕНИЕ, для которых не найдено соответствия
во второй таблице.
Предположим, нам необходимо вывести все записи из справочника Товары
и показать актуальную цену товаров, на которые установлена цена в регистре сведений Цены. При этом последние установленные цены из регистра
сведений для товаров, которых не существует в справочнике товаров,
выводить не нужно. Для этого нам нужно соединить в запросе данные из
таблиц Справочник.Товары и РегистрСведений.Цены.СрезПоследних левым
внешним соединением (листинг 1.72).
96
Язык запросов «1С:Предприятия 8»
Листинг 1.72. Левое внешнее соединение данных справочника «Товары»
и среза последних записей регистра сведений «Цены»
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Производитель,
Цены.Цена
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ РегистрСведений.Цены.СрезПоследних КАК Цены
ПО Товары.Ссылка = Цены.Товар
Результат запроса представлен на рис. 1.69.
Рис. 1.69. Левое внешнее соединение данных справочника «Товары»
и среза последних записей регистра сведений «Цены»
Мы видим, что в результат запроса попали все 10 записей из Товары (см. рис.
1.66) независимо от того, была найдена соответствующая им запись из среза
последних записей регистра сведений Цены или нет. Так произошло потому,
что таблица Справочник.Товары расположена слева от слова СОЕДИНЕНИЕ.
Строки результата запроса, для которых не найдено соответствующих условию
записей из второго источника, будут содержать значение NULL в полях,
формируемых на основании записей из этого источника. Обратите внимание,
что значения NULL не являются нулем или пустой строкой. Значения данного
типа обозначают неуказанные (отсутствующие) значения или значения, не
имеющие смысла. В данном случае в поле Цена для товаров Обувь, Продукты,
Детская обувь и Пинетки будет находиться значение NULL.
Из второй таблицы в результат запроса попадут только записи, удовлетворяющие условию связи, то есть если в справочнике товаров не существует
товара, для которого установлена цена в регистре сведений, то такая запись
не попадет в результат запроса.
ПРИМЕЧАНИЕ
Ключевое слово ВНЕШНЕЕ в тексте запроса можно не указывать, но оно
повышает наглядность и читаемость текста запроса.
Глава 1. Механизм запросов
97
Таким образом, в случае, когда таблица справочника связывается левым
внешним соединением с таблицей по полю, имеющему тип ссылки на этот
справочник, вы можете быть уверены, что не потеряете нужных вам данных.
Поэтому в отчетах используется в основном именно левое соединение
таблиц.
Правое внешнее соединение
При правом соединении таблиц ситуация зеркально противоположная.
То есть из правой таблицы в результат запроса попадут все записи, а из
левой – только те, которые удовлетворяют условию связи.
Предположим, нам необходимо вывести из справочника Товары те товары, на
которые установлена цена в регистре сведений Цены, и показать актуальную
цену для каждого товара. При этом товары, на которые не установлена
цена, выводить не нужно, а из регистра сведений нужно вывести срез всех
последних записей. Для этого нам нужно соединить в запросе данные из
таблиц Справочник.Товары и РегистрСведений.Цены.СрезПоследних правым
внешним соединением (листинг 1.73).
Листинг 1.73. Правое внешнее соединение данных справочника «Товары»
и среза последних записей регистра сведений «Цены»
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Производитель,
Цены.Цена
ИЗ
Справочник.Товары КАК Товары
ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ РегистрСведений.Цены.СрезПоследних КАК Цены
ПО Товары.Ссылка = Цены.Товар
Результат запроса представлен на рис. 1.70.
Рис. 1.70. Правое внешнее соединение данных справочника «Товары» и среза
последних записей регистра сведений «Цены»
Мы видим, что в результат запроса попали все 6 записей из таблицы среза
последних записей регистра сведений Цены (см. рис. 1.66), расположенной
справа от слова СОЕДИНЕНИЕ. В нашем примере это не так очевидно: так, все
98
Язык запросов «1С:Предприятия 8»
товары, для которых установлены цены, присутствуют в справочнике Товары.
Но в общем случае если в справочнике товаров не существует товара, для
которого установлена цена в регистре сведений, то такая запись все равно
попадет в результат запроса.
Строки результата запроса, для которых не найдено соответствующих
условию записей из первого источника, будут содержать значение NULL
в полях, формируемых на основании записей из этого источника.
Из первой таблицы в результат запроса попадут только записи, удовлетворяющие условию связи. То есть если в срезе последних записей регистра
сведений Цены не найдена цена для какого-то товара, то такая запись из
справочника Товары не попадет в результат запроса.
Правое соединение полностью аналогично левому, но при этом
таблицы меняются местами в тексте запроса. Именно так и происходит
в «1С:Предприятии».
Таким образом, если мы поменяем местами наши исходные таблицы и свяжем
их правым соединением (первый вариант запроса), то мы получим результат,
полностью аналогичный предыдущему примеру с левым соединением
(см. рис. 1.69), листинг 1.74.
Листинг 1.74. Правое внешнее соединение данных справочника «Товары»
и среза последних записей регистра сведений «Цены»
// Первый вариант
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Производитель,
Цены.Цена
ИЗ
РегистрСведений.Цены.СрезПоследних КАК Цены
ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ Справочник.Товары КАК Товары
ПО Товары.Ссылка = Цены.Товар
// Второй вариант
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Производитель,
Цены.Цена
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ РегистрСведений.Цены.СрезПоследних КАК Цены
ПО Товары.Ссылка = Цены.Товар
При этом порядок следования полей таблиц в условии связи значения
не имеет, важно лишь, какая таблица расположена слева, а какая – справа
от слова СОЕДИНЕНИЕ.
Глава 1. Механизм запросов
99
Полное соединение
При полном соединении таблиц в результат запроса будут включены записи
из обеих исходных таблиц, которые соответствуют указанному условию.
Кроме того, в результат запроса будут включены также еще и те записи из
обоих источников, для которых не найдено соответствий.
Предположим, нам необходимо вывести все записи из справочника Товары
и срез всех последних записей из регистра сведений Цены. При этом нужно
показать актуальную цену для тех товаров, на которые она устанавливалась.
Для этого нам нужно соединить в запросе данные из таблиц Справочник.Товары и РегистрСведений.Цены.СрезПоследних полным внешним
соединением (листинг 1.75).
Листинг 1.75. Полное внешнее соединение данных справочника «Товары»
и среза последних записей регистра сведений «Цены»
ВЫБРАТЬ
Товары.Код,
Товары.Наименование,
Товары.Производитель,
Цены.Цена
ИЗ
Справочник.Товары КАК Товары
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ РегистрСведений.Цены.СрезПоследних КАК Цены
ПО Товары.Ссылка = Цены.Товар
Результат запроса представлен на рис. 1.71.
Рис. 1.71. Полное внешнее соединение данных справочника «Товары»
и среза последних записей регистра сведений «Цены»
Мы видим, что в результат запроса попали все записи из исходных таблиц
запроса. Но в нашем частном случае мы получили результат, полностью
аналогичный примеру с левым соединением (см. рис. 1.69), так как все
товары, для которых установлены цены в регистре сведений, присутствуют
в справочнике Товары. Для тех товаров, на которые устанавливалась цена,
она найдена в срезе последних записей регистра сведений и отражена
в результате запроса.
100
Язык запросов «1С:Предприятия 8»
Строки результата запроса, для которых не найдено соответствующих
условию записей из какого-либо источника, будут содержать значение NULL
в полях, формируемых на основании записей из этого источника.
Следует иметь в виду, что при работе в клиент-серверном варианте, когда
в качестве СУБД используется PostgreSQL, производительность выполнения
запросов с конструкцией ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ значительно
снижается. В особенности это касается случаев, когда в запросе встречаются
две и более такие конструкции. Поэтому в общем случае не рекомендуется
использовать полное внешнее соединение в запросах. И в тех случаях, где
это возможно, рекомендуется переписать текст исходного запроса без использования этой конструкции.
Выше мы рассматривали различные виды соединения источников запроса на
примере двух таблиц базы данных. В общем случае в запросе может содержаться не только одно соединение (двух источников), но и несколько соединений нескольких источников сразу. Об этом рассказывается в следующем
примере.
Кроме того, в соединении источников могут участвовать и вложенные
запросы. Однако соединения с вложенными запросами крайне нежелательны,
так как в большинстве случаев могут привести к неоптимальному выполнению запроса.
Подробнее
Раздел «Не использовать соединения с вложенными запросами и с виртуальными таблицами», стр. 360.
Как получить данные из разных таблиц,
связанных несколькими соединениями
Рассмотрим теперь более сложную задачу, когда в результате запроса используется несколько соединений данных из нескольких таблиц, и проанализируем
подробно каждый этап такого соединения. Предположим, нам необходимо
вывести перечень товаров из справочника Товары и при этом показать остаток
каждого товара и количество его продаж.
В нашей демонстрационной конфигурации существует регистр накопления
ОстаткиТоваров, который накапливает информацию о приходе и расходе
товаров. Виртуальная таблица Остатки этого регистра накопления позволяет
получить информацию об остатках каждого товара. Также в конфигурации
существует регистр накопления Продажи, который накапливает информацию
о продажах товаров. Виртуальная таблица Обороты этого регистра накопления
позволяет получить информацию о количестве и сумме оборотов (продаж)
каждого товара.
Глава 1. Механизм запросов
101
Поэтому для выполнения поставленной задачи нам нужно соединить
в запросе данные из таблиц Справочник.Товары, РегистрНакопления.ОстаткиТоваров.Остатки и РегистрНакопления.Продажи.Обороты.
подробнее
В данном примере для большей наглядности мы используем виртуальную таблицу Остатки регистра накопления ОстаткиТоваров и виртуальную
таблицу Обороты регистра накопления Продажи. Подробнее о назначении
и использовании виртуальных таблиц регистра накопления будет рассказано в разделах «Получение остатков», стр. 234, «Получение оборотов»,
стр. 246.
Регистры накопления ОстаткиТоваров и Продажи содержат поле Товар,
ссылающееся на справочник Товары. По полю этого типа и будет производиться связь таблиц в запросе, то есть в условии связи для каждой записи
из исходных таблиц будут сравниваться значения уникального поля Ссылка
из справочника и ссылочного поля Товар из виртуальных таблиц Остатки или
Обороты регистров накопления.
Исходные данные, используемые в примере о соединении таблиц в запросе,
представлены на рис. 1.72.
Рис. 1.72. Исходные записи справочника «Товары» и виртуальных таблиц
регистров накопления «ОстаткиТоваров» и «Продажи»
102
Язык запросов «1С:Предприятия 8»
На рис. 1.72 мы видим, что в справочнике Товары отражаются семь записей,
которые не являются группой, так как на таблицу справочника наложено соответствующее условие. Также мы видим, что записи всех трех таблиц, используемых в примере, не полностью соответствуют друг другу по полю Товар.
Так, товар Пинетки ни разу не продавался и не поступал (не имеет остатка),
т. к. он новый. Товар Сметана имеет остаток, но он ни разу не продавался.
Товары Сапоги и Масло полностью проданы, т. е. они не имеют остатка. Эти
особенности будут отражены в результате запроса, когда мы будем связывать
данные всех таблиц между собой разными видами соединений.
Сначала свяжем данные всех трех таблиц левыми соединениями и выведем
из справочника Товары поле Товар, из виртуальной таблицы Остатки регистра
накопления ОстаткиТоваров (таблицы остатков) – поле КоличествоОстаток,
из виртуальной таблицы Обороты регистра накопления Продажи (таблицы
оборотов) – поле КоличествоОборот (листинг 1.76).
Листинг 1.76. Левое соединение данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
ВЫБРАТЬ
Товары.Наименование,
ОстаткиТоваров.КоличествоОстаток,
Продажи.КоличествоОборот
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки КАК ОстаткиТоваров
ПО Товары.Ссылка = ОстаткиТоваров.Товар
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты КАК Продажи
ПО Товары.Ссылка = Продажи.Товар
ГДЕ
Товары.ЭтоГруппа = ЛОЖЬ
Результат запроса представлен на рис. 1.73.
Рис. 1.73. Левое соединение данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
Мы видим, что в результат запроса попали все записи (7 товаров) из справочника Товары, как и должно быть при левом соединении. Для каждого товара
были найдены соответствующие записи в таблицах остатков и оборотов
товаров и подставлены в результат запроса.
Глава 1. Механизм запросов
103
Если проанализировать текст запроса, представленный в листинге 1.76,
то понятно, что сначала выполняется левое соединение справочника Товары
с виртуальной таблицей остатков. Результат этого соединения мы видим на
рис. 1.74, слева. Для каждого товара из справочника ищется соответствующая
запись в таблице остатков, и найденный остаток выводится для конкретного
товара. Товары, у которых нет остатка (Сапоги, Масло, Пинетки), также попадают в результат соединения.
Рис. 1.74. Левое соединение данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
На следующем этапе результат этого первого соединения (см. рис. 1.74,
левая таблица) связывается левым соединением с виртуальной таблицей
оборотов. Для каждого товара из результата первого соединения ищется соответствующая запись в таблице оборотов, и найденное количество продаж
выводится для конкретного товара. Товары, которые ни разу не продавались
(Сметана, Пинетки), также попадают в результат запроса (см. рис. 1.74, правая
таблица).
Теперь посмотрим, как изменится результат запроса при различных комбинациях типов соединений исходных таблиц запроса. Оставим без изменения
тип первого (по порядку следования в запросе) соединения справочника
Товары с виртуальной таблицей остатков. А тип второго соединения изменим
на внутреннее соединение (листинг 1.77).
Листинг 1.77. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
ВЫБРАТЬ
Товары.Наименование,
ОстаткиТоваров.КоличествоОстаток,
Продажи.КоличествоОборот
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки
КАК ОстаткиТоваров
ПО Товары.Ссылка = ОстаткиТоваров.Товар
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты КАК Продажи
ПО Товары.Ссылка = Продажи.Товар
ГДЕ
Товары.ЭтоГруппа = ЛОЖЬ
104
Язык запросов «1С:Предприятия 8»
При выполнении запроса сначала все происходит так же, как и в предыдущем
случае. Таблица справочника Товары связывается с виртуальной таблицей
остатков левым соединением. Результат этого соединения мы видим на рис.
1.75, слева. Для каждого товара из справочника ищется соответствующая
запись в таблице остатков, и найденный остаток выводится для конкретного
товара. Товары, у которых нет остатка (Сапоги, Масло, Пинетки), также попадают в результат соединения.
Рис. 1.75. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
На следующем этапе результат этого первого соединения (см. рис. 1.75, левая
таблица) связывается внутренним соединением с виртуальной таблицей
оборотов. Поскольку тип соединения – внутренний, в результат запроса
попадают только те товары из результата соединения, для которых найдена
соответствующая запись в таблице оборотов. Товары, которые ни разу не
продавались (Сметана, Пинетки) из результата первого соединения, не попадают в результат запроса (см. рис. 1.75, правая таблица).
Теперь свяжем внутренним соединением справочник Товары с виртуальной
таблицей остатков. Тип второго соединения (по порядку следования
в запросе) установим как левое соединение (листинг 1.78).
Листинг 1.78. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
ВЫБРАТЬ
Товары.Наименование,
ОстаткиТоваров.КоличествоОстаток,
Продажи.КоличествоОборот
ИЗ
Справочник.Товары КАК Товары
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки
КАК ОстаткиТоваров
ПО Товары.Ссылка = ОстаткиТоваров.Товар
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты КАК Продажи
ПО Товары.Ссылка = Продажи.Товар
ГДЕ
Товары.ЭтоГруппа = ЛОЖЬ
Глава 1. Механизм запросов
105
При выполнении запроса сначала происходит внутреннее соединение справочника Товары с виртуальной таблицей остатков. Результат этого соединения
мы видим на рис. 1.76, слева. Поскольку тип соединения – внутренний,
в результат попадают только четыре из семи товаров из справочника,
у которых есть остаток.
Рис. 1.76. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
На следующем этапе результат этого первого соединения (см. рис. 1.76, левая
таблица) связывается левым соединением с виртуальной таблицей оборотов.
Для каждого товара из результата первого соединения ищется соответствующая запись в таблице оборотов, и найденное количество продаж выводится
для конкретного товара. Товары, которые ни разу не продавались (Сметана)
из результата первого соединения, также попадают в результат запроса (см.
рис. 1.76, правая таблица).
Теперь свяжем все три исходные таблицы запроса внутренними соединениями (листинг 1.79).
Листинг 1.79. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
ВЫБРАТЬ
Товары.Наименование,
ОстаткиТоваров.КоличествоОстаток,
Продажи.КоличествоОборот
ИЗ
Справочник.Товары КАК Товары
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки
КАК ОстаткиТоваров
ПО Товары.Ссылка = ОстаткиТоваров.Товар
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты КАК Продажи
ПО Товары.Ссылка = Продажи.Товар
ГДЕ
Товары.ЭтоГруппа = ЛОЖЬ
При выполнении запроса сначала все происходит так же, как и в предыдущем
случае. Таблица справочника Товары связывается с виртуальной таблицей
остатков внутренним соединением. Результат этого соединения мы видим на
рис. 1.77, слева. Поскольку тип соединения – внутренний, в результат попадают только четыре из семи товаров из справочника, у которых есть остаток.
106
Язык запросов «1С:Предприятия 8»
Рис. 1.77. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
На следующем этапе результат этого первого соединения (см. рис. 1.77, левая
таблица) связывается внутренним соединением с виртуальной таблицей
оборотов. Поскольку тип соединения – внутренний, в результат запроса
попадают только те товары из результата соединения, для которых найдена
соответствующая запись в таблице оборотов. Товары, которые ни разу не
продавались (Сметана) из результата первого соединения, не попадают
в результат запроса (см. рис. 1.77, правая таблица).
По такому же принципу вы можете самостоятельно поэкспериментировать,
что произойдет с результатом запроса, если связать таблицы комбинациями
других, не рассмотренных нами соединений.
Мы же обратим внимание на другую тонкость. Как видно из рассмотренных
выше случаев (листинги 1.76–1.79), три исходные таблицы запроса Справочник.Товары, РегистрНакопления.ОстаткиТоваров.Остатки и РегистрНакопления.Продажи.Обороты последовательно связывались разными видами
соединений. Порядок расположения синтаксических конструкций в предложении ИЗ был следующий: .
Но можно расположить соединения не последовательно, а как бы вложить их
друг в друга и изменить условие связи таблиц, тогда результат запроса будет
другим. Например, свяжем левым соединением таблицы остатков и оборотов
и затем результат этого соединения свяжем левым соединением с таблицей
справочника товаров (листинг 1.80).
Листинг 1.80. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
ВЫБРАТЬ
Товары.Наименование,
ОстаткиТоваров.КоличествоОстаток,
Продажи.КоличествоОборот
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки КАК ОстаткиТоваров
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты КАК Продажи
ПО ОстаткиТоваров.Товар = Продажи.Товар
ПО Товары.Ссылка = ОстаткиТоваров.Товар
ГДЕ
Товары.ЭтоГруппа = ЛОЖЬ
Глава 1. Механизм запросов
107
Из текста запроса мы видим, что второе (по порядку следования в запросе)
соединение как бы вложено в первое. Но на самом деле оно выполняется
первым как соединение с наибольшим уровнемвложенности. Этот уровень
также подчеркнут смещением в тексте запроса относительно первого соединения. При этом первое (по порядку следования в запросе) условие связи
относится ко второму (вложенному) соединению, а второе условие связи –
к первому соединению. Чтобы не запутаться, разберем эту ситуацию на
нашем конкретном примере.
При выполнении запроса (см. листинг 1.80) сначала таблица остатков связывается с таблицей оборотов левым соединением. Результат этого соединения
мы видим на рис. 1.78, слева. Поскольку тип соединения – левый, в результат
попадают все 4 товара, у которых есть остаток. При этом у одного из товаров
(Сметана) нет продаж, а товары, которые проданы без остатка (Сапоги, Масло),
в результат соединения не попадают.
Рис. 1.78. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
На следующем этапе таблица справочника товаров связывается левым
соединением с результатом первого соединения (см. рис. 1.78, левая таблица).
Для каждого товара из справочника ищется соответствующая запись в результате первого соединения, и найденные остатки и обороты выводятся для
конкретного товара. Товары, у которых нет остатка (Сапоги, Масло, Пинетки)
из справочника Товары, также попадают в результат запроса (см. рис. 1.78,
правая таблица).
Теперь изменим тип первого (по порядку следования в запросе) соединения
на внутреннее соединение, тип второго соединения оставим без изменений
(листинг 1.81).
Листинг 1.81. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
ВЫБРАТЬ
Товары.Наименование,
ОстаткиТоваров.КоличествоОстаток,
Продажи.КоличествоОборот
108
Язык запросов «1С:Предприятия 8»
ИЗ
Справочник.Товары КАК Товары
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки
КАК ОстаткиТоваров
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты КАК Продажи
ПО ОстаткиТоваров.Товар = Продажи.Товар
ПО Товары.Ссылка = ОстаткиТоваров.Товар
ГДЕ
Товары.ЭтоГруппа = ЛОЖЬ
При выполнении запроса на первом этапе все происходит так же, как и
в предыдущем случае (см. объяснение над рис. 1.78). Результат этого соединения мы видим на рис. 1.79, слева.
Рис. 1.79. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
На следующем этапе таблица справочника товаров связывается внутренним
соединением с результатом первого соединения (см. рис. 1.79, левая таблица).
Поскольку тип соединения – внутренний, то в результат запроса (см. рис.
1.79, правая таблица) попадают только четыре из семи товаров из справочника, которые присутствуют в результате первого соединения.
Теперь установим тип первого (по порядку следования в запросе) соединения
как левое соединение, а тип второго соединения – как внутреннее соединение
(листинг 1.82).
Листинг 1.82. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
ВЫБРАТЬ
Товары.Наименование,
ОстаткиТоваров.КоличествоОстаток,
Продажи.КоличествоОборот
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки КАК ОстаткиТоваров
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты
КАК Продажи
ПО ОстаткиТоваров.Товар = Продажи.Товар
ПО Товары.Ссылка = ОстаткиТоваров.Товар
ГДЕ
Товары.ЭтоГруппа = ЛОЖЬ
Глава 1. Механизм запросов
109
При выполнении запроса сначала таблица остатков связывается с таблицей
оборотов внутренним соединением. Результат этого соединения мы видим на
рис. 1.80, слева. Поскольку тип соединения – внутренний, в результат попадают только 3 товара, у которых есть и остаток, и оборот. При этом товар
Сметана, у которого есть остаток, но нет продаж, а также товары, которые
проданы без остатка (Сапоги, Масло), в результат соединения не попадают.
Рис. 1.80. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
На следующем этапе таблица справочника товаров связывается левым соединением с результатом первого соединения (см. рис. 1.80, левая таблица). Для
каждого товара из справочника ищется соответствующая запись в результате
первого соединения, и найденные остатки и обороты выводятся для конкретного товара. Товары, которые ни разу не продавались (Сметана, Пинетки),
и товары, у которых нет остатка (Сапоги, Масло, Пинетки) из справочника
Товары, также попадают в результат запроса (см. рис. 1.80, правая таблица).
Теперь изменим тип первого (по порядку следования в запросе) соединения
на внутреннее соединение, а тип второго соединения оставим без изменений
(листинг 1.83).
Листинг 1.83. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
ВЫБРАТЬ
Товары.Наименование,
ОстаткиТоваров.КоличествоОстаток,
Продажи.КоличествоОборот
ИЗ
Справочник.Товары КАК Товары
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки
КАК ОстаткиТоваров
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты
КАК Продажи
ПО ОстаткиТоваров.Товар = Продажи.Товар
ПО Товары.Ссылка = ОстаткиТоваров.Товар
ГДЕ
Товары.ЭтоГруппа = ЛОЖЬ
110
Язык запросов «1С:Предприятия 8»
При выполнении запроса на первом этапе все происходит так же, как
и в предыдущем случае (см. объяснение над рис. 1.80). Результат этого
соединения мы видим на рис. 1.81, слева.
Рис. 1.81. Комбинация типов соединений данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
На следующем этапе таблица справочника товаров связывается внутренним
соединением с результатом первого соединения (см. рис. 1.81, левая таблица).
Поскольку тип соединения – внутренний, то в результат запроса (см. рис.
1.81, правая таблица) попадают только три из семи товаров из справочника,
которые присутствуют в результате первого соединения.
Как видно из рассмотренных выше случаев (листинги 1.80–1.83), порядок
расположения синтаксических конструкций в предложении ИЗ был следующий:
.
Таким образом, от порядка соединения исходных таблиц запроса и их местоположения при описании соединения напрямую зависит результат запроса.
Поэтому в этом вопросе нужно проявлять повышенное внимание.
Как получить данные из таблицы,
на которую ссылается поле другой таблицы
В языке запросов «1С:Предприятия» существует очень удобная возможность – обращаться не только к полям исходных таблиц запроса, перечисленным в предложении ИЗ, но и к полям таблицы, на которую ссылается поле
исходной таблицы запроса.
В начале главы, на стр. 14, мы рассматривали, как хранятся данные таблиц,
имеющих ссылочные поля на другие таблицы в информационной базе
«1С:Предприятия» (рис. 1.82).
Глава 1. Механизм запросов
111
Рис. 1.82. Связь данных документа и справочника
по ссылочному полю в информационной базе
В приведенном примере в качестве исходной таблицы выступает документ
Событие, который имеет ссылочное поле Клиент. Это поле имеет тип ссылки
на справочник Клиенты, а значениями поля Клиент в таблице документа являются ссылки на конкретные элементы справочника. В этом случае, обращаясь
к полю Клиент в документе, мы можем получить из справочника Клиенты
любые данные о клиенте, на которого ссылается данное поле.
Теперь посмотрим, как это реализуется с помощью языка запросов. Допустим, нам нужно вывести список документов Событие и при этом для каждого
клиента отобразить его адрес и телефон. Это можно сделать с помощью
следующего запроса (листинг 1.84).
Листинг 1.84. Вывод полей из таблицы,
на которую ссылается поле исходной таблицы запроса
ВЫБРАТЬ
Событие.Дата КАК Дата,
Событие.ВидСобытия,
Событие.Клиент,
Событие.Клиент.Адрес КАК Адрес,
Событие.Клиент.Телефон КАК Телефон
ИЗ
Документ.Событие КАК Событие
УПОРЯДОЧИТЬ ПО
Дата
112
Язык запросов «1С:Предприятия 8»
В списке выборки запроса перечислены поля Дата, ВидСобытия и Клиент
исходной таблицы запроса – документа Событие, а также поля Адрес и
Телефон таблицы справочника Клиенты, на которую ссылается поле документа
Клиент. Имена полей таблицы справочника пишутся через точку, например,
Клиент.Адрес.
Обращение к полям через точку называется операцией разыменования
ссылочного поля. Причем в общем случае в цепочке разыменования может
быть перечислено через точку несколько полей, например, Номенклатура.Поставщик.Страна. В этом примере поле Номенклатура исходной таблицы
ссылается на другую таблицу (справочник Поставщики), имеющую, в свою
очередь, поле Поставщик, ссылающееся на третью таблицу (справочник
Страны), из которой запросом получается поле Страна.
В результате выполнения запроса (см. листинг 1.84) для каждой записи
таблицы документа Событие по значению поля Клиент будет найдена соответствующая запись в таблице справочника Клиенты, в которой значение
поля Ссылка будет равно значению поля Клиент в документе. Затем из этой
записи справочника будут получены значения полей Адрес и Телефон аналогично тому, как выбираются значения полей Дата и ВидСобытия из исходной
таблицы запроса (рис. 1.83).
Рис. 1.83. Вывод полей из таблицы,
на которую ссылается поле исходной таблицы запроса
Значения самого поля Клиент также на самом деле получаются из таблицы
справочника в соответствии с его свойством Основное представление.
подробнее
О поле Представление рассказано в разделе «Как получить текстовое представление ссылочного поля», стр. 38.
Возможность обращения к полям через точку позволяет значительно упростить написание запросов. Рекомендуется всегда пользоваться разыменованием полей там, где это возможно, чтобы не усложнять запросы лишними
конструкциями. Конечно, количество ссылочных полей, перечисленных
в цепочке разыменования, должно быть ограничено пределами разумного,
чтобы не снижать эффективность исполнения запроса.
Ниже мы увидим, что на самом деле применение такой конструкции приводит
к неявному соединению с дополнительными таблицами для получения
Глава 1. Механизм запросов
113
значений полей через точку. Поэтому при разыменовании ссылочных полей
составного типа (например, поле Регистратор регистра накопления и т. п.)
следует предпринимать дополнительные усилия по оптимизации запроса, так
как в этом случае запрос может работать неоптимально.
Подробнее
Раздел «Ограничить получение данных через точку от полей составного
ссылочного типа», стр. 366.
Рассмотрим, что же на самом деле происходит при получении значений
полей через точку в языке запросов. При разыменовании ссылочных полей
платформа добавляет таблицу, на которую ссылается поле исходной таблицы
запроса, в список источников запроса (в предложение ИЗ) и выполняет соединение этих таблиц по полям ссылочного типа. То есть в условии связи (после
ключевого слова ПО) для каждой записи из обеих таблиц проверяется условие
равенства значения ссылочного поля исходной таблицы (в нашем случае поля
Клиент документа Событие) и уникальной ссылки, идентифицирующей запись
той таблицы, на которую указывает это поле (в нашем случае поля Ссылка
справочника Клиенты).
Таким образом, предыдущий запрос можно представить в следующих вариантах (листинг 1.85).
Листинг 1.85. Вывод полей из таблицы,
на которую ссылается поле исходной таблицы запроса
// Разыменование полей ссылочного типа
ВЫБРАТЬ
Событие.Дата КАК Дата,
Событие.ВидСобытия,
Событие.Клиент,
Событие.Клиент.Адрес КАК Адрес,
Событие.Клиент.Телефон КАК Телефон
ИЗ
Документ.Событие КАК Событие
УПОРЯДОЧИТЬ ПО
Дата
// Соединение таблиц
ВЫБРАТЬ
Событие.Дата КАК Дата,
Событие.ВидСобытия,
Событие.Клиент,
Клиенты.Адрес,
Клиенты.Телефон
ИЗ
Документ.Событие КАК Событие
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Клиенты КАК Клиенты
ПО Событие.Клиент = Клиенты.Ссылка
УПОРЯДОЧИТЬ ПО
Дата
114
Язык запросов «1С:Предприятия 8»
Во втором варианте запроса после ключевого слова ИЗ перечислены источники запроса – таблица Документ.Событие, содержащая ссылочное поле
Клиент, и таблица Справочник.Клиенты, на которую ссылается это поле.
Но таблицы не просто перечислены через запятую, а связаны между собой
левым соединением (между именами таблиц находятся ключевые слова ЛЕВОЕ
СОЕДИНЕНИЕ). Тип соединения таблиц определяет, должны ли записи каждой
из таблиц удовлетворять условию связи.
подробнее
Типы соединений источников запроса подробно рассмотрены в разделе
«Как получить данные из разных таблиц для одного и того же поля»,
стр. 91.
В условии связи источников запроса сравниваются значения ссылочных
полей из обеих таблиц (Событие.Клиент = Клиенты.Ссылка). Как мы знаем,
при левом соединении таблиц в результат запроса попадут записи из обеих
таблиц, которые удовлетворяют условию связи, и, кроме того, записи из
первой таблицы, расположенной слева от ключевого слова СОЕДИНЕНИЕ, для
которых не найдено соответствия во второй таблице.
Результат запроса (см. рис. 1.83), использующего соединение таблиц, будет
аналогичен выполнению предыдущего запроса, использующего разыменование полей ссылочного типа.
Как получить данные из разных таблиц,
не связывая, а дополняя их
При выборке данных с помощью запросов бывает необходимо дополнить
данные, получившиеся в результате выполнения одного запроса, данными
другого запроса. Например, можно дополнить информацию из приходных
накладных со структурой (Контрагент, Товар, Количество, Цена) данными из
расходных накладных с такой же структурой.
Для этого в языке запросов существует возможность объединения нескольких
запросов. При этом записи, полученные с помощью каждого из объединяемых
запросов, собираются в один результат запроса. При объединении каждый
запрос получает данные независимо, то есть у каждого из запросов – свое
описание выбираемых полей (ВЫБРАТЬ), источников запроса (ИЗ), условий
отбора (ГДЕ), полей группировки (СГРУППИРОВАТЬ ПО). Затем данные,
получаемые в результате каждого запроса, объединяются, и уже над этим
объединением выполняются такие операции, как упорядочивание результатов
(УПОРЯДОЧИТЬ ПО) и расчет итогов (ИТОГИ ПО).
Глава 1. Механизм запросов
115
Для объединения запросов используется предложение ОБЪЕДИНИТЬ, которое
располагается в секции Объединение запросов текста запроса. Все секции
запроса приведены на рис. 1.13, стр. 25.
Предположим, нам нужно объединить вместе информацию из документов
Заказ товара и информацию из документов Расходная накладная. При этом
нужно вывести суммарное количество заказанных и проданных товаров по
каждому клиенту. Также мы хотим видеть общий итог по результату запроса
в целом и отдельно по каждому клиенту.
Для решения поставленной задачи нужно получить данные из таблицы
Документ.ЗаказТовара, сгруппировать их по полям этой таблицы Клиент
и Товар, затем получить данные из таблицы Документ.РасходнаяНакладная,
сгруппировать их по полям этой таблицы Покупатель и Товар и объединить
полученную информацию. Затем рассчитать общие итоги и итоги по полю
Клиент для результата объединения запросов (листинг 1.86).
Листинг 1.86. Объединение данных о заказах товаров клиентами
и данных о продажах товаров этим клиентам
ВЫБРАТЬ
Заказ.Ссылка.Клиент КАК Клиент,
Заказ.Товар КАК Товар,
СУММА(Заказ.Количество) КАК Заказано,
СУММА(0) КАК Продано
ИЗ
Документ.ЗаказТовара.Состав КАК Заказ
СГРУППИРОВАТЬ ПО
Заказ.Ссылка.Клиент,
Заказ.Товар
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Накладная.Ссылка.Покупатель,
Накладная.Товар,
СУММА(0),
СУММА(Накладная.Количество)
ИЗ
Документ.РасходнаяНакладная.Состав КАК Накладная
СГРУППИРОВАТЬ ПО
Накладная.Ссылка.Покупатель,
Накладная.Товар
ИТОГИ ПО
ОБЩИЕ,
Клиент
В тексте запроса сначала описывается первый запрос, получающий информацию из документа ЗаказТовара, затем следуют ключевые слова ОБЪЕДИНИТЬ ВСЕ, после которых следует описание присоединяемого запроса,
116
Язык запросов «1С:Предприятия 8»
получающего информацию из документа РасходнаяНакладная. После этого
описывается необходимость расчета итогов для результата объединения
запросов.
Названия полей результата запроса описываются в списке полей выборки
первого из объединяемых запросов. Поля выборки второго запроса сопоставляются с полями результата в соответствии с порядком их следования
в списке полей выборки.
Поэтому объединяемые запросы должны иметь одинаковое количество
полей в списке полей выборки. В случае, если поля выборки объединяемых
запросов имеют разный тип, поля результата запроса будут иметь составной
тип. Но в нашем случае поле выборки результата объединения Клиент имеет
тип СправочникСсылка.Клиенты, так как поле Клиент документа ЗаказТовара
и поле Покупатель документа РасходнаяНакладная из объединяемых запросов
также принадлежат к этому типу.
Результат выполнения запроса представлен на рис. 1.84.
Рис. 1.84. Объединение данных о заказах товаров клиентами
и данных о продажах товаров этим клиентам
В общем случае могут объединяться результаты выполнения сразу нескольких
запросов.
По умолчанию при объединении запросов полностью одинаковые строки
в результате запроса, сформированные разными запросами, заменяются
одной. Если требуется, чтобы были оставлены разные строки, необходимо
указать ключевое слово ВСЕ.
Глава 1. Механизм запросов
117
В общем случае при объединении в запросе результатов нескольких запросов
следует использовать конструкцию ОБЪЕДИНИТЬ ВСЕ, а не ОБЪЕДИНИТЬ,
поскольку во втором варианте при объединении запросов полностью одинаковые строки заменяются одной, на что затрачивается дополнительное время,
даже в случаях, когда одинаковых строк в запросах заведомо быть не может.
Временные таблицы и пакетные запросы
Довольно часто бывают ситуации, когда в качестве источника запроса
необходимо использовать не таблицы базы данных, а данные, полученные
в результате выполнения другого запроса. Например, источником запроса
могут служить данные приходных накладных, сгруппированные по товарам
или отобранные по дате документа.
Запрос, служащий источником данных, является вложенным, а запрос,
который эти данные использует, является основным или внешним.
Рассмотрим эту ситуацию на примере. Допустим, нам нужно вывести данные
обо всех товарах и их производителях с соблюдением иерархии справочника
Товары. Для каждого товара из справочника мы хотим видеть дату его поступления и поставщика из приходных накладных.
Казалось бы, в этом нет ничего особенно сложного. Свяжем левым соединением таблицы справочника Товары и документа ПриходнаяНакладная
по ссылкам товаров и выведем требуемые поля из обеих таблиц. При этом
упорядочим результат запроса по наименованиям товаров в порядке иерархии
справочника (листинг 1.87).
Листинг 1.87. Вывод всех товаров в порядке иерархии справочника «Товары»
с данными об их поступлении
ВЫБРАТЬ
Товары.Наименование,
Товары.Производитель,
Поступление.Ссылка.Дата,
Поступление.Ссылка.Поставщик
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПриходнаяНакладная.Состав КАК Поступление
ПО Товары.Ссылка = Поступление.Товар
УПОРЯДОЧИТЬ ПО
Товары.Наименование ИЕРАРХИЯ
Результат выполнения запроса представлен на рис. 1.85.
На реальной базе, конечно, список товаров может быть очень большим. Предположим, мы хотим получить тот же список, но с данными о поступлениях
товаров только за прошедший месяц.
118
Язык запросов «1С:Предприятия 8»
Рис. 1.85. Вывод всех товаров в порядке иерархии справочника «Товары»
с данными об их поступлении
Для этого наложим условие на поле Дата приходной накладной так, чтобы
выбирались данные из приходных накладных только за ноябрь (при этом,
конечно же, за другие годы данных в нашей демонстрационной конфигурации нет), листинг 1.88.
Листинг 1.88. Вывод всех товаров в порядке иерархии справочника «Товары»
с данными об их поступлении за ноябрь
ВЫБРАТЬ
Товары.Наименование,
Товары.Производитель,
Поступление.Ссылка.Дата,
Поступление.Ссылка.Поставщик
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПриходнаяНакладная.Состав КАК Поступление
ПО Товары.Ссылка = Поступление.Товар
ГДЕ
МЕСЯЦ(Поступление.Ссылка.Дата) = 11
УПОРЯДОЧИТЬ ПО
Товары.Наименование ИЕРАРХИЯ
Мы видим, что результат запроса – несколько не такой, как мы ожидали
(рис. 1.86).
Рис. 1.86. Вывод товаров, поступавших в ноябре, из справочника «Товары»
Глава 1. Механизм запросов
119
В результате выполнения запроса отражены только те товары, которые поступали в ноябре, а также «пропала» иерархия справочника товаров, так как
в результате запроса нет данных для построения иерархии. Так произошло
потому, что при накладывании условия отбора на поле правой таблицы при
левом соединении тип соединения становится внутренним.
В этом и в некоторых других случаях выходом из ситуации является использование вложенного запроса в качестве источника внешнего запроса. Поэтому
предыдущий запрос можно переписать следующим образом (листинг 1.89).
Листинг 1.89. Вывод всех товаров в порядке иерархии справочника «Товары»
с данными об их поступлении за ноябрь
ВЫБРАТЬ
Товары.Наименование,
Товары.Производитель,
ПоступлениеТоваров.Дата,
ПоступлениеТоваров.Поставщик
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ
(ВЫБРАТЬ
Поступление.Товар,
Поступление.Ссылка.Дата,
Поступление.Ссылка.Поставщик
ИЗ
Документ.ПриходнаяНакладная.Состав КАК Поступление
ГДЕ
МЕСЯЦ(Поступление.Ссылка.Дата) = 11
) КАК ПоступлениеТоваров
ПО Товары.Ссылка = ПоступлениеТоваров.Товар
УПОРЯДОЧИТЬ ПО
Товары.Наименование ИЕРАРХИЯ
В тексте основного запроса вложенный запрос выделен жирным шрифтом
и смещен относительно внешнего запроса. При выполнении основного
запроса сначала выполняется вложенный запрос, в котором получаются
данные о поступлении товаров за ноябрь. Затем справочник товаров связывается левым соединением с результатом вложенного запроса по ссылкам
товаров, и в итоге мы получаем требуемый результат (рис. 1.87).
Рис. 1.87. Вывод всех товаров в порядке иерархии справочника «Товары»
с данными об их поступлении за ноябрь
120
Язык запросов «1С:Предприятия 8»
Однако мы видим, что текст запроса (см. листинг 1.89) стал довольно
громоздким и сложным для восприятия. С этой точки зрения лучше поместить
результат вложенного запроса во временную таблицу и затем использовать ее
как источник нашего основного запроса.
Временная таблица не существует в базе данных. Это просто некоторая
область в памяти компьютера, которая создается на ограниченное время,
как, например, это происходит при создании какой-либо переменной встроенного языка. Эта область создается и заполняется данными при выполнении
запроса, содержащего ключевое слово ПОМЕСТИТЬ, после которого следует
произвольное имя временной таблицы.
Подробнее
Документация «1С:Предприятие 8.3. Руководство разработчика», раздел 8.2
«Язык запросов», а также встроенная справка Справка ► Содержание справки ► 1С:Предприятие ► Встроенный язык ► Работа с запросами ► Выполнение
и работа с запросами во встроенном языке ► Работа с временными таблицами.
Дополним текст вложенного запроса из предыдущего запроса предложением
ПОМЕСТИТЬ, которое располагается сразу после списка полей выборки
(листинг 1.90).
Листинг 1.90. Помещение данных о поступлениях товаров
за ноябрь во временную таблицу
ВЫБРАТЬ
Поступление.Товар,
Поступление.Ссылка.Дата КАК Дата,
Поступление.Ссылка.Поставщик
ПОМЕСТИТЬ ПоступлениеТоваров
ИЗ
Документ.ПриходнаяНакладная.Состав КАК Поступление
ГДЕ
МЕСЯЦ(Поступление.Ссылка.Дата) = 11
Заметим, что для быстрой и эффективной выборки данных из временной
таблицы желательно проиндексировать эту таблицу по полям, которые будут
затем участвовать в условии отбора или в условии соединения. Делается это
с помощью предложения ИНДЕКСИРОВАТЬ ПО.
подробнее
«Эффективное использование индексов. Пример 5», стр. 353.
Чтобы увидеть данные, помещенные во временную таблицу, в консоли
запросов нужно нажать кнопку Выполнить запрос с временными таблицами
рядом с кнопкой Выполнить (рис. 1.88).
Глава 1. Механизм запросов
121
Рис. 1.88. Данные временной таблицы «ПоступлениеТоваров»
Итак, в результате выполнения запроса (листинг 1.90) мы поместили данные
о поступлениях товаров за ноябрь во временную таблицу с именем ПоступлениеТоваров.
Теперь эту таблицу можно использовать в качестве источника других
запросов точно так же, как и таблицу базы данных. Но сделать это можно либо
из встроенного языка с помощью менеджера временных таблиц (этот вариант
мы рассмотрим позже на стр. 180), либо с помощью пакетного запроса. Этот
вариант мы и рассмотрим сейчас.
Пакетный запрос содержит последовательность запросов, разделенных
символом «;» (точка с запятой). При выполнении пакетного запроса входящие
в него запросы выполняются друг за другом. При этом если при выполнении
одного из запросов была создана временная таблица, то следующие за ним
запросы при их выполнении могут использовать данные этой временной
таблицы.
Необходимо учитывать, что временные таблицы будут существовать до окончания исполнения всего пакета запроса или до исполнения в пакете запроса,
уничтожающего данную временную таблицу с помощью конструкции
УНИЧТОЖИТЬ.
122
Язык запросов «1С:Предприятия 8»
Подробнее
Документация «1С:Предприятие 8.3. Руководство разработчика», раздел 8.2
«Язык запросов», а также встроенная справка Справка ► Содержание справки
► 1С:Предприятие ► Встроенный язык ► Работа с запросами ► Выполнение и
работа с запросами во встроенном языке ► Работа с пакетными запросами.
Таким образом, разобьем запрос, содержащий вложенный запрос (см. листинг
1.89), на два запроса и объединим их в один пакетный запрос (листинг 1.91).
Листинг 1.91. Вывод всех товаров в порядке иерархии справочника «Товары»
с данными об их поступлении за ноябрь
ВЫБРАТЬ
Поступление.Товар,
Поступление.Ссылка.Дата КАК Дата,
Поступление.Ссылка.Поставщик
ПОМЕСТИТЬ ПоступлениеТоваров
ИЗ
Документ.ПриходнаяНакладная.Состав КАК Поступление
ГДЕ
МЕСЯЦ(Поступление.Ссылка.Дата) = 11
;
ВЫБРАТЬ
Товары.Наименование,
Товары.Производитель,
ПоступлениеТоваров.Дата,
ПоступлениеТоваров.Поставщик
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ ПоступлениеТоваров КАК ПоступлениеТоваров
ПО Товары.Ссылка = ПоступлениеТоваров.Товар
УПОРЯДОЧИТЬ ПО
Товары.Наименование ИЕРАРХИЯ
В первом запросе мы помещаем данные о поступлении товаров за ноябрь во
временную таблицу с именем ПоступлениеТоваров. Затем следует символ
«;» (точка с запятой), который указывает на то, что это – пакетный запрос.
В следующем запросе справочник товаров связывается левым соединением
с временной таблицей по ссылкам товаров, и в итоге мы получаем результат,
аналогичный результату при выполнении запроса, содержащего вложенный
запрос (см. рис. 1.87).
Таким образом, можно сделать вывод, что текст запроса в случае использования временных таблиц становится более понятным и осмысленным, чем
при использовании вместо них вложенных запросов.
Также использование временных таблиц вместо вложенных запросов почти
всегда делает запрос более оптимальным. С точки зрения эффективности
исполнения запросов крайне не рекомендуется использовать соединения
Глава 1. Механизм запросов
123
с вложенными запросами, так как в этом случае СУБД может выбрать
неоптимальный план запроса, что на больших объемах данных приводит
к временным задержкам и другим неприятностям при выполнении таких
запросов.
подробнее
Раздел «Не использовать соединения с вложенными запросами и с виртуальными таблицами» стр. 360.
Пример, показывающий использование вложенного запроса для ограничения
значений выборки в условии отбора (раздел «Как использовать данные
одного запроса внутри другого запроса», стр. 87), также можно переделать
с использованием временной таблицы вместо вложенного запроса.
В заключение покажем, как можно переписать запрос, выполняющий
несколько соединений нескольких таблиц (раздел «Как получить данные из
разных таблиц, связанных несколькими соединениями», стр. 100) с использованием временной таблицы (листинг 1.92).
Листинг 1.92. Два варианта запроса, выполняющего несколько соединений
данных нескольких таблиц
// Первый вариант с двумя левыми соединениями
ВЫБРАТЬ
Товары.Наименование,
ОстаткиТоваров.КоличествоОстаток,
Продажи.КоличествоОборот
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки
КАК ОстаткиТоваров
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты КАК Продажи
ПО ОстаткиТоваров.Товар = Продажи.Товар
ПО Товары.Ссылка = ОстаткиТоваров.Товар
ГДЕ
Товары.ЭтоГруппа = ЛОЖЬ
// Второй вариант с временной таблицей
ВЫБРАТЬ
ОстаткиТоваров.Товар,
ОстаткиТоваров.КоличествоОстаток,
Продажи.КоличествоОборот
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
РегистрНакопления.ОстаткиТоваров.Остатки КАК ОстаткиТоваров
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты КАК Продажи
ПО ОстаткиТоваров.Товар = Продажи.Товар
;
ВЫБРАТЬ
Товары.Наименование,
ВременнаяТаблица.КоличествоОстаток,
124
ИЗ
Язык запросов «1С:Предприятия 8»
ВременнаяТаблица.КоличествоОборот
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ВременнаяТаблица
ПО ВременнаяТаблица.Товар = Товары.Ссылка
ГДЕ
Товары.ЭтоГруппа = ЛОЖЬ
Второй вариант, использующий временную таблицу, более понятен,
по крайней мере полезен для изучения. Так как если выполнить сначала
только первую часть запроса (помещающую данные во временную таблицу),
а затем – весь запрос целиком, то можно поэтапно проследить, какие данные
получаются в результате каждого соединения (рис. 1.89).
Рис. 1.89. Левое соединение данных справочника «Товары»
и данных таблиц остатков и оборотов товаров
Глава 2.
Работа с запросами
во встроенном языке
Конструктор запроса
В первой главе мы познакомились с азами языка запросов, научились создавать синтаксически правильные тексты запросов и выполнять их в консоли
запросов. Для решения небольших демонстрационных задач мы писали текст
запросов вручную, в специальном окне консоли запросов, и затем сразу же
могли увидеть результат выполнения запроса. На начальном этапе это было
очень полезно, чтобы набраться опыта при создании простых запросов
и закрепить понимание пройденного материала.
Но в дальнейшем для решения реальных задач написание запросов вручную
может быть довольно утомительным. Кроме того, помимо знания всех
синтаксических конструкций и правил составления запросов, это потребует
от разработчика знания имен таблиц языка запросов, их полей и параметров,
что потребует постоянного обращения к синтакс-помощнику или встроенной
справке.
Поэтому в платформе «1С:Предприятие» существуют инструменты для
облегчения труда разработчика при написании запросов – конструктор
запроса и конструктор запроса с обработкой результата.
С помощью конструктора запроса можно визуально сконструировать запрос,
то есть с помощью мыши выбрать и перетащить нужные таблицы, поля, установить связи между ними, условия отбора и т. д. После нажатия кнопки ОК
конструктор запроса создаст синтаксически правильный текст запроса.
126
Язык запросов «1С:Предприятия 8»
С помощью конструктора запроса с обработкой результата можно, помимо
самого текста запроса, создать готовый фрагмент кода для получения
данных с помощью запроса и вывода их, например, в табличный документ.
Этот вопрос мы рассмотрим позже (стр. 164), а пока остановимся на общих
принципах работы с конструктором запроса.
Чтобы открыть конструктор запроса, нужно перейти в модуль формы,
обработки и т. п., вызвать правой кнопкой мыши контекстное меню и затем
выбрать пункт Конструктор запроса или Конструктор запроса с обработкой
результата (рис. 2.1).
Рис. 2.1. Вызов конструктора запроса
Или же можно выполнить команду главного меню конфигуратора Текст ►
Конструктор запроса/Конструктор запроса с обработкой результата.
В случае, когда вызывается конструктор запроса без обработки результата,
желательно предварительно в тексте модуля сделать заготовку для создания
запроса и вызвать контекстное меню, поместив курсор между кавычек
(листинг 2.1).
Листинг 2.1. Заготовка для создания запроса
Запрос = Новый Запрос;
Запрос.Текст = "";
Также конструктор запроса может быть вызван из схемы компоновки данных
при создании набора данных, источником которого является запрос к инфор-
Глава 2. Работа с запросами во встроенном языке
127
мационной базе, при настройке свойств динамического списка, источником
которого служит произвольный запрос, и т. д.
Попробуем с помощью конструктора запроса создать текст запросов для
нескольких простых задач, используемых в первой главе. Мы не будем здесь
объяснять, как и почему применена та или иная конструкция языка запросов.
На этих примерах мы просто покажем, как создать аналогичный текст запроса
не вручную, а с помощью конструктора.
Создание простого запроса
Для начала создадим с помощью конструктора запроса запрос для отображения в порядке иерархии всех товаров из справочника Товары. Этот пример
мы рассматривали в разделе «Как получить записи иерархической таблицы
и расположить их в порядке иерархии» на стр. 57 (листинг 2.2).
Листинг 2.2. Вывод записей справочника «Товары»,
расположенных в порядке иерархии
ВЫБРАТЬ
Товары.Код,
Товары.Наименование КАК Наименование,
Товары.Родитель,
Товары.ЭтоГруппа
ИЗ
Справочник.Товары КАК Товары
УПОРЯДОЧИТЬ ПО
Наименование ИЕРАРХИЯ
Теперь наша задача – получить аналогичный запрос с помощью конструктора
запроса.
Итак, откроем нашу демонстрационную конфигурацию Язык запросов
в режиме Конфигуратор и откроем модуль формы обработки РаботаСЗапросами. Затем создадим в тексте модуля заготовку, показанную в листинге
2.1, поместим курсор между кавычек и вызовем из контекстного меню пункт
Конструктор запроса. Подтвердим, что мы хотим создать новый запрос. После
этого откроется конструктор запроса (рис. 2.2).
Сначала мы попадаем на основную закладку конструктора Таблицы и поля.
На этой закладке в списке База данных можно выбрать реальные и виртуальные таблицы в качестве источников запроса. Раскроем группу справочников, выберем таблицу Товары и перенесем ее в список Таблицы. В этом
списке можно выбрать нужные поля для отображения в запросе. Раскроем
структуру выбранной таблицы и перенесем в список Поля поля Код, Наименование, Родитель, ЭтоГруппа (рис. 2.2).
128
Язык запросов «1С:Предприятия 8»
Рис. 2.2. Окно конструктора запроса
ПРИМЕЧАНИЕ
Выделенные элементы можно перенести из одного списка в другой перетаскиванием мышью или двойным щелчком на них. Либо можно использовать кнопки >, >>,
