Если Вы работаете с одной таблицей, то Вам нет необходимости использовать объединение. Совсем другое дело, когда Вы работаете с несколькими таблицами и Вам, например, нужно получить данные, и по покупкам и по продажам товаров.
Для объединения используется предложение ОБЪЕДИНИТЬ . На самом деле происходит объединение результатов запросов, то есть каждый запрос собирает данные независимо друг от друга, а затем эти результаты объединяются в один. И только над последним запросом выполняются остальные операции, например, упорядочивание и расчет итогов.
При объединении запросов происходит "вертикальное склеивание" результатов, то есть сначала идут строки результата первого запроса, затем второго.
Перейдём к практике:
Код 1C v 8.х
ВЫБРАТЬ
Номенклатура,
Количество,
Сумма
ИЗ
Документ.Приходная.Товары
ОБЪЕДИНИТЬ
ВЫБРАТЬ
Номенклатура,
Количество,
Сумма
ИЗ
Документ.Приходная.Товары
Результат этого запроса следующий:
Номенклатура Количество Сумма
Бумага А4 25 14 500
Стикеры 500л 8 4 880
Файл прозрачный 5 4 100
Набор офисный 8 8 840
В итоге мы получим две таблицы, которые будут соединены друг с другом. Трудно увидеть, где кончается документ Приходная и начинается документ Расходная. Но мы сделаем кое-что, чтобы понять это:
Код 1C v 8.х
ВЫБРАТЬ
Номенклатура,
Количество КАК КолПриход,
Сумма КАК СумПриход,
0 КАК КолРасход,
0 КАК СумРасход
ИЗ
Документ.Приходная.Товары
ОБЪЕДИНИТЬ
ВЫБРАТЬ
Номенклатура,
0 КАК КолПриход,
0 КАК СумПриход,
Количество КАК КолРасход,
Сумма КАК СумРасход
ИЗ
Документ.Расходная.Товары
Как видно из текста запроса мы задали для каждого документа разные псевдонимы для количества и суммы. Также на место тех полей, где мы будем получать значения суммы и количества, поставим нули. Это нужно, чтобы пустые значения количества и суммы заменялись нулями.
Бумага А4 25 14 500
Стикеры 500л 8 4 880
Файл прозрачный 5 4 100
Набор офисный 8 8 840
Набор офисный 1 1 105
Файл прозрачный 1 820
Стикеры 500л 1 610
Теперь нам нужно из результат запроса убрать дублирующиеся элементы. У нас два запроса и если мы будем сворачивать, каждый отдельно, чтобы исключить дублирующиеся элементы, то у нас ничего не получится. Поэтому мы приведём запрос к следующему виду:
Код 1C v 8.х
ВЫБРАТЬ
Номенклатура,
СУММА(КолПриход) КАК КолПриход,
СУММА(СумПриход) КАК СумПриход,
СУММА(КолРасход) КАК КолРасход,
СУММА(СумРасход) КАК СумРасход
ИЗ
(ВЫБРАТЬ
Номенклатура,
Количество КАК КолПриход,
Сумма КАК СумПриход,
0 КАК КолРасход,
0 КАК СумРасход
ИЗ
Документ.Приходная.Товары
ОБЪЕДИНИТЬ
ВЫБРАТЬ
Номенклатура,
0 КАК КолПриход,
0 КАК СумПриход,
Количество КАК КолРасход,
Сумма КАК СумРасход
ИЗ
Документ.Расходная.Товары) КАК ВложенныйЗапрос
СГРУППИРОВАТЬ ПО Номенклатура
Итак, мы видим, что весь наш запрос заключён в скобки и назван как ВложенныйЗапрос. Это сделано для того, чтобы мы смогли сгруппировать записи из обоих подзапросов и исключить дублирующиеся элементы. Такая конструкция называется вложенным запросом. Результат этого запроса следующий:
Номенклатура КолПриход СумПриход КолРасход СумРасход
Бумага А4 25 14 500
Стикеры 500л 8 4 880 1 610
Набор офисный 5 4 100 1 1 105
Файл прозрачный 8 8 840 1 820
В итоге получаем сгруппированную таблицу, в которой данные из вложенных подзапросов приведены к понятному виду. Количество вложенных запросов не должно превышать 255. Мы использовали здесь четыре подзапроса.
Если мы хотим оставлять в запросе только уникальные значения записей при этом, не используя группировку. Нужно писать предложение ОБЪЕДЕНИТЬ без ключевого слова ВСЕ.
Информация взята с сайта
Бывают ситуации когда в одном запросе необходимо объединить несколько запросов, причем соединения таблиц никак не могут в этом помочь. Проще всего показать на примере.
Допустим в нашей системе факты покупки и продажи товара регистрируются документами Приход и Расход соответственно. Контрагент может являться как покупателем, так и поставщиком. Зачет задолженности может производится поставкой товара:
Чтобы подсчитать общую задолженность контрагента необходимо сложить сумму всех расходов по этому контрагенту и вычесть сумму всех приходов от этого же контрагента, проще всего это сделать с помощью оператора ОБЪЕДИНИТЬ ВСЕ:
Запрос.Текст =
"
//посчитаем на какую сумму мы отгрузили контрагентам
|ВЫБРАТЬ
| Расход.Контрагент,
|ИЗ
| Документ.Расход КАК Расход
|СГРУППИРОВАТЬ ПО
| Расход.Контрагент
|ОБЪЕДИНИТЬ ВСЕ
//посчитаем на какую сумму контрагенты
//поставили нам товара
|ВЫБРАТЬ
| Приход.Контрагент,
//сумму берем с отрицательным знаком,
//что при объединении она вычлась из суммы расхода
| СУММА(-Приход.Сумма)
|ИЗ
| Документ.Приход КАК Приход
|СГРУППИРОВАТЬ ПО
| Приход.Контрагент";
В первом запросе мы считаем сумму расходов по каждому контрагенту, во втором - сумму на которую нам поставил товар каждый из контрагентов. Сумма во втором запросе берется со знаком минус, что бы при свертке результирующей таблицы, она вычлась из суммы отгрузки данному контрагенту. В итоге получим таблицу вида:
Это не совсем то, что мы хотели, но уже близко. Для достижения требуемого результата осталось сгруппировать по контрагенту. Для этого запрос необходимо поместить во временную таблицу (работа с временными таблицами рассматривается в закрытой части курса ) и уже из нее выбрать и сгруппировать поля:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Расход.Контрагент,
| СУММА(Расход.Сумма) КАК Долг
|ПОМЕСТИТЬ ВТ_ПриходРасход
|ИЗ
| Документ.Расход КАК Расход
|СГРУППИРОВАТЬ ПО
| Расход.Контрагент
|ОБЪЕДИНИТЬ ВСЕ
|ВЫБРАТЬ
| Приход.Контрагент,
| СУММА(-Приход.Сумма)
|ИЗ
| Документ.Приход КАК Приход
|СГРУППИРОВАТЬ ПО
| Приход.Контрагент
|;
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_ПриходРасход.Контрагент,
| СУММА(ВТ_ПриходРасход.Долг) КАК Долг
|ИЗ
| ВТ_ПриходРасход КАК ВТ_ПриходРасход
|СГРУППИРОВАТЬ ПО
| ВТ_ПриходРасход.Контрагент";
При объединении двух запросов количество полей у них должно быть одинаковым, если в каком либо из запросов не хватает полей, то их надо добавить в виде констант. Обратимся к примеру выше, пусть в документе расход также есть поле скидка, которое уменьшает сумму долга контрагента, но в документе приход нет никаких скидок. Как быть в этом случае? Так:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Расход.Контрагент,
|ИЗ
| Документ.Расход КАК Расход
|СГРУППИРОВАТЬ ПО
| Расход.Контрагент
|ОБЪЕДИНИТЬ ВСЕ
|ВЫБРАТЬ
| Приход.Контрагент,
| СУММА(-Приход.Сумма),
//добавляем нулевое поле скидка
| 0
|ИЗ
| Документ.Приход КАК Приход
|СГРУППИРОВАТЬ ПО
| Приход.Контрагент";
Осталось вычесть скидку и сгруппировать.
Также важен порядок. Поля будут объединятся именно в том порядке в котором они указаны в секциях ВЫБРАТЬ обоих запросов. Применительно к предыдущему примеру - поменяем местами поля скидка и сумма в выборке приходов:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Расход.Контрагент,
| СУММА(Расход.Сумма) КАК Долг,
| СУММА(Расход.Скидка) КАК Скидка
|ИЗ
| Документ.Расход КАК Расход
|СГРУППИРОВАТЬ ПО
| Расход.Контрагент
|ОБЪЕДИНИТЬ ВСЕ
|ВЫБРАТЬ
| Приход.Контрагент,
//меняем местами
| 0,
| СУММА(-Приход.Сумма)
|ИЗ
| Документ.Приход КАК Приход
|СГРУППИРОВАТЬ ПО
| Приход.Контрагент";
Доброго времени суток, уважаемые читатели блога сайт. Сегодня мы детально обсудим объединение запросов в 1С . Вы можете также скачать для типовой конфигурации Управление торговым предприятием для Казахстана, редакция 1.0.
Если Вы работаете с одной таблицей, то Вам нет необходимости использовать объединение. Совсем другое дело, когда Вы работаете с несколькими таблицами и Вам, например, нужно получить данные по покупкам и по продажам товаров.
Для объединения используется предложение ОБЪЕДИНИТЬ / UNION . На самом деле происходит объединение результатов запросов , то есть каждый запрос собирает данные независимо друг от друга, а затем эти результаты объединяются в один. И только над последним запросом выполняются остальные операции, например, упорядочивание и расчет итогов.
В отличие от при объединении запросов происходит «вертикальное склеивание» результатов, то есть сначала идут строки результата первого запроса, затем второго.
Перейдём к практике:
ВЫБРАТЬ Номенклатура, Количество, СуммаОБЪЕДИНИТЬ ВЫБРАТЬ Номенклатура, Количество, Сумма ИЗ Документ.Расходная.Товары
Результат этого запроса следующий:
В итоге мы получим две таблицы, которые будут соединены друг с другом. Трудно увидеть, где кончается документ Приходная и начинается документ Расходная. Но мы кое-что сделаем, чтобы разобраться:
ВЫБРАТЬ Номенклатура, Количество КАК КолПриход , Сумма КАК СумПриход , 0 КАК КолРасход , 0 КАК СумРасход ИЗ Документ.Приходная.Товары ОБЪЕДИНИТЬ КолПриход , 0 КАК СумПриход , Количество КАК КолРасход , Сумма КАК СумРасход ИЗ Документ.Расходная.Товары
Как видно из текста запроса мы задали для каждого документа разные псевдонимы для количества и суммы. Также на место тех полей, где мы будем получать значения суммы и количества, поставим нули. Это нужно, чтобы пустые значения количества и суммы заменялись нулями.
Номенклатура | КолПриход | СумПриход | КолРасход | СумРасход |
Бумага А4 | 25 | 14 500 | ||
Стикеры 500л | 8 | 4 880 | ||
Файл прозрачный | 5 | 4 100 | ||
Набор офисный | 8 | 8 840 | ||
Набор офисный | 1 | 1 105 | ||
Файл прозрачный | 1 | 820 | ||
Стикеры 500л | 1 | 610 |
Теперь нам нужно из результат запроса убрать дублирующиеся элементы. У нас два запроса и если мы будем сворачивать, каждый отдельно, чтобы исключить дублирующиеся элементы, то у нас ничего не получится. Поэтому мы приведём запрос к следующему виду:
ВЫБРАТЬИтак, мы видим, что весь наш запрос заключён в скобки и назван как ВложенныйЗапрос. Это сделано для того, чтобы мы смогли сгруппировать записи из обоих подзапросов и исключить дублирующиеся элементы. Такая конструкция называется вложенным запросом. Результат этого запроса следующий:
Номенклатура | КолПриход | СумПриход | КолРасход | СумРасход |
Бумага А4 | 25 | 14 500 | ||
Стикеры 500л | 8 | 4 880 | 1 | 610 |
Набор офисный | 5 | 4 100 | 1 | 1 105 |
Файл прозрачный | 8 | 8 840 | 1 | 820 |
В итоге получаем сгруппированную таблицу, в которой данные из вложенных подзапросов приведены к понятному виду.
Количество вложенных запросов не должно привышать 255. Мы использовали здесь четыре подзапрос.
Если мы хотим оставлять в запросе только уникальные значения записей, не используя группировку, тогда, нужно писать предложение ОБЪЕДЕНИТЬ без ключевого слова ВСЕ .
Язык запросов является одним из основополагающих механизмов 1С 8.3 для разработчиков. При помощи запросов можно быстро получить любые данные, хранящиеся в базе. Его синтаксис очень похож на SQL, но есть и отличия.
Основные достоинства языка запросов 1С 8.3 (8.2) перед SQL:
Недостатки языка запросов в 1С:
Рассмотрим наш мини учебник по основным конструкциям языка запросов 1С.
В связи с тем, что запросы в 1С позволяют лишь получать данные, любой запрос должен начинаться со слова «ВЫБРАТЬ». После этой команды указываются поля, данные из которых нужно получить. Если указать «*», то будут выбраны все доступные поля. Место, откуда будут выбираться данные (документы, регистры, справочники и прочее) указывается после слова «ИЗ».
В рассмотренном ниже примере выбираются наименования всей номенклатуры из справочника «Номенклатура». После слова «КАК» указываются псевдонимы (имена) для таблиц и полей.
ВЫБРАТЬ
Номенклатура.Наименование КАК НаименованиеНоменклатуры
ИЗ
Справочник.Номенклатура КАК Номенклатура
Рядом с командой «ВЫБРАТЬ» можно указать ключевые слова:
Эти ключевые слова могут использоваться как все вместе, так и по отдельности.
Это предложение блокирует данные для исключения взаимных конфликтов. Заблокированные данные не будут считываться из другого соединения до окончания транзакции. В данном предложении можно указывать конкретные таблицы, которые нужно заблокировать. В противном случае будут заблокированы все. Конструкция актуальна лишь для режима автоматических блокировок.
Чаще всего предложение «ДЛЯ ИЗМЕНЕНИЯ» используется при получении остатков. Ведь при одновременной работе нескольких пользователей в программе, пока один получает остатки, другой может их изменить. В таком случае полученный остаток будет уже не верен. Если же заблокировать данные этим предложением, то пока первый сотрудник не получит корректный остаток и не совершит с ним все необходимые манипуляции, второй сотрудник будет вынужден ждать.
ВЫБРАТЬ
Взаиморасчеты.Сотрудник,
Взаиморасчеты.СуммаВзаиморасчетовОстаток
ИЗ
РегистрНакопления.ВзаиморасчетыССотрудниками.Остатки КАК Взаиморасчеты
ДЛЯ ИЗМЕНЕНИЯ
Конструкция необходима для наложения какого-либо отбора на выгружаемые данные. В некоторых случая получения данных из регистров разумнее прописывать условия отборов в параметрах виртуальных таблиц. При использовании «ГДЕ», сначала получаются все записи, и только потом применяется отбор, что значительно замедляет выполнение запроса.
Ниже приведен пример запроса получения контактных лиц с определенной должностью. Параметр отбора имеет формат: &ИмяПараметра (имя параметра произвольное).
Конструкция позволяет указывать условия непосредственно в теле запроса.
В приведенном ниже примере «ДополнительноеПоле» будет содержать текст в зависимости от того проведен документ или нет:
ВЫБРАТЬ
ПоступлениеТиУ.Ссылка,
ВЫБОР
КОГДА ПоступлениеТиУ.Проведен
ТОГДА «Документ проведен!»
ИНАЧЕ «Документ не проведен…»
КОНЕЦ КАК ДополнительноеПоле
ИЗ
Документ.ПоступлениеТоваровУслуг КАК ПоступлениеТиУ
Соединения связывают две таблицы по определенному условию связи.
Суть ЛЕВОГО соединения заключается в том, что полностью берется первая указанная таблица и к ней по условию связи привязывается вторая. Если записей, соответствующих первой таблице во второй не нашлось, то в качестве их значений подставляется NULL. Проще говоря, главной является первая указанная таблица и к её данным уже подставляются данные второй таблицы (если они есть).
Например, необходимо получить номенклатурные позиции из документов «Поступление товаров и услуг» и цены из регистра сведений «Цены номенклатуры». В данном случае, если цена у какой-либо позиции не найдена, вместо нее подставиться NULL. Из документа все позиции будут выбраны вне зависимости от того, есть ли на них цена или нет.
ВЫБРАТЬ
ПоступлениеТиУ.Номенклатура,
Цены.Цена
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТиУ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ПО ПоступлениеТиУ.Номенклатура = Цены.Номенклатура
В ПРАВОМ все в точности да наоборот.
Данный вид соединения отличается от предыдущих тем, что в результате будут возвращены все записи как первой таблицы, так и второй. Если по заданному условию связи в первой или второй таблице не найдено записей, вместо них будет возвращено значение NULL.
При использовании в предыдущем примере полного соединения будут выбраны все позиции номенклатуры из документа «Поступление товаров и услуг» и все последние цены из регистра «Цены номенклатуры». Значения не найденных записей, как в первой, так и во второй таблице будут равняться NULL.
Отличием ВНУТРЕННЕГО соединения от ПОЛНОГО является то, что если хотя бы в одной из таблиц не найдена запись, то запрос не выведет ее вообще. В результате будут выбраны только те номенклатурные позиции из документа «Поступление товаров и услуг», для которых в регистре сведений «Цены номенклатуры» есть записи, если в предыдущем примере заменить «ПОЛНОЕ» на «ВНУТРЕННЕЕ».
Группировка в запросах 1С позволяет сворачивать строки таблицы (группировочные поля) по определенному общему признаку (группируемым полям). Группировочные поля могут выводиться только с применением агрегатных функций.
Результатом следующего запроса будет список видов номенклатуры с максимальными ценами по ним.
ВЫБРАТЬ
,
МАКСИМУМ(Цены.Цена) КАК Цена
ИЗ
СГРУППИРОВАТЬ ПО
Цены.Номенклатура.ВидНоменклатуры
В отличие от группировки при использовании итогов выводятся все записи и уже к ним добавляются итоговые строки. Группировка выводит лишь обобщенные записи.
Итоги можно подводить по всей таблице целиком (с использованием ключевого слова «ОБЩИЕ»), по нескольким полям, по полям с иерархической структурой (ключевые слова «ИЕРАРХИЯ», «ТОЛЬКО ИЕРАРХИЯ»). При подведении итогов не обязательно использовать агрегатные функции.
Рассмотрим пример, аналогичный примеру выше с использованием группировки. В данном случае результат запроса вернет не только сгруппированные поля, но и детальные записи.
ВЫБРАТЬ
Цены.Номенклатура.ВидНоменклатуры КАК ВидНоменклатуры,
Цены.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ИТОГИ
МАКСИМУМ(Цена)
ПО
ВидНоменклатуры
Данный оператор схож с оператором «ГДЕ», но используется только для агрегатных функций. Остальные поля, кроме используемых этим оператором, должны быть сгруппированы. Оператор «ГДЕ» не применим для агрегатных функций.
В рассмотренном ниже примере отбираются максимальные цены номенклатуры, если они превышают 1000, сгруппированные по виду номенклатуры.
ВЫБРАТЬ
МАКСИМУМ(Цены.Цена) КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
СГРУППИРОВАТЬ ПО
Цены.Номенклатура.ВидНоменклатуры
ИМЕЮЩИЕ
МАКСИМУМ(Цены.Цена) > 1000
Оператор «УПОРЯДОЧИТЬ ПО» сортирует результат запроса. Для того, чтобы гарантированно выводить записи в постоянном порядке, используется АВТОУПОРЯДОЧИВАНИЕ. Примитивные типы сортируются по обычным правилам. Ссылочные типы сортируются по GUID.
Пример получения списка сотрудников, отсортированного по наименованию:
ВЫБРАТЬ
Сотрудники.Наименование КАК Наименование
ИЗ
Справочник.Сотрудники КАК Сотрудники
УПОРЯДОЧИТЬ ПО
Наименование
АВТОУПОРЯДОЧИВАНИЕ
Для создания запросов с 1С есть очень удобный встроенный механизм – конструктор запросов. Он содержит следующие основные вкладки:
Текст самого запроса можно просмотреть, нажав в левом нижнем углу на кнопку «Запрос». В данной форме его можно откорректировать вручную или скопировать.
Для быстрого просмотра результата запроса в режиме «Предприятие», либо отладки сложных запросов используется . В ней пишется текст запроса, устанавливаются параметры, и показывается его результат.
Скачать консоль запросов можно на диске ИТС, либо по .
; Вложенные запросы (в разработке).
Поступление товаров и услуг и Возврат товаров поставщику за указанный период.
Новые вкладки: Объединения/Псевдонимы.
Конструктор запросов 1с позволяет создавать объединения запросов. При их помощи можно последовательно выводить в результат данные, полученные из нескольких запросов, не используя при этом связи. Единственной условие для объединения — одинаковый набор полей в каждом отдельном запросе.
В конструкторе перейдем на вкладку Объединения/Псевдонимы . Она предназначена для создания объединений запросов и для задания псевдонимов для полей запроса. Псевдонимы полей нужны, если вас не устраивают стандартные имена полей базы данных. Если поле запроса состоит только из поля таблицы базы данных, то псевдоним для него не обязателен. Если же при создании поля вы использовали функции языка запросов, то псевдоним для такого поля обязателен. Для таких полей конструктор запросов создает стандартные псевдонимы Поле1…ПолеN , эти псевдонимы можно заменить на те, которые удобны для вас.
Рассмотрим разделы вкладки Объединения /Псевдонимы :
Разберем решение задачи, приведенной в начале урока. Напомню условия:
Задача: выбрать запросом все проведенные документы Поступление товаров и услуг и Возврат товаров поставщику за указанный период.
В итоге у нас получится запрос со следующим текстом.