1с разное количество полей в объединяемых запросах исправить
Войдите как ученик, чтобы получить доступ к материалам школы
Язык запросов 1С 8.3 для начинающих программистов: объединение
Автор уроков и преподаватель школы: Владимир Милькин
Объединение в запросах
В языке запросов имеется возможность объединять несколько запросов. При этом записи, полученные с помощью каждого из объединяемых запросов, будут собраны в один результат запроса.
Как всегда начнём с примера.
Пусть требуется написать запрос, который возвращает все названия цветов и вкусов в одной таблице.
Цвета мы умеем выбирать так:
А вот, чтобы объединить эти два результата в один как раз и потребуется операция объединения:
Обратите внимание на то, что операция объединения вовсе не гарантирует, что элементы будут идти в каком-то определённом порядке. В общем случае они могут следовать друг за другом как угодно, поэтому если важен порядок необходимо как и всегда указывать его явно (через секцию УПОРЯДОЧИТЬ).
Требования к запросам, участвующим в объединении
У объединяемых запросов должно быть одинаковое количество полей . Иначе мы получим такую ошибку:
В объединяемых запросах соответствующие друг другу (по порядку) поля должны иметь одинаковый тип . Но это требование, в отличие от предыдущего, не является обязательным. Если соответствующие друг другу поля имеют разный тип, то поле результата будет иметь СОСТАВНОЙ тип, который разбирался нами на одном из прошлых уроков:
Несмотря на то, что поле первого запроса имеет тип СТРОКА, а второго ЧИСЛО, нам удалось объединить их в одну таблицу. Но тип результатирующего поля стал составным и нам придётся учитывать это в дальнейшем. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь.
Кстати, вы заметили, что поле итоговой таблицы (после объединения) называется Наименование, а не Калорийность? Эта такое правило : поля итоговой таблицы всегда имеют названия совпадающие с именами описанными в первом из объединяемых запросов.
Объединение более двух запросов
При объединении каждый запрос собирает данные независимо, а уже затем результаты объединяются в один. Количество объединяемых запросов не ограничено.
Напишем объединение трёх запросов:
Повторяющиеся строки
Давайте объединим один и тот же запрос сам с собой:
Обратите внимание на то, что мы объединили две одинаковые таблицы цветов, а в результате каждый цвет встречается ровно один раз.
Всё потому, что по умолчанию при объединении запросов полностью одинаковые строки в результате запроса, заменяются одной. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь.
Если требуется, чтобы были оставлены в том числе одинаковые строки (дубли), необходимо указать ключевое слово ВСЕ:
Упорядочивание при объединении
Пытаться отдельно упорядочивать результаты запросов, участвующих в объединении не имеет смысла. Поэтому при попытке написать:
Упорядочивать можно только результат объединения :
Обратите внимание на то, что секция УПОРЯДОЧИТЬ ПО в данном случае относится не к последнему запросу, а уже к результату объединения запросов.
Подведение итогов при объединении
Пытаться отдельно подводить итоги по запросам, участвующим в объединении, также не имеет смысла. Поэтому при попытке написать:
Подводить итоги можно только по результату объединения :
Обратите внимание на то, что секция ИТОГИ ПО в данном случае относится не к последнему запросу, а уже к результату объединения запросов.
Рабочий пример
Давайте решим такую задачу. Требуется вывести для каждого элемента справочника Еда закупленное и проданное количество за весь период.
Поступление еды у нас в базе происходит документом ПоступлениеЕды. У документа есть табличная часть Еда, с реквизитами Номенклатура и Количество.
Сначала выберем все строки табличной части Еда из всех поступлений:
Теперь сгруппируем этот результат по еде с суммированием количества:
Аналогичным образом получим продажу еды из документов ПродажаЕды:
Осталось объединить эти два запроса:
Обратите внимание на то, как мы дополнили оба запроса ещё одним полем СУММА(0). Мы вынуждены были сделать это, так как оба запроса содержат по два поля, а объединение запросов должно содержать три поля. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь. Если бы мы не сделали этого, а написали просто:
В отличии от соединения таблиц, объединение производит присоединение данных к таблице с абсолютно одинаковым количеством колонок.
Возможно 2 варианта объединения:
При этом не производятся никакие проверки — это простая операция:
- Значения полей могут быть различного типа.
- Если количество полей различно, система выдаст ошибку — «Разное количество полей объединяемых в запросах».
- Первая таблица считается главной — именно из нее берутся имена полей результирующей выборки (конструктор удаляет псевдонимы, если они присутствуют, автоматически).
- Нет явного ограничения количества таких объединений.
Внешний вид объединения в конструкторе запросов:
Добавляются эти запросы на вкладке «Объединение/Псевдонимы»
Здесь же можно управлять режимом объединения (флажки «Без дублей»):
При добавлении поля в одну из таких таблиц (в режиме конструктора), в остальные таблицы добавляется значение NULL — автоматического подбора по именам источника не происходит. В этом случае приходится подредактировать текст запроса руками.
Если в объединении присутствуют неопределенное или пустые поля, такая процедура может происходить не полностью (режим ОБЪЕДИНИТЬ) — не все строки включаются в результат. Такое было замечено однократно, возможно, было связано с какой-то версией платформы, либо с использованием агрегирующих функции запроса.
Объединение больших выборок может влиять на производительность запроса: так как ведётся поиск дублей строк.
Реклама — виновница того, что народ выбрал себе в вожди Гитлера. Реклама призвана убеждать граждан, что ситуация нормальна, когда она катастрофически ненормальна.
— Фредерик Бегбедер
Язык запросов 1С позволяет сделать соединения двух и более таблиц. Под соединением понимается состыковка двух таблиц по ключевым полям.
Всего 4 вида соединений
- Полное соединение — в результате получается одна таблица, содержащая все выбранные колонки, обычно число строк равняется количеству совпадающих по ключам строк плюс все несовпадающие.
- Левое соединение — на выходе будет выбранные поля обеих таблиц (полностью первой и совпадающие по ключам соединения второй), количество строк всегда больше или равно количеству в первой таблице (условие соединения может вызывать дубли, если в правой таблице поля соединения дублируются))
- Правое соединение — обратное левому (главная таблица вторая).
- Внутреннее соединение — количество строк такой таблице равно количеству совпадающих по условию объединения строк.
Возможно выборка всех полей объединяемых таблиц (обращение к ним по псевдониму), но если соединение не внутреннее, то в отсутствующем поле будет значение NULL, поэтому следует рассматривать его особым образом или подменять значение через функцию ЕстьNULL().
Условием соединения может быть и простое условие «ИСТИНА», которое соединит таблицы с значительным увеличением количества строк (каждая строка соединится с каждой строкой второй таблицы).
Различные виды объединений используются для своих своих задач и целей:
Левое, когда требуется к одной основной таблице добавить какие-то свойства из другой. Например к списку контрагентов добавить задолженность, банковские реквизиты, фио руководителей.
При формировании такого запроса, следует добиться отсутствия в правой таблице дублей строк, этому способствует использование временных таблиц или вложенных. В противном случае в левой таблице появятся дубли контрагентов, что будет мешать верному восприятию.
Если задвоения будут в динамическом списке, это может вызвать ошибку дублирования ключевого поля.
Внутреннее соединение служит для максимально ограничения выборки, например: получить контрагентов у которых были реализации в выбранный период.
В управлении торговлей для решения такой задачи соединение не так уж важно, достаточного было бы обратится к регистру «Продажи», а вот в «Бухгалтерии предприятия» соединение будет более верным решением: ведь мы не знаем заранее какой счет учета используется для продажи, могут быть созданные пользователем субсчета (это даст более короткое решение, но все зависит от ситуации — не обязательно же оно должно быть универсальным).
При необходимости соединения более двух таблиц производится цепочка соединений — сначала производится первое соединение, а с результатом производится соединение последующих таблиц.
Требование к таким таблицам:
- различные их псевдонимы и непересечение имен ключей и таблиц.
- При множественных соединениях допустимо комбинация нескольких видов соединений.
Как задать соединение в конструкторе запроса:
Шаг 1: выберите необходимые таблицы, которые возможно соединить
- Это производится на вкладке таблицы конструктора запроса.
- Далее можно сразу выбрать нужные поля.
Шаг 2: Переходим на вкладку «Связи»
- Для простых таблиц, где явно определяются связи, система их объединит самостоятельно
- В данном случае левое соединение
- Чтобы получить внутреннее: снимаются оба флажка «Все» — тогда в таблицу попадут только контрагенты у которых есть контактные лица
- Чтобы получить полное: устанавливаются оба флажка
- Если требуется соединение по двум условиям добавляется еще одна строка связи.
Вид соединения задается соответствующим словом перед «СОЕДИНЕНИЕ», остальной синтаксис идентичен. Правое не рассматриваем, так как конструктор запроса автоматически поменяет таблицы местами и сделает левое соединение.
Пример текста запроса «Внутреннее соединение»:
ВЫБРАТЬ
Контрагенты . Ссылка ,
КонтактныеЛица . Ссылка КАК КонтактноеЛицо
ИЗ
Справочник . Контрагенты КАК Контрагенты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник . КонтактныеЛица КАК КонтактныеЛица
ПО Контрагенты . ОсновноеКонтактноеЛицо = КонтактныеЛица . Ссылка
И ( Контрагенты . ЮрФизЛицо = ЗНАЧЕНИЕ ( Перечисление . ЮрФизЛицо . ЮрЛицо )
Пример левого соединения
ВЫБРАТЬ
Контрагенты . Ссылка ,
КонтактныеЛица . Ссылка КАК КонтактноеЛицо
ИЗ
Справочник . Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ Справочник . КонтактныеЛица КАК КонтактныеЛица
ПО Контрагенты . ОсновноеКонтактноеЛицо = КонтактныеЛица . Ссылка
И ( Контрагенты . ЮрФизЛицо = ЗНАЧЕНИЕ ( Перечисление . ЮрФизЛицо . ЮрЛицо )
Пример «полного соединения»
ВЫБРАТЬ
Контрагенты . Ссылка ,
КонтактныеЛица . Ссылка КАК КонтактноеЛицо
ИЗ
Справочник . Контрагенты КАК Контрагенты
ПОЛНОЕ СОЕДИНЕНИЕ Справочник . КонтактныеЛица КАК КонтактныеЛица
ПО Контрагенты . ОсновноеКонтактноеЛицо = КонтактныеЛица . Ссылка
И ( Контрагенты . ЮрФизЛицо = ЗНАЧЕНИЕ ( Перечисление . ЮрФизЛицо . ЮрЛицо )
На что следует обратить внимание:
- Как уже было сказано на пустые значения при левом или полном соединении.
- С осторожностью используйте полные соединения и срезы последних из регистров, это может сильно замедлить выборку.
- При использовании конструктора обращайте внимание на соединяемые таблицы иногда он может подставить в левую таблицу не ожидаемое вами, если это упустить вначале придется затем исправлять руками или через правое (одна секунда внимания сбережет время в дальнейшем).
- Если вы выберете 2 таблицы, но не укажите связи, то получится неявное полное соединение (таблицы будут через «запятую»). Пример:
ВЫБРАТЬ
Контрагенты . Ссылка ,
КонтактныеЛица . Ссылка КАК КонтактноеЛицо
ИЗ
Справочник . Контрагенты КАК Контрагенты ,
Справочник . КонтактныеЛица КАК КонтактныеЛица
Всякая реклама есть средство отделить человека от его денег.
— Джон Пристли
6. При группировке требуется, чтобы значения в столбце Цена просуммировались. Для этого подойдёт агрегатная функция
7. При группировке требуется найти максимальное значение в столбце Цена. Для этого подойдёт агрегатная функция
8. При группировке требуется найти количество различных значений в столбце Цена. Для этого подойдёт агрегатная функция
Домашнее задание
а) Напишите запрос, который группирует цвета, встречающиеся среди еды:
Эталонное решение. Сначала пишем код сами, проверяем на компьютере (как и все примеры программ из уроков), только потом сверяемся
Войдите на сайт как ученик
б) Напишите запрос, который находит максимальную калорийность еды для каждого цвета:
Эталонное решение. Сначала пишем код сами, проверяем на компьютере (как и все примеры программ из уроков), только потом сверяемся
Войдите на сайт как ученик
в) Напишите запрос, который группирует еду по вкусу и цвету:
Эталонное решение. Сначала пишем код сами, проверяем на компьютере (как и все примеры программ из уроков), только потом сверяемся
Войдите на сайт как ученик
Если всё ещё не совсем понятно, как это группировать сразу по двум полям, то давайте разберём такой пример:
Пусть у нас есть следующая таблица:
И мы хотим сгруппировать её по полю А. Это значит, что мы возьмём все строчки с одинаковым полем А и "слепёшим" (свернём, сгруппируем) в одну.
Поэтому на выходе будет:
А теперь пусть есть таблица:
А Б
1 3
1 3
1 5
2 4
2 4
2 6
И мы группируем сразу по А и Б. Это значит, что мы возьмём все строчки с одинаковыми А и Б и "слепёшим" (свернём, сгруппируем) в одну.
Поэтому на выходе будет:
А Б
1 3
1 5
2 4
2 6
А теперь замените А на Вкус, а Б на Цвет.
г) Напишите запрос, который выведет количество цветов в таблице Справочник.Цвета:
Эталонное решение. Сначала пишем код сами, проверяем на компьютере (как и все примеры программ из уроков), только потом сверяемся
Войдите на сайт как ученик
Для учеников
Уровень сложности уроков идёт снизу вверх. Поэтому старайтесь заниматься последовательно, ничего не пропуская и не откладывая на потом.
Обязательно выполняйте все тесты и домашние задания, старайтесь улучшить свои результаты. Сверяйтесь с эталонными решениями только после самостоятельного выполнения задач.
На вопросы учеников — отвечаю по почте, но прежде загляните в ЧАВО (ссылка) .
Объединение в запросах 1С 8.3 - это выполнение двух разных запросов и объединение их результатов в один общий.
Требования к объединяемым таблицам это одинаковое количество полей. Если соответствующие друг другу поля в разных таблицах имеют один и тот же тип, то итоговое поле будет тоже иметь этот тип, если же тип разный, то итоговое поле будет иметь составной тип данных. Упорядочивать (группировать, подводить итоги) таблиц имеет смысл только после их объединения в конечный результат.
- ОБЪЕДИНИТЬ - конструкция объединяет два результата и группирует повторяющие строки (дубликаты строк из разных запросов будут удалены).
- ОБЪЕДИНИТЬ ВСЕ - также объединяет два результата, но строки в результате автоматически не группирует (дубликаты строк из разных запросов удалены не будут). Используется для ускорения выполнения запроса, когда заведомо невозможно получения одинаковых строк.
✔ Объединение двух таблиц в одну
&НаСервере
Процедура ОбъединениеДвухТаблицВОдну ()
// Объединение "Контрагенты" и "Классификатор Сроков Полезного Использования"
Запрос = Новый Запрос ( "ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.Контрагенты
|
|ОБЪЕДИНИТЬ
|
|ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.КлассификаторСроковПИ" );
РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;
&НаСервере
Процедура ОбъединениеБолееДвухТаблицВОдну ()
// Объединение трех таблиц Контрагенты, Материалы и Страны
Запрос = Новый Запрос ( "ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.Контрагенты
|
|ОБЪЕДИНИТЬ
|
|ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.Материалы
|
|ОБЪЕДИНИТЬ
|
|ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.Страны" );
РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;
&НаСервере
Процедура СохранениеДубликатовВРезультатеОбъединенияТаблиц ()
// Объединение таблицы "Классификатор Сроков Полезного Использования" саму с собой так,
// чтобы дубликаты, которые получились сами по себе - остались в выборке (задвоились)
Запрос = Новый Запрос ( "ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.КлассификаторСроковПИ
|
|ОБЪЕДИНИТЬ ВСЕ
|
|ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.КлассификаторСроковПИ" );
РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;
&НаСервере
Процедура ОбъединениеТаблицСРазнымКоличествомПолей ()
// Создание дополнительных (недостающих) полей и заполнение их значениями
Запрос = Новый Запрос ( "ВЫБРАТЬ
| Наименование,
| Вес
|ИЗ
| Справочник.Материалы
|
|ОБЪЕДИНИТЬ
|
|ВЫБРАТЬ
| Наименование,
| 0
|ИЗ
| Справочник.Контрагенты" );
РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;
&НаСервере
Процедура УпорядочиваниеПриОбъединенииТаблиц ()
// Упорядочивание таблиц Материалы и КлассификаторСроковПИ по наименованию (алфавиту)
Запрос = Новый Запрос ( "ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.Материалы
|
|ОБЪЕДИНИТЬ
|
|ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.КлассификаторСроковПИ
|
|УПОРЯДОЧИТЬ ПО
| Наименование УБЫВ" );
РезультатЗапроса = Запрос . Выполнить (); Записи = РезультатЗапроса . Выбрать ();
Пока Записи . Следующий () Цикл
// Обход результата запроса по каждой записи в полученной выборке
КонецЦикла;
Читайте также: