Лекция3.Запросы.Связи

1.

Запросы
Виды соединений

2.

3.

Количество записей= Количество записей 1 таблицы * Количество записей 2
таблицы

4.

5.

6.

Соединения используются, чтобы сопоставить (соединить) строки одной
таблицы строкам другой таблицы по определенному признаку.
-------------------------------------------|Люди
|
| Характеристики
|
--------------------------------------------|ИМЯ
|
| ИМЯ
ВОЗРАСТ |
--------------------------------------------|Алена
|
| Алена
21
|
|Алефтина |
| Алефтина
18
|
|Владимир |
| Павел
25
|
|Владислав |
| Петр
30
|
|
|
| Порфирий
50
|
---------------------------------------------

7.

Результат внутреннего соединения по имени:
ИмяИзТаблицы1 ИмяИзТаблицы2 ВозрастИзТаблицы2
Алена
Алена
21
Алефтина
Алефтина
18

8.

Запрос = Новый Запрос(
"ВЫБРАТЬ
| Люди.Наименование КАК ИмяИзТаблицы1,
| Характеристики.Наименование КАК ИмяИзТаблицы2,
| Характеристики.Возраст КАК ВозрастИзТаблицы2
|ИЗ
| Справочник.Люди КАК Люди
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ
| Справочник.Характеристики КАК Характеристики
| ПО
| Люди.Наименование = Характеристики.Наименование"
);

9.

Получить данные по товарам из табличной части товары документа
РеализацияТоваровИУслуг и клиентах по документу
РеализацияТоваровИУслуг. Эту же задачу можно выполнить с
помощью разыменования полей. Выполним с помощью
внутреннего соединения:
1. С помощью консоли запросов
2. С помощью СКД

10.

С помощью консоли запросов

11.

12.

13.

14.

С помощью СКД

15.

16.

17.

18.

19.

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

20.

Левое соединение - это внутреннее соединение + строки из левой таблицы,
для которых не найдены соответствия.
------------|Люди
|
------------|ИМЯ
|
------------|Алена
|
|Алефтина |
|Владимир |
|Владислав |
|
|
-------------
-------------------------------| Характеристики
|
--------------------------------| ИМЯ
ВОЗРАСТ |
--------------------------------| Алена
21
|
| Алефтина
18
|
| Павел
25
|
| Петр
30
|
| Порфирий
50
|
---------------------------------

21.

Результат левого внешнего соединения по имени:
ИмяИзТаблицы1 ИмяИзТаблицы2 ВозрастИзТаблицы2
Алена
Алена
21
Алефтина
Алефтина
18
Владимир
NULL
NULL
Владислав
NULL
NULL

22.

Запрос = Новый Запрос(
"ВЫБРАТЬ
| Люди.Наименование КАК ИмяИзТаблицы1,
| Характеристики.Наименование КАК ИмяИзТаблицы2,
| Характеристики.Возраст КАК ВозрастИзТаблицы2
|ИЗ
| Справочник.Люди КАК Люди
| ЛЕВОЕ СОЕДИНЕНИЕ
| Справочник.Характеристики КАК Характеристики
| ПО
| Люди.Наименование = Характеристики.Наименование"
);

23.

Функция ЕСТЬNULL
В языке запросов реализована функция ЕСТЬNULL(),
предназначение которой - заменять выражение на
другое выражение в случае, если выражения имело
значение NULL. Синтаксическая диаграмма данной функции
выглядит так:
ЕСТЬNULL(<Проверяемое выражение>, <Выражение замены>)
Данная функция вернет значение первого параметра, в случае,
если оно не является NULL, и значение второго выражения, в
противном случае.

24.

Пример:
ВЫБРАТЬ
СправочникНоменклатуры.Наименование,
ЕСТЬNULL(УчетНоменклатурыОстатки.КоличествоОстаток, 0) КАК
КоличествоОстаток
ИЗ
Справочник.Номенклатура КАК СправочникНоменклатуры
ЛЕВОЕ СОЕДИНЕНИЕ
РегистрНакопления.УчетНоменклатуры.Остатки КАК
УчетНоменклатурыОстатки
ПО УчетНоменклатурыОстатки.Номенклатура =
СправочникНоменклатуры.Ссылка
ГДЕ
СправочникНоменклатуры.ЭтоГруппа = ЛОЖЬ

25.

В данном примере получаются все элементы справочника
номенклатуры, после чего, для каждой номенклатуры из регистра
накопления получаются текущие остатки. Т.к. в результате
соединения в поле УчетНоменклатурыОстатки.КоличествоОстаток"
будут значения NULL для номенклатуры, по которой не было
остатков и чтобы вместо значения NULL в результате запроса
присутствовало значение 0, мы использовали функцию ЕСТЬNULL(),
которая осуществит желаемую замену.

26.

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

27.

Правое соединение - это внутреннее соединение + строки из правой
таблицы, для которых не найдены соответствия.
------------|Люди
|
------------|ИМЯ
|
------------|Алена
|
|Алефтина |
|Владимир|
|Владислав|
|
|
-------------
-------------------------------| Характеристики
|
--------------------------------| ИМЯ
ВОЗРАСТ |
--------------------------------| Алена
21
|
| Алефтина
18
|
| Павел
25
|
| Петр
30
|
| Порфирий
50
|
---------------------------------

28.

// Результат правого внешнего соединения по имени:
// ИмяИзТаблицы1 ИмяИзТаблицы2 ВозрастИзТаблицы2
// Алена
Алена
21
// Алефтина
Алефтина
18
// NULL
Павел
25
// NULL
Петр
30
// NULL
Порфирий
50

29.

Запрос = Новый Запрос(
"ВЫБРАТЬ
| Люди.Наименование КАК ИмяИзТаблицы1,
| Характеристики.Наименование КАК ИмяИзТаблицы2,
| Характеристики.Возраст КАК ВозрастИзТаблицы2
|ИЗ
| Справочник.Люди КАК Люди
| ПРАВОЕ СОЕДИНЕНИЕ
| Справочник.Характеристики КАК Характеристики
| ПО
| Люди.Наименование = Характеристики.Наименование"
);

30.

31.

Задание 1
• Получить цены всех товаров, включая те, для которых цены не
установлены. Все товары имеются в справочнике Номенклатура,
цены товаров хранятся в регистре сведений ЦеныНоменклатуры.
• Связь должна быть установлена левое соединение по
справочнику Номенклатура.
• Если ЕстьNULL, заменить 0.
• 1. С помощью консоли запросов
• 2. С помощью отчета СКД

32.

33.

34.

35.

36.

37.

38.

39.

С помощью отчета СКД

40.

41.

42.

43.

44.

45.

46.

47.

Задание 2
• Найти остатки товаров на складах по всем товарам справочника
Номенклатура. Остатки не должны быть равны 0.

48.

49.

50.

51.

52.

53.

54.

55.

Задание 2
Найти остатки товаров по всем товарам справочника
Номенклатура. Товары не должны быть группой и остатки не
должны быть равны 0.

56.

57.

58.

59.

60.

Сравнение с NULL - оператор ЕСТЬ NULL

61.

Получить данные с остатками и ценами
3 таблицы

62.

63.

64.

65.

// Полное соединение - это левое соединение + правое соединение.
// ------------// |Люди
|
// ------------// |ИМЯ
|
// ------------// |Алена
|
// |Алефтина |
// |Владимир |
// |Владислав |
// |
|
// -------------
-------------------------------| Характеристики
|
--------------------------------| ИМЯ
ВОЗРАСТ |
--------------------------------| Алена
21
|
| Алефтина
18
|
| Павел
25
|
| Петр
30
|
| Порфирий 50
|
---------------------------------

66.

// Результат полного внешнего соединения по имени:
// ИмяИзТаблицы1 ИмяИзТаблицы2 ВозрастИзТаблицы2
// Алена
Алена
21
// Алефтина
Алефтина
18
// Владимир
NULL
NULL
// Владислав
NULL
NULL
// NULL
Павел
25
// NULL
Петр
30
// NULL
Порфирий
50

67.

Запрос = Новый Запрос(
" ВЫБРАТЬ
| Люди.Наименование КАК ИмяИзТаблицы1,
| Характеристики.Наименование КАК ИмяИзТаблицы2,
| Характеристики.Возраст КАК ВозрастИзТаблицы2
|ИЗ
| Справочник.Люди КАК Люди
| ПОЛНОЕ СОЕДИНЕНИЕ
| Справочник.Характеристики КАК Характеристики
| ПО
|Люди.Наименование = Характеристики.Наименование"
);

68.

69.

Полное соединение
• Составить запрос по контрагентам и заказам – номер, дата
документа, клиент, на какую сумму, выполнить полное
соединение по контрагентам и документам заказов, без названий
групп.

70.

71.

72.

73.

74.

75.

76.

77.

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

78.

Запрос = Новый Запрос(
"ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.Вкусы
|
|ОБЪЕДИНИТЬ
|
|ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.Цвета"
);

79.

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

80.

Запрос = Новый Запрос(
"ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.Цвета
|
|ОБЪЕДИНИТЬ ВСЕ
|
|ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.Цвета"
);

81.

Запрос = Новый Запрос(
"ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.Номенклатура
|ОБЪЕДИНИТЬ
|ВЫБРАТЬ
| Наименование
|ИЗ
| Справочник.Цвета
|УПОРЯДОЧИТЬ ПО
| Наименование УБЫВ"
);

82.

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

83.

Заказ 1 на вкладке Объединение

84.

85.

Запрос 2 на вкладке Объединение

86.

87.

88.

Упорядочение

89.

90.

СКД. Запрос 1 на вкладке Объединение

91.

Запрос 2

92.

93.

94.

95.

96.

97.

98.

99.

100.

Задания к практике
• 1. Получить все продажи по контрагентам с помощью
внутреннего соединения
• 1.1 Только по группе Покупатели

101.

102.

103.

104.

105.

106.

107.

108.

• 2. Получить остатки товаров по определенному складу (параметр
склад) с помощью левого соединения. Информацию выдать по
всем товарам.

109.

110.

111.

112.

113.

• 3. Получить остатки всех товаров с помощью правого соединения.
В результат должны попасть все элементы справочника
Номенклатура.
• 3.1 Получить остатки и цены всех товаров.

114.

115.

116.

117.

118.

• 4. Составить запрос по контрагентам и документам продаж
(номер, дата, клиент, сумма). Без названий групп контрагентов с
помощью полного соединения.

119.

120.

121.

122.

123.

124.

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

125.

126.

127.

128.

129.

130.

131.

132.

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