Куперс

Бухучет и анализ

Как объединить две таблицы в 1С?

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

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

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

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

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

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

Запрос.Текст =
«ВЫБРАТЬ
| Номенклатура.Товар,
| Номенклатура.Цвет КАК ЦветНоменклатура,
| Остатки.Цвет КАК ЦветОстатки,
| Остатки.Количество
|ИЗ
| Номенклатура КАК Номенклатура
| ЛЕВОЕ СОЕДИНЕНИЕ Остатки КАК Остатки
| ПО Номенклатура.Товар = Остатки.Товар»;
Для стула не нашлось сопоставлений из таблицы остатков, поэтому поля заполнились значениями NULL, которые обязательно нужно обработать функцией ЕСТЬNULL, см. Функции языка запросов 1С 8.
Левое соединение работает примерно как цикл в цикле — берется первая запись из левой таблицы и пробегаются все записи из правой на предмет удовлетворения условию связи. Затем берется вторая запись из левой таблицы и т.д. Если вдруг условию связи удовлетворяют несколько записей из правой таблицы, то в результирующую таблицу будет добавлено несколько строк (по количеству удачных связей).Как видим, полученная таблица не информативна, данные не отражают реальную суть, поэтому лучше связать эти таблицы по двум полям: Товар и Цвет, только на этот раз обработаем NULLы:
Запрос.Текст =
«ВЫБРАТЬ
| Номенклатура.Товар,
| Номенклатура.Цвет,
| ЕСТЬNULL(Остатки.Количество, 0) КАК Количество
|ИЗ
| Номенклатура КАК Номенклатура
| ЛЕВОЕ СОЕДИНЕНИЕ Остатки КАК Остатки
| ПО Номенклатура.Товар = Остатки.Товар
| И Номенклатура.Цвет = Остатки.Цвет»;

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

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

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

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

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

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

Полное соединение передаст в результат все записи из обоих таблиц, те записи, которые удовлетворяет условию связи — будут связаны, те записи, которые не удовлетворяют условию связи все равно окажутся в результате запроса, но с частью NULLовых полей. Полное это как бы левое и правое соединения в одном.
Задач на эту тему может быть много, давайте попробуем решить одну из них. Наша организация является дилером 2 мебельных фабрик: «Заря» и «Рассвет». Ассортимент со стоимостью каждой из фабрик хранится в разных таблицах. Необходимо составить единый прайс лист, причем брать в него продукцию по минимальной цене:
Применим полное соединение с выборкой всех полей, связывать будем по товару:
Запрос.Текст =
«ВЫБРАТЬ
| НоменклатураЗаря.Товар КАК ТоварЗаря,
| НоменклатураЗаря.Цена КАК ЦенаЗаря,
| НоменклатураРассвет.Товар КАК ТоварРассвет,
| НоменклатураРассвет.Цена КАК ЦенаРассвет
|ИЗ
| НоменклатураЗаря КАК НоменклатураЗаря
| ПОЛНОЕ СОЕДИНЕНИЕ НоменклатураРассвет КАК НоменклатураРассвет
| ПО НоменклатураЗаря.Товар = НоменклатураРассвет.Товар»;

Это не совсем то, что нам нужно, давайте соединим поле товар в одно и обработаем NULLы:
Запрос.Текст =
«ВЫБРАТЬ
//конструкция ЕСТЬNULL рассматривалась в разделе функции языка запросов
| ЕСТЬNULL(НоменклатураЗаря.Товар, НоменклатураРассвет.Товар) КАК Товар,
//если цена не определена, то инициализируем ее
//почему 1000000 см. пояснения ниже
| ЕСТЬNULL(НоменклатураЗаря.Цена, 1000000) КАК ЦенаЗаря,
| ЕСТЬNULL(НоменклатураРассвет.Цена, 1000000) КАК ЦенаРассвет
|ИЗ
| НоменклатураЗаря КАК НоменклатураЗаря
| ПОЛНОЕ СОЕДИНЕНИЕ НоменклатураРассвет КАК НоменклатураРассвет
| ПО НоменклатураЗаря.Товар = НоменклатураРассвет.Товар»;
Осталось только выбрать минимальную цену. Итоговый тект запроса будет выглядеть следующим образом:
Запрос.Текст =
«ВЫБРАТЬ
| ЕСТЬNULL(НоменклатураЗаря.Товар, НоменклатураРассвет.Товар) КАК Товар,
| ВЫБОР
| КОГДА ЕСТЬNULL(НоменклатураЗаря.Цена, 1000000) > ЕСТЬNULL(НоменклатураРассвет.Цена, 1000000)
| ТОГДА ЕСТЬNULL(НоменклатураРассвет.Цена, 1000000)
| ИНАЧЕ ЕСТЬNULL(НоменклатураЗаря.Цена, 1000000)
| КОНЕЦ КАК Цена
|ИЗ
| НоменклатураЗаря КАК НоменклатураЗаря
| ПОЛНОЕ СОЕДИНЕНИЕ НоменклатураРассвет КАК НоменклатураРассвет
| ПО НоменклатураЗаря.Товар = НоменклатураРассвет.Товар»;

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

Всего их пять

ПРИСОЕДИНИТЬСЯ

s. Они есть :

  1. 1. ПРИСОЕДИНЯЙТЕСЬ или ВНУТРЕННЕЕ СОЕДИНЕНИЕ 2. ВНЕШНЕЕ СОЕДИНЕНИЕ 2.1 ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ ИЛИ ЛЕВОЕ СОЕДИНЕНИЕ 2.2 ПРЯМОЕ НАРУЖНОЕ СОЕДИНЕНИЕ или ПРЯМОЕ СОЕДИНЕНИЕ 2.3 ПОЛНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ или ПОЛНОЕ СОЕДИНЕНИЕ 3. ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ 4. СОКРАЩЕНИЕ СОЕДИНЕНИЯ 5. САМОСОЕДИНЕНИЕ

1. Присоединиться или присоединиться

В такого рода

ПРИСОЕДИНИТЬСЯ

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

Другими словами,

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

основан на единственном факте, что: ТОЛЬКО совпадающие записи в ОБОИХ таблицах ДОЛЖНЫ быть перечислены.

Обратите внимание, что

ПРИСОЕДИНИТЬСЯ

без каких-либо других

ПРИСОЕДИНИТЬСЯ

ключевые слова (как

ВНУТРЕННИЙ

,

ВНЕШНИЙ

,

ОСТАВИЛ

и т. д.)

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

, Другими словами,

ПРИСОЕДИНИТЬСЯ

является синтаксическим сахаром для

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

(видеть:

Разница между JOIN и INNER JOIN

).

2. НАРУЖНОЕ СОЕДИНЕНИЕ:

НАРУЖНОЕ СОЕДИНЕНИЕ

извлекает

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

Существует три вида внешнего соединения:

2.1 ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ или ЛЕВОЕ СОЕДИНЕНИЕ

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

ЗНАЧЕНИЕ NULL

ценности.

2.2 ПРАВИЛЬНОЕ ВСТУПЛЕНИЕ или ПРАВИЛЬНОЕ СОЕДИНЕНИЕ

Эта

ПРИСОЕДИНИТЬСЯ

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

ЗНАЧЕНИЕ NULL

ценности.

2.3 ПОЛНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ или ПОЛНОЕ СОЕДИНЕНИЕ

Эта

ПРИСОЕДИНИТЬСЯ

комбинаты

ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ

а также

ПРАВО НАРУЖНОЕ СОЕДИНЕНИЕ

, Он возвращает строки из любой таблицы при выполнении условий и возвращает

ЗНАЧЕНИЕ NULL

значение, когда нет совпадения.

Другими словами,

НАРУЖНОЕ СОЕДИНЕНИЕ

основан на том факте, что: ДОЛЖНЫ быть перечислены ТОЛЬКО совпадающие записи в ОДНОЙ из таблиц (ВПРАВО или ВЛЕВО) или ОБА из таблиц (ПОЛНАЯ).

  1. Обратите внимание, что `OUTER JOIN` является ослабленной формой` INNER JOIN`.

3. ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ:

Он основан на двух условиях:

  • JOIN производится для всех столбцов с одинаковыми именами на равенство.
  • Удаляет дубликаты столбцов из результата.

Похоже, что это скорее теоретический характер, и в результате (вероятно) большинство СУБД даже не заботятся об этом.

4. CROSS JOIN:

Это декартово произведение двух задействованных таблиц. Результатом

CROSS JOIN

не будет иметь смысла в большинстве ситуаций. Более того, нам это вообще не понадобится (или, если быть точным, нужно меньше всего).

5. САМ СОЕДИНЯЙТЕСЬ:

Это не другая форма

ПРИСОЕДИНИТЬСЯ

скорее это

ПРИСОЕДИНИТЬСЯ

(

ВНУТРЕННИЙ

,

ВНЕШНИЙ

и т. д.) стола для себя.

СОЕДИНЕНИЯ на основе операторов

В зависимости от оператора, используемого для

ПРИСОЕДИНИТЬСЯ

оговорка, может быть два типа

ПРИСОЕДИНИТЬСЯ

s. Они есть

  • Equi JOIN
  • Тета ПРИСОЕДИНЯЙТЕСЬ

1. Equi JOIN:

Для чего угодно

ПРИСОЕДИНИТЬСЯ

тип (

ВНУТРЕННИЙ

,

ВНЕШНИЙ

и т. д.), если мы используем ТОЛЬКО оператор равенства (=), то мы говорим, что

ПРИСОЕДИНИТЬСЯ

является

EQUI JOIN

,

2. Тета ПРИСОЕДИНЯЙТЕСЬ:

Это так же как

EQUI JOIN

но он позволяет все другие операторы, такие как>, = и т. д.

Многие считают, что EQUI JOIN и Theta JOIN похожи на INNER, OUTER и т. Д. JOIN. Но я твердо верю, что это ошибка и делает идеи расплывчатыми. Поскольку INNER JOIN, OUTER JOIN и т. Д. Все связаны с таблицами и их данными, тогда как EQUI JOIN и THETA JOIN связаны только с операторами, которые мы используем в первом.

Опять же, многие считают NATURAL JOIN своего рода «своеобразным» EQUI JOIN. На самом деле, это правда, из-за первого условия, которое я упомянул для NATURAL JOIN. Однако мы не должны ограничивать это просто ЕСТЕСТВЕННЫМИ СОЕДИНЕНИЯМИ. ВНУТРЕННИЕ СОЕДИНЕНИЯ, ВНЕШНИЕ СОЕДИНЕНИЯ и т. Д. Тоже могут быть EQUI JOIN.

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

Допустим в нашей системе факты покупки и продажи товара регистрируются документами Приход и Расход соответственно. Контрагент может являться как покупателем, так и поставщиком. Зачет задолженности может производится поставкой товара:

Чтобы подсчитать общую задолженность контрагента необходимо сложить сумму всех расходов по этому контрагенту и вычесть сумму всех приходов от этого же контрагента, проще всего это сделать с помощью оператора ОБЪЕДИНИТЬ ВСЕ:


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

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

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

Запрос = Новый Запрос;
Запрос.Текст =
«ВЫБРАТЬ
| Расход.Контрагент,
| СУММА(Расход.Сумма) КАК Долг
|ПОМЕСТИТЬ ВТ_ПриходРасход
|ИЗ
| Документ.Расход КАК Расход
|
|СГРУППИРОВАТЬ ПО
| Расход.Контрагент
|
|ОБЪЕДИНИТЬ ВСЕ
|
|ВЫБРАТЬ
| Приход.Контрагент,
| СУММА(-Приход.Сумма)
|ИЗ
| Документ.Приход КАК Приход
|
|СГРУППИРОВАТЬ ПО
| Приход.Контрагент
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_ПриходРасход.Контрагент,
| СУММА(ВТ_ПриходРасход.Долг) КАК Долг
|ИЗ
| ВТ_ПриходРасход КАК ВТ_ПриходРасход
|
|СГРУППИРОВАТЬ ПО
| ВТ_ПриходРасход.Контрагент»;

Результат:

Требования к объединению запросов

При объединении двух запросов количество полей у них должно быть одинаковым, если в каком либо из запросов не хватает полей, то их надо добавить в виде констант. Обратимся к примеру выше, пусть в документе расход также есть поле скидка, которое уменьшает сумму долга контрагента, но в документе приход нет никаких скидок. Как быть в этом случае? Так:

Запрос = Новый Запрос;
Запрос.Текст =
«ВЫБРАТЬ
| Расход.Контрагент,
| СУММА(Расход.Сумма) КАК Долг,
| СУММА(Расход.Скидка) КАК Скидка
|ИЗ
| Документ.Расход КАК Расход
|
|СГРУППИРОВАТЬ ПО
| Расход.Контрагент
|
|ОБЪЕДИНИТЬ ВСЕ
|
|ВЫБРАТЬ
| Приход.Контрагент,
| СУММА(-Приход.Сумма),
//добавляем нулевое поле скидка
| 0
|ИЗ
| Документ.Приход КАК Приход
|
|СГРУППИРОВАТЬ ПО
| Приход.Контрагент»;

Осталось вычесть скидку и сгруппировать.

Также важен порядок. Поля будут объединятся именно в том порядке в котором они указаны в секциях ВЫБРАТЬ обоих запросов. Применительно к предыдущему примеру — поменяем местами поля скидка и сумма в выборке приходов:

Запрос = Новый Запрос;
Запрос.Текст =
«ВЫБРАТЬ
| Расход.Контрагент,
| СУММА(Расход.Сумма) КАК Долг,
| СУММА(Расход.Скидка) КАК Скидка
|ИЗ
| Документ.Расход КАК Расход

Объединение таблиц в запросах 1С

рубрики: Запросы | Дата: 1 февраля, 2016

Рассмотрим объединение таблиц в запросах 1С8. В этом случае итоговая таблица формируется путем размещенеия записей одной или нескольких таблиц под записями исходной таблицы. Объединение таблиц осуществляется с помощью функции ОБЪЕДИНИТЬ языка запросов 1С.
Эта функция используется в двух вариантах: ОБЪЕДИНИТЬ и ОБЪЕДИНИТЬ ВСЕ. Различие между ними в том, что при использовании ОБЪЕДИНИТЬ ВСЕ в итоговую таблицу добавляются все записи из таблиц, которые мы объединяем, а при использовании ОБЪЕДИНИТЬ в случае если в таблицах есть идентичные строки, то в итоговой таблице будет только одна строка, т.е. дубли строк в этом случае удаляются.

Продемонстрирую это на примере.
Откроем консоль запросов и создадим небольшой запрос, который создаст нам элементарную таблицу из пары колонок и одной строки:

ВЫБРАТЬ «001» КАК Код, «professia1c.ru» КАК ИмяСайта

В результате выполнения запроса получаем вот такую таблицу

Код ИмяСайта
001 professia1c.ru

А теперь добавим в нашу таблицу еще пару строк

ВЫБРАТЬ «001» КАК Код, «professia1c.ru» КАК ИмяСайта ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ «002», «1c.ru» ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ «001» КАК Код, «professia1c.ru» КАК ИмяСайта

Соответственно результат:

Код ИмяСайта
001 professia1c.ru
002 1c.ru
001 professia1c.ru

Здесь мы видим, что первая и третья строка дублируются.
А теперь заменим функцию ОБЪЕДИНИТЬ ВСЕ на ОБЪЕДИНИТЬ перед третьей строкой:

ВЫБРАТЬ «001» КАК Код, «professia1c.ru» КАК ИмяСайта ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ «002», «1c.ru» ОБЪЕДИНИТЬ ВЫБРАТЬ «001» КАК Код, «professia1c.ru» КАК ИмяСайта

В итоге видим, что дубли строк у нас исчезли

Код ИмяСайта
001 professia1c.ru
002 1c.ru

Теперь откроем последний запрос в конструкторе и перейдем на закладку Объединения/Псевдонимы, где как раз и отражается факт использования функции ОБЪЕДИНИТЬ.

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

Объединение таблиц с использованием конструктора запросов рассматривается в отдельной статье.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Наверх