ЗАПРОСЫ

Система 1Средприятие 8.0 поддерживает два способа доступа к данным, хранящимся в базе данных:

· Объектный (для чтения и записи),

· Табличный (для чтения).

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

Запрос - это специальный объект встроенного языка системы "1Средприятие 8.0", служащий для формирования и выполнения обращений к информационной базе 1С:Предприятия 8.0, с требованием выбрать необходимые данные из базы данных. В процессе выполнения запроса также возможна дополнительная обработка результата запроса: сортировка, группировка, вычисление итогов по группам и др.

Работу механизма запросов в 1Средприятии 8.0 обеспечивают следующие объекты встроенного языка:

· Запрос (Указание текста запроса и параметров, его выполнение);

· РезультатЗапроса (Выборка/выгрузка данных, полученных запросом);

· ВыборкаИзРезультатаЗапроса (Обход полученного результата и получение данных);

· КоллекцияКолонокРезультатаЗапроса (Унифицированная коллекция всех полей результата);

· КолонкаРезультатаЗапроса (Получение параметров любого поля запроса).

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

В выполняемый запрос могут быть переданы внешние параметры, например, для вычисления условий отбора данных. Это может быть сделано выполнением метода объекта ЗапросстановитьПараметр(<Имя>, <Значение>). <Имя> - имя добавляемого параметра, <Значение> - передаваемое значение произвольного типа. При выполнении запроса, данные параметры будут использованы механизмом запросов в тех местах, где в тексте упоминаются имена этих параметров.

Объект РезультатЗапроса представляет собой массив данных в памяти, выбранный из базы данных, при помощи метода объекта Запросыполнить() и соответствующий структуре текста запроса. Это объект предназначен для хранения и дальнейшей обработки полученных данных.

Схему работы с запросом можно представить в виде следующих пошаговых действий:

· Создание объекта Запрос;

· Составление текста запроса, передача текста объекту Запрос;

· Установка параметров запроса;

· Выполнение запроса;

· Выгрузка результата в таблицу, или получение выборки по результату запроса;

· Дальнейшая обработка выборки данных результата.

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

<Текст запроса>
    <Описание запроса>
    <Объединение запросов>
    <Упорядочивание результатов>
    <АВТОУПОРЯДОЧИВАНИЕ>
    <Описание итогов>

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

В качестве источников данных языка запросов выступают таблицы запросов. Данные таблицы подразделяются на два основных класса: реальные и виртуальные.

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

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

· таблица справочника и документа;

· основная таблица регистра сведений;

· основная таблица регистра накопления;

· основная таблица регистра бухгалтерии;

· основная таблица регистра расчета;

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

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

· таблица получения среза последних (наиболее поздних) записей периодического регистра сведений;

· таблица получения среза первых (наиболее ранних) записей регистра сведений;

· таблицы получения остатков и/или оборотов регистра накоплений, бухгалтерии;

Для каждой таблицы определяется имя, которое используется в запросах для идентификации таблицы. Имя таблицы может быть задано на английском или русском языках. Например, так: РегистрНакоплениястаткиМатериалов.Остатки (<Параметры>). Данное имя исходной виртуальной таблицы отличается от описания реальной таблицы РегистрНакоплениястаткиМатериалов наличием слова "Остатки", определяющим, что необходимо использовать не таблицу движений регистра "ОстаткиМатериалов", а остатки по регистру на некоторый момент времени, определяемый параметрами виртуальной таблицы.

Отдельный подкласс таблиц образуют так называемые объектные таблицы. В качестве объектной таблицы обязательно выступает реальная таблица запросов. Смысловое отличие объектных таблиц от прочих следует из названия - объектные таблицы храненят данные состояния объектов системы 1C:Предприятие 8.0, таких как справочники, документы, счета и т. п. Каждой объектной таблице соответствует тип объектов системы 1C:Предприятие 8.0. Например, объектам типа Справочникслуги соответствует одна таблица, объектам типа Справочник.Клиенты - другая. Каждая отдельная запись объектной таблицы хранит состояние отдельного объекта соответствующего типа. В соответствии с этим у каждой объектной таблицы определено поле типа Ссылка на текущую запись. Кроме того, для объектных таблиц определено поле Представление, предназначенное для автоматически формируемого пользовательского представления объекта из содержимого полей записи.

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

В качестве примера объектных таблиц можно привести следующие таблицы:

· таблицы справочников;

· таблицы документов;

· таблицы плана счетов;

· таблицы плана видов характеристик;

· таблицы плана видов расчета;

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

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

Язык запросов.

Ключевое слово ВЫБРАТЬ.

Текст запроса всегда начинается с обязательного ключевого слова ВЫБРАТЬ. Данное ключевое слово позволяет указать список полей для выборки, либо, используя символ звездочка (*), выбрать все имеющиеся поля из таблиц-источников данных.

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

ВЫБРАТЬ 1, "Это строка", ПОДСТРОКА("Это строка", 5, 6), &Параметр1

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

С помощью ключевого слова РАЗЛИЧНЫЕ можно указать, что в результат не должны попадать повторяющиеся строки.

Например:

ВЫБРАТЬ РАЗЛИЧНЫЕ Материалыатериал КАК Материал ИЗ
    Документ.ПоступлениеМатериалов.Материалы КАК Материалы

Ключевое слово РАЗРЕШЕННЫЕ означает, что запрос выберет только те записи, на которые у текущего пользователя есть права, т.е. в соответствии с ограничениями, установленными на уровне записей и полей баз данных. Если данное слово не указать, то в случае, когда запрос выберет записи, на которые у пользователя нет прав, будет вызвано исключение. Данное ключевое слово может присутствовать только в предложении ВЫБРАТЬ верхнего уровня и распространяется на весь запрос, включая вложенные запросы.

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

Например:

ВЫБРАТЬ ПЕРВЫЕ 3 Наименование
ИЗ
    Справочникслуги

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

Например:

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

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

Предложение ИЗ. В предложении ИЗ <Список источников> указываются источники данных - таблицы информационной базы, содержимое которых обрабатывается в запросе. Описание источников может быть опущено только в том случае, если они полностью определены в списке полей выборки.

Например:

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

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

Например:

ВЫБРАТЬ
    Наименование, Стоимость
ИЗ
    Справочникслуги
ГДЕ
    (Стоимость>=100) и (Стоимость<=2000)

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

В выражении шаблона может быть использован служебный символ "%", обозначающий любую последовательность символов. Можно использовать другие символы:

*       "_" - один произвольный символ

*       "[…]" - любой одиночный символ из перечисленных внутри квадратных скобок

*       "[^…]" - любой одиночный символ, кроме тех, которые перечислены, следом за знаком "^"

Например:

ВЫБРАТЬ Наименование
ИЗ
    Справочникслуги
ГДЕ
    Наименование ПОДОБНО "%тех%"

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

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

Например:

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

Например:

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

СГРУППИРОВАТЬ ПО
    АктОбОказанииУслуг.Услуги.(Услуга)

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

Например:

ВЫБРАТЬ
    Услуга,
    СУММА(Количество) КАК Продано
ИЗ
    ДокументктОбОказанииУслуг.Услуги
СГРУППИРОВАТЬ ПО
    Услуга
ИМЕЮЩИЕ СУММА(Количество)>10

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

ВЫБРАТЬ Услуга, СУММА(Сумма) КАК Сумма
ИЗ
    РегистрНакопленияслуги
СГРУППИРОВАТЬ ПО
    Услуга
ДЛЯ ИЗМЕНЕНИЯ
    РегистрНакопления.Услуги

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

Например:

ВЫБРАТЬ
    Услугааименование КАК Услуга,
    Количество, Сумма
ИЗ
    Документ.АктОбОказанииУслуг.Услуги
УПОРЯДОЧИТЬ ПО
    Услуга Возр,
    Сумма Возр

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

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

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

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

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

Если АВТОУПОРЯДОЧИВАНИЕ используется автономно, т.е. в тексте запроса отсутствуют предложения: УПОРЯДОЧИТЬ ПО, ИТОГИ, СГРУППИРОВАТЬ ПО. В этом случае результат запроса будет упорядочен по полям сортировки по умолчанию для таблиц, выбранных в качестве источников данных.

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

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

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

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

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

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

Например:

Общие итоги:

ВЫБРАТЬ
    ОстаткиМатериаловатериал КАК Материал,
    ОстаткиМатериалов.Сумма КАК Сумма
ИЗ
    РегистрНакопления.ОстаткиМатериалов КАК ОстаткиМатериалов
ИТОГИ
    СУММА(Сумма)
ПО
    ОБЩИЕ

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

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

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

ВЫБРАТЬ
    ОстаткиМатериаловатериал КАК Материал,
    ОстаткиМатериалов.Сумма КАК Сумма
ИЗ
    РегистрНакопления.ОстаткиМатериалов КАК ОстаткиМатериалов
ИТОГИ
    СУММА(Сумма)
ПО
    Материал ИЕРАРХИЯ

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

Например:

ВЫБРАТЬ 
    Услугислуга КАК Услуга, 
    Услуги.Количество КАК Количество
ИЗ 
    Документ.АктОбОказанииУслуг.Услуги КАК Услуги
УПОРЯДОЧИТЬ ПО Услуга
ИТОГИ 
    СУММА(Количество)
ПО 
    Услуга КАК ИтогПоУслуге

 

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

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

Например:

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

ВЫБРАТЬ Док.Услуга КАК Услуга,
    Док.Услуга.Родитель КАК Группа,
ИЗ Документ.АктОбОказанииУслуг.Услуги КАК Док

Вложенные запросы.

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

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

Вложенный запрос можно также использовать при задании параметров виртуальной таблицы и в секции ГДЕ в операторах "В" и "НЕ В".

Например:

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

Встроенные функции, условия по функциям.

В языке запросов определены встроенные функции, которые могут использоваться в выражениях в списке полей выборки секции ВЫБРАТЬ и в условии отбора в предложении ГДЕ.

Функции работы со строками:

*       ПОДСТРОКА(<Строка>, <НачальнаяПозиция>, <Длина>)

Функции работы с датами:

*       ГОД( <Дата> );

*       КВАРТАЛ( < Дата > );

*       МЕСЯЦ( < Дата > );

*       ДЕНЬГОДА( < Дата > );

*       ДЕНЬ( < Дата > );

*       НЕДЕЛЯ( < Дата > );

*       ДЕНЬНЕДЕЛИ( < Дата > );

*       ЧАС( < Дата > );

*       МИНУТА( < Дата > );

*       СЕКУНДА( < Дата > );

*       НАЧАЛОПЕРИОДА(<Дата>, <Период>), <Период> - одно из : минута, час, день, неделя, месяц, квартал, год, декада, полугодие;

*       КОНЕЦПЕРИОДА(<Дата>, <Период>), <Период> - одно из : минута, час, день, неделя, месяц, квартал, год, декада, полугодие;

*       ДОБАВИТЬКДАТЕ(<Дата>, <Тип>, <Количество>), <Тип> - одно из : минута, час, день, неделя, месяц, квартал, год, декада, полугодие, <Количество> - тип Число;

*       РАЗНОСТЬДАТ(<Дата1>, <Дата2>, <Тип>), <Тип> - одно из : минута, час, день, месяц, квартал, год.

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

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

ВЫБРАТЬ
    Ссылка КАК Документ,
    Клиент КАК Заказчик,
    ГОД(Дата) КАК Год,
    МЕСЯЦ(Дата) КАК Месяц,
    ДЕНЬ(Дата) КАК День,
    ДЕНЬНЕДЕЛИ(Дата) КАК ДеньНедели
ИЗ
    ДокументктОбОказанииУслуг

или

ВЫБРАТЬ
    ПоступлениеМатериаловсылка,
    ПоступлениеМатериалов.Дата
ИЗ
    Документ.ПоступлениеМатериалов КАК ПоступлениеМатериалов
ГДЕ
РАЗНОСТЬДАТ(ПоступлениеМатериалов.Дата, ДАТАВРЕМЯ(2005, 1, 1), ДЕНЬ) < 100

Прочие функции:

*                        ЕСТЬNULL(<Выражение1>, <Выражение2>) - предназначена для замены значения NULL на другое значение. Параметр <Выражение1> - выражение любого типа.
Параметр <Выражение2> - выражение любого типа. Возвращаемое значение: значение первого параметра, в случае, если первый параметр не содержит значение NULL, значение второго параметра в противном случае. Второй параметр будет преобразован к типу первого в случае, если тип первого параметра является строкой или числом.

*                        ПРЕДСТАВЛЕНИЕ(<Выражение>) - предназначена для получения строкового представления значения произвольного типа. Возвращаемое значение - представление значения, тип Строка.

Например:

ВЫБРАТЬ Услугислуга,
    ЕСТЬNULL(СУММА(Услуги.Количество), 0) КАК Количество
ИЗ
    Документ.АктОбОказанииУслуг.Услуги КАК Услуги
СГРУППИРОВАТЬ ПО
    Услуги.Услуга

Например:

ВЫБРАТЬ ПРЕДСТАВЛЕНИЕ(Актлиент) КАК Покупатель
ИЗ Документ.АктОбОказанииУслуг КАК Акт

Агрегатные функции.

Агрегатные функции запроса используется для получения сводной информации из базы данных при группировке результата запроса и подсчета итогов. Агрегатные функции могут использоваться в списке полей выборки, предложениях ИМЕЮЩИЕ, ИТОГИ, УПОРЯДОЧИТЬ ПО.

Ниже перечислен состав агрегатных функций, используемых в языке запросов:

Функция

Описание

СУММА

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

МАКСИМУМ

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

МИНИМУМ

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

СРЕДНЕЕ

Вычисляет среднее значение всех попавших в выборку значений поля, переданного в качестве параметра функции.
Поле, переданное в качестве параметра, должно содержать числовые значения. Если поле не содержит числовых значений или если среди значений поля в выборке встретится нечисловое значение (кроме значений NULL), то применение функции СУММА, вызовет ошибку.

КОЛИЧЕСТВО

Вычисляет количество значений поля, переданного в качестве параметра функции. Функция КОЛИЧЕСТВО допускает три способа использования:
подсчет количества значений, не равных NULL;
подсчет количества различных значений, не равных NULL. Для этого перед указанием поля необходимо использовать ключевое слово РАЗЛИЧНЫЕ.
подсчет общего количества строк результата запроса. Для этого в качестве параметра функции надо указать символ * (звездочка).

Например:

ВЫБРАТЬ
    Количество(РАЗЛИЧНЫЕ Поставщик) КАК ВсегоПоставщиков,
    СУММА(СуммаДокумента) КАК СуммаЗакупки,
    МИНИМУМ(СуммаДокумента) КАК СуммаМинимум,
    СРЕДНЕЕ(СуммаДокумента) КАК СуммаСредняя,
    МАКСИМУМ(СуммаДокумента) КАК СуммаМаксимум
ИЗ ДокументоступлениеМатериалов

Операции выбора в языке запросов.

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

Следующий запрос использует операцию выбора для заполнения поля Диапазон:

ВЫБРАТЬ
    КОЛИЧЕСТВО(Услугисылка) КАК Документов,
    СУММА(Услуги.Сумма) КАК Сумма,
    ВЫБОР
        КОГДА Услуги.Ссылка.Дата < ДАТАВРЕМЯ(2005, 1, 1, 0, 0, 0)
            ТОГДА "До января"
        КОГДА Услуги.Ссылка.Дата < ДАТАВРЕМЯ(2005, 2, 1, 0, 0, 0)
            ТОГДА "До февраля"
        ИНАЧЕ "После февраля"
    КОНЕЦ КАК Диапазон
ИЗ
    Документ.АктОбОказанииУслуг.Услуги КАК Услуги

СГРУППИРОВАТЬ ПО
    ВЫБОР
        КОГДА Услуги.Ссылка.Дата < ДАТАВРЕМЯ(2005, 1, 1, 0, 0, 0)
            ТОГДА "До января"
        КОГДА Услугисылка.Дата < ДАТАВРЕМЯ(2005, 2, 1, 0, 0, 0)
            ТОГДА "До февраля"
        ИНАЧЕ "После февраля"
    КОНЕЦ

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

Поля исходных таблиц могут иметь составной тип данных. Для таких полей может возникнуть необходимость привести значения поля к какому-либо определенному типу. В языке запросов предусмотрен оператор ВЫРАЗИТЬ, который выполняет приведение типа. Им можно пользоваться в списке полей выборки (предложение ВЫБРАТЬ) и в условии отбора (предложение ГДЕ).

Синтаксическая диаграмма оператора ВЫРАЗИТЬ такова:

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

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

Например:

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

Оператор проверки значения на NULL. Оператор Есть NULL позволяет проверить значение выражения слева от него на NULL. Если значение равно NULL - результатом оператора будет ИСТИНА, иначе - ЛОЖЬ. Применение НЕ изменяет действие оператора на обратное.

Например:

ВЫБРАТЬ
    Клиентыаименование,
    Клиенты.СайтКлиента
ИЗ
    Справочник.Клиенты КАК Клиенты
ГДЕ
    Клиенты.СайтКлиента ЕСТЬ NULL

Оператор проверки ссылочного значения. Оператор ССЫЛКА позволяет проверить, является ли значение выражения, указанного слева от него, ссылкой на таблицу, указанную справа. Если да - результатом оператора будет ИСТИНА, иначе - ЛОЖЬ.

Например:

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

Оператор В. Оператор В позволяет проверить, совпадает ли значение выражения, указанного справа от него, с одним из значений, описанных слева. Если совпадает хотя бы с одним - результатом оператора будет ИСТИНА, иначе - ЛОЖЬ. Применение НЕ изменяет действие оператора на обратное.

Например:

ВЫБРАТЬ Наименование
ИЗ Справочникатериалы
ГДЕ Наименование В ("Скоба для крепления","Розетки RJ-45")

Оператор В ИЕРАРХИИ. Для справочников проверка может осуществляться на принадлежность по иерархии. Результатом оператора В ИЕРАРХИИ будет ИСТИНА, если значение выражения слева является ссылкой на элемент справочника и входит во множество значений справа или иерархически принадлежит какой-нибудь группе, содержащейся в этой группе.

Например:

ВЫБРАТЬ Наименование
ИЗ Справочникатериалы
ГДЕ Справочник.Материалы.Ссылка НЕ В ИЕРАРХИИ (&Группа)

Оператор МЕЖДУ. Оператор МЕЖДУ позволяет проверить, входит ли значение выражения, указанного слева от него, в диапазон, указанный справа. Если входит - результатом оператора будет ИСТИНА, иначе - ЛОЖЬ. Применение НЕ изменяет действие оператора на обратное.

Например:

ВЫБРАТЬ Наименование, Стоимость
ИЗ Справочникслуги
ГДЕ Стоимость МЕЖДУ 100 и 1000

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

<Соединение>

[ВНУТРЕННЕЕ] СОЕДИНЕНИЕ <Описание источника> ПО <Условие отбора>
ЛЕВОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ <Описание источника> ПО <Условие отбора> ПРАВОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ
<Описание источника> ПО <Условие отбора>
ПОЛНОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ <Описание источника> ПО <Условие отбора>

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

Например:

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

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

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

Например:

ВЫБРАТЬ РАЗЛИЧНЫЕ Менеджерысылка КАК Менеджер,
    Заказы.Ссылка КАК Заказ
    ИЗ Справочник.Менеджеры КАК Менеджеры
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Заказы КАК Заказы
    ПО Менеджеры.Ссылка = Заказы.Менеджер

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

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

ВЫБРАТЬ Заказысылка КАК Заказ,
    Менеджеры.Ссылка КАК Менеджер
    ИЗ Справочник.Заказы КАК Заказы
        ПРАВОЕ СОЕДИНЕНИЕ Справочник.Менеджеры КАК Менеджеры
        ПО Заказы.Менеджер = Менеджеры.Ссылка

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

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

Например:

ВЫБРАТЬ Менеджерысылка КАК Менеджер,
    Заказы.Ссылка КАК Заказ
ИЗ Справочник.Менеджеры КАК Менеджеры
    ПОЛНОЕ СОЕДИНЕНИЕ Справочник.Заказы КАК Заказы
    ПО Менеджеры.Ссылка = Заказы.Менеджер

Использование ключевых слов "ОБЪЕДИНИТЬ" и "ОБЪЕДИНИТЬ ВСЕ" в запросах.

В общем случае, при объединении в запросе результатов нескольких запросов следует использовать конструкцию "ОБЪЕДИНИТЬ ВСЕ", а не "ОБЪЕДИНИТЬ".

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

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

Неправильно:

ВЫБРАТЬ Поступлениесылка
ИЗ Документ.ПоступлениеМатериалов КАК Поступление
ОБЪЕДИНИТЬ
ВЫБРАТЬ Акт.Ссылка
ИЗ Документ.АктОбОказанииУслуг КАК Акт

Правильно:

ВЫБРАТЬ Поступлениесылка
ИЗ Документ.ПоступлениеМатериалов КАК Поступление
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ Акт.Ссылка
ИЗ Документ.АктОбОказанииУслуг КАК Акт

В тексте запроса можно писать комментарии, которые оформляются таким же образом, как и во встроенном языке платформы 8.0, с помощью //.

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

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

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

Конструктор запросов может быть вызван в режиме "Конфигуратор" - пункт "Конструктор запросов" в контекстном меню по нажатию правой кнопки мыши в окне редактирования текста модуля объекта или из пункта главного меню "Текст - Конструктор запроса" при редактировании текста модуля. Так же он может быть вызван и в режиме "Предприятие", в контекстном меню окна редактирования поля текстового документа, если в "Конфигураторе" для этого объекта для свойства "Расширение" было установлено значение "Язык запросов".

Общий вид конструктора запросов представлен ниже:

Рис 9.1.

Как видно из рисунка, форма Конструктора запросов имеет несколько закладок.

Рассмотрим каждую из них.

Таблицы и поля. На этой закладке в списке "База данных» приводится полное дерево метаданных - возможных источников данных запросов, причем как реальные, так и виртуальные таблицы. В список "Таблицы" нужно перенести те источники данных, которые необходимы в запросе, содержимое этого списка будет стоять после ключевого слова ИЗ в создаваемом запросе. В качестве источника можно использовать вложенный запрос, для создания которого в списке "Таблицы" необходимо нажать кнопку командной панели этого списка или аналогичный пункт из контекстного меню, вызываемого правой кнопкой мыши на поле данного списка. Также можно задать новое имя источника, используя контекстное меню, пункт "Переименовать таблицу". Из списков "База данных" и "Таблицы" в список "Поля" нужно перенести те поля, которые должны отображаться в результате запроса, то есть содержимое конструкции ВЫБРАТЬ. Нужные поля можно перенести из окна "Таблицы" по двойному щелчку мыши или используя клавиши ">" ">>" или из окон "База данных" или "Таблицы", используя технологию Drag&Drop (перетаскивание объекта мышью). В таблице "Поля" по кнопке можно добавить новое вычисляемое поле.

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

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

Условия. На этой закладке на результат запроса могут быть наложены всевозможные условия отбора результата запроса, то есть содержимое предложения ГДЕ. Тут можно задать как простые условия - равенство реквизита параметру, так и сложные - ручной ввод условия. Условие можно добавить по кнопке , пункт "Добавить" из контекстного меню или путем переноса нужного поля, используя технологию Drag&Drop.

Дополнительно. На этой закладке в запрос могут быть введены такие ключевые слова, как ПЕРВЫЕ, ДЛЯ ИЗМЕНЕНИЯ, РАЗЛИЧНЫЕ, РАЗРЕШЕННЫЕ.

Объединения/Псевдонимы. Здесь объединяются данные из нескольких таблиц (причем есть возможность поменять таблицы местами), а также можно редактировать псевдонимы выбираемых полей, назначаемые Конструктором запросов автоматически.

При объединении данных нескольких таблиц на закладке "Таблицы и поля" появляются дополнительные боковые закладки для редактирования запросов. В случае разного количества выбранных полей из таблиц - источников конструктор сам добавит в каждый запрос необходимое количество недостающих полей со значением NULL. На закладке Объединения/Псевдонимы можно настроить соответствие добавленных полей и полей результата запроса.

Рис 9.2.

Порядок. По необходимости, задаются условия сортировки результата запроса. Если в качестве источника выбрана иерархическая таблица, то в качестве значения сортировки можно выбрать ИЕРАРХИЯ.

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

Следует обратить внимание и на наличие кнопки "Запрос", по нажатию которой можно сразу же увидеть текст запроса.

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

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

Рис 9.3.

Консоль запросов. Разработчиками фирмы 1С, для системы 1Средприятие 8.0 была создана удобная внешняя обработка "Консоль запросов", которая поставляется на дисках информационно-технологического сопровождения (ИТС). Эта обработка предоставляет удобный инструмент разработки текстов запросов и просмотра их результата. Общий вид обработки представлен на рисунке:

Рис 9.4.

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

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

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

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

*                        Прямой обход - выборка будет выдавать записи в той последовательности, в которой они располагаются в результате запроса. Для получения линейной выборки необходимо вызвать метод Выбрать объекта РезультатЗапроса без параметров, либо с параметром ОбходРезультатаЗапросарямой. Для навигации используются три метода: Следующий(), СледующийПоЗначениюПоля(), НайтиСледующий()

*                        Обход по группировкам с иерархией - обходятся только записи, находящиеся на одном уровне. Для получения иерархической выборки из результата необходимо вызвать метод Выбрать объекта РезультатЗапроса с параметром ОбходРезультатаЗапросаоГруппировкамСИерархией. Чтобы получить остальные записи результата запроса у объекта ВыборкаИзРезультатаЗапроса можно получить еще одну выборку, которая будет обходить подчиненные записи текущей записи выборки.

*                        Обход по группировкам - обходятся только записи одного уровня, при этом записи с иерархическими итогами рассматриваются как детальные записи, а не как узловые. Стоит отметить, что группировочные записи и запись общих итогов относятся к более высокому уровню, чем детальные записи. Для получения выборки по группировкам из результата запроса необходимо вызвать метод Выбрать объекта РезультатЗапроса с параметром ОбходРезультатаЗапросаоГруппировкам

Рассмотрим использование запросов на примере отчета "Реализация за период".

Процедура РеализацияЗаПериод(ТабДок, КонДата, НачДата, СуммаДокумента) Экспорт
//{{КОНСТРУКТОР_ВЫХОДНЫХ_ФОРМ(РеализацияЗаПериод)
// Данный фрагмент построен конструктором.
// При повторном использовании конструктора, внесенные вручную изменения будут утеряны!!!

Макет = ОтчетОбъектолучитьМакет("РеализацияЗаПериод");
Запрос = Новый Запрос;
Запросекст =
"ВЫБРАТЬ
|    АктОбОказанииУслугУслуги.Услуга КАК Номенклатура,
|    АктОбОказанииУслугУслуги.Услуга.Представление КАК НоменклатураПредставление,
|    АктОбОказанииУслугУслуги.Сумма КАК Сумма
| ИЗ
|    Документ.АктОбОказанииУслуг.Услуги КАК АктОбОказанииУслугУслуги
| ГДЕ
|    АктОбОказанииУслугУслуги.Ссылка.Дата МЕЖДУ &НачДата И &КонДата И
|    АктОбОказанииУслугУслуги.Ссылка.СуммаДокумента >= &СуммаДокумента
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ
|
|    АктОбОказанииУслугМатериалы.Материал,
|    АктОбОказанииУслугМатериалы.Материал.Представление,
|    АктОбОказанииУслугМатериалы.Сумма
| ИЗ
|    Документ.АктОбОказанииУслуг.Материалы КАК АктОбОказанииУслугМатериалы
|
| ГДЕ
|    АктОбОказанииУслугМатериалы.Ссылка.Дата МЕЖДУ &НачДата И &КонДата И
|    АктОбОказанииУслугМатериалысылка.СуммаДокумента >= &СуммаДокумента
|
| УПОРЯДОЧИТЬ ПО
|    Номенклатура
|
| ИТОГИ СУММА(Сумма) ПО
|    ОБЩИЕ
| АВТОУПОРЯДОЧИВАНИЕ";

Запрос.УстановитьПараметр("КонДата", КонДата);
Запрос.УстановитьПараметр("НачДата", НачДата);
Запрос.УстановитьПараметр("СуммаДокумента", СуммаДокумента);

Результат = Запрос.Выполнить();

ОбластьЗаголовок = Макет.ПолучитьОбласть("Заголовок");
ОбластьПодвал = Макет.ПолучитьОбласть("Подвал");
ОбластьШапкаТаблицы = Макет.ПолучитьОбласть("ШапкаТаблицы");
ОбластьПодвалТаблицы = Макет.ПолучитьОбласть("ПодвалТаблицы");
ОбластьОбщийИтог = Макет.ПолучитьОбласть("ОбщиеИтоги");
ОбластьДетальныхЗаписей = Макет.ПолучитьОбласть("Детали");

ТабДокчистить();
ТабДок.Вывести(ОбластьЗаголовок);
ТабДок.Вывести(ОбластьШапкаТаблицы);
ТабДок.НачатьАвтогруппировкуСтрок();

ВыборкаОбщийИтог = Результат.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);

ВыборкаОбщийИтог.Следующий(); // Общий итог
ОбластьОбщийИтог.Параметры.Заполнить(ВыборкаОбщийИтог);
ТабДок.Вывести(ОбластьОбщийИтог, ВыборкаОбщийИтог.Уровень());

ВыборкаДетали = ВыборкаОбщийИтог.Выбрать();
Пока ВыборкаДетали.Следующий() Цикл
    ОбластьДетальныхЗаписей.Параметры.Заполнить(ВыборкаДетали);
    ТабДок.Вывести(ОбластьДетальныхЗаписей, ВыборкаДетали.Уровень());
КонецЦикла;

ТабДок.ЗакончитьАвтогруппировкуСтрок();
ТабДок.Вывести(ОбластьПодвалТаблицы);
ТабДокывести(ОбластьПодвал);

//}}КОНСТРУКТОР_ВЫХОДНЫХ_ФОРМ
КонецПроцедуры

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

 Задание 14

1.     Создайте отчет "Поступление материалов" с выводом информации о поступлении материалов в разрезе поставщиков и материалов.

2.     Создайте отчет "Взаиморасчеты с клиентом" с выводом информации о взаиморасчетах с клиентами в разрезе контрагентов и расчетных документов.

3.     Создайте отчет "Отчет о продажах" с выводом информации о продажах услуг в разрезе контрагентов и услуг и с выводом информации в виде сводной таблицы.