Итак, приступим. Где обычно нам требуются сложные запросы?
В самом распространенном своем использовании сложные запросы с временными таблицами - находят свое применение в процедуре проведения документа-расходника, когда при проведении документа нам нужно знать: хватит ли товара на складе, который мы списываем текущей накладной, или нам нужно выяснить списываемую этой накладной стоимость товара (как самый простой метод списание себестоимости товара - здесь мы будем рассматривать - метод по средневзвешенной стоимости, но существует еще fifo- первый пришел, первый ушел, и lifo - последний пришел, первый ушел. lifo по текущему правилу бухгалтерского учета уже нельзя использовать, но в управленческом учете - он до сих пор используется).
В указанном примере из книги Радченко/Хрусталева на стр 429 мы должны в процедуре проведения документа "Оказание услуг" найти среднюю стоимость списываемой документом номенклатуры. Для этого мы используем данные из двух регистров остатков:СтоимостьМатериалов и СтоимостьМатериаловОстатки.
Из регистра остатков материала - мы берем общее количество данного списываемого товара, а из регистра стоимость материалов - мы берем стоимость этого товара (стоимость товара в регистр записывается при проведении Приходной накладной).
Далее нам нужно выполнить простое выражение:Стоимость товара разделить на его общий остаток на всех складах, и мы получим среднюю стоимость списываемого товара.
Собственно все просто: взяли два регистра, поставили условие по товару из документа - и получили требуемый результат. Но если мы в реальной базе, где данных в регистрах может быть сотни тысяч записей - будем перебирать в результативном цикле ВСЕ записи регистров, чтобы сверить их с перечнем товара из нашего документа, то это будет очень долго и трудоемко!
Отсюда, главная наша задача: чтобы документ проводился быстро! Для этого в самом начале, при вызове регистров, нам нужно ограничить полученные данные из этих регистров перечнем нашего товара. Согласитесь, что если в документе мы проводим только две позиции товара, то зачем нам нужно перебирать оба регистра по всем его многотысячным записям?
И вот для решения задачи оптимизации проведения документа - нам и требуется создать некий фильтр из списка товара по накладной. Для этого мы создаем таблицу, выгружаем в нее номенклатуру из текущего документа. Далее к этой таблице в запросе Левым соединением прикрепляем наши регистры, после чего в итоговую выборку запроса попадают только данные, отфильтрованные перечнем товара из накладной! Ничего лишнего, все работает быстро!
Мы подошли к самой сути - принципу создания этой таблицы, в которую мы будем выгружать товар по накладной.
В приведенном учебнике - нам дают следующий вариант создания Временных Таблиц в запросах:
Для работы с запросами есть объект МенеджерВременныхТаблиц, о нем можно прочитать в Синтаксис-помощнике:Работа с запросами=>МенеджерВременныхТаблиц (см.рис)
Что делает этот менеджер временных таблиц? Он предоставляет нам область, место, или лист бумаги - так проще, куда мы можем временно записать и так же временно хранить нашу табличку с данными по товару из документа.
По примеру из урока - мы обозначаем эту самую область, в которую далее создадим и загрузим данными нашу табличку:
//Создать менеджер временных таблиц
МенеджерВТ=Новый МенеджерВременныхТаблиц;
Запрос = Новый Запрос;
//Укажем какой Менеджер временных таблиц использует этот запрос
Запрос.МенеджерВременныхТаблиц=МенеджерВТ;
Запрос.Текст =
Далее по уроку - мы, используя меню под правой кнопкой мыши, - открываем КонструкторЗапроса:
Теперь здесь в запросе - мы полностью формируем со всеми условиями вид нашей временной таблицы, а так же заполняем ее сразу данными. В этом запросе мы выбираем табличную часть документа "ОказаниеУслуг", выбираем необходимые нам поля из этого документа:Номенклатуру, ВидНоменклатуры, Количество по документу и Сумму по документу.
Далее в этом запросе мы накладываем условия, что работаем только с текущим конкретным документом "ОказаниеУслуг", а не со всеми этими документами сразу (в условии запроса ставим параметр &Ссылка на конкретный документ)
В этом же запросе - мы добавляем группировку (и суммирование по группировке) по Номенклатуре и ее виду, т.к. в документе у нас может быть задвоенная позиция по одному Товару.
И вот мы добрались до вкладки "Дополнительно" в Конструкторе запросов, где непосредственно указываем, что мы создаем временную таблицу, и ее новое имя "НоменклатураДокумента":
Текст Запроса с созданной временной таблицей по уроку из книги Радченко/Хрусталева будет следующим:
//Создать менеджер временных таблиц
МенеджерВТ=Новый МенеджерВременныхТаблиц;
Запрос = Новый Запрос;
//Укажем какой Менеджер временных таблиц использует этот запрос
Запрос.МенеджерВременныхТаблиц=МенеджерВТ;
Запрос.Текст =
"ВЫБРАТЬ
| ОказаниеУслугиПереченьНоменклатуры.Номенклатура,
| ОказаниеУслугиПереченьНоменклатуры.Номенклатура.ВидНоментклатуры КАК ВидНоменклатуры,
| СУММА(ОказаниеУслугиПереченьНоменклатуры.Количество) КАК КоличествоВДокументе,
| СУММА(ОказаниеУслугиПереченьНоменклатуры.Сумма) КАК СуммаВДокументе
|ПОМЕСТИТЬ НоменклатураДокумента
|ИЗ
| Документ.ОказаниеУслуги.ПереченьНоменклатуры КАК ОказаниеУслугиПереченьНоменклатуры
|ГДЕ
| ОказаниеУслугиПереченьНоменклатуры.Ссылка = &Ссылка
|
|СГРУППИРОВАТЬ ПО
| ОказаниеУслугиПереченьНоменклатуры.Номенклатура,
| ОказаниеУслугиПереченьНоменклатуры.Номенклатура.ВидНоментклатуры";
Запрос.УстановитьПараметр("Ссылка", Ссылка);
РезультатЗапроса = Запрос.Выполнить();
Вот таким довольно интересным способом по уроку уважаемых преподавателей Максима Григорьевича Радченко и Елены Юрьевны Хрусталевой мы создали временную таблицу, в которую поместили данные о номенклатуре из проводимого нами документа.
Далее по занятию - нам следует связать нашу созданную временную таблицу с двумя регистрами (Остатков материалов и Стоимостью номенклатуры).
Для этого мы создаем второй Запрос, к которому сразу привяжем наш менеджер Временных таблиц, в котором, собственно, и хранится наша таблица:
Запрос2 = Новый Запрос;
//Укажем какой Менеджер временных таблиц использует этот запрос
Запрос2.МенеджерВременныхТаблиц=МенеджерВТ;
Запрос2.Текст =
Так же с помощью меню под правой кнопкой мыши - выберем пункт КонструкторЗапросов. Здесь во втором запросе - мы добавляем наши два регистра по Остаткам и Стоимости. А вот чтобы привязать их к созданной предыдущим запросом ВременнойТаблице, которая у нас хранится в МенеджереВременныхТаблиц, нам нужно нажать на кнопку в КонструктореЗапросов "Создать описание временной таблицы" - и полностью прописать название полей и их тип, которые есть в той Временной таблице, что мы создали первым запросом:
Данный второй запрос очень простой - мы просто создаем в КонструктореЗапросов на вкладке "Связи" - связь нашей временной таблицы с двумя регистрами ЛЕВЫМ СОЕДИНЕНИЕМ. Только в этом случае у нас справа будет наша ВременнаяТаблица, а слева от нее все равно ВЕСЬ набор данных регистров!!! Поэтому, если так и оставим, то оптимизации работы процедуры проведения документа - мы так и не добьемся!
Значит нам нужно в КонструктореЗапросов в параметры регистров наложить ограничение выборки данных четко по данным из ВременнойТаблицы. Для этого выделим в КонструктореЗапросов каждый из регистров и нажмем на кнопку "Параметры виртуальной таблицы" (см.рис), далее в поле "Условие" нажмем на кнопку выборки и в открывшемся окне "ПроизвольноеВыражение" запишем условие:
Материал В
(ВЫБРАТЬ
НоменклатураДокумента.Номенклатура
ИЗ
НоменклатураДокумента)
***********Весь листинг с двумя запросами по учебнику выглядит следующим образом:
//Создать менеджер временных таблиц
МенеджерВТ=Новый МенеджерВременныхТаблиц;
Запрос = Новый Запрос;
//Укажем какой Менеджер временных таблиц использует этот запрос
Запрос.МенеджерВременныхТаблиц=МенеджерВТ;
Запрос.Текст =
"ВЫБРАТЬ
| ОказаниеУслугиПереченьНоменклатуры.Номенклатура,
| ОказаниеУслугиПереченьНоменклатуры.Номенклатура.ВидНоментклатуры КАК ВидНоменклатуры,
| СУММА(ОказаниеУслугиПереченьНоменклатуры.Количество) КАК КоличествоВДокументе,
| СУММА(ОказаниеУслугиПереченьНоменклатуры.Сумма) КАК СуммаВДокументе
|ПОМЕСТИТЬ НоменклатураДокумента
|ИЗ
| Документ.ОказаниеУслуги.ПереченьНоменклатуры КАК ОказаниеУслугиПереченьНоменклатуры
|ГДЕ
| ОказаниеУслугиПереченьНоменклатуры.Ссылка = &Ссылка
|
|СГРУППИРОВАТЬ ПО
| ОказаниеУслугиПереченьНоменклатуры.Номенклатура,
| ОказаниеУслугиПереченьНоменклатуры.Номенклатура.ВидНоментклатуры";
Запрос.УстановитьПараметр("Ссылка", Ссылка);
РезультатЗапроса = Запрос.Выполнить();
Запрос2 = Новый Запрос;
//Укажем какой Менеджер временных таблиц использует этот запрос
Запрос2.МенеджерВременныхТаблиц=МенеджерВТ;
Запрос2.Текст = "ВЫБРАТЬ
| НоменклатураДокумента.Номенклатура,
| НоменклатураДокумента.ВидНоменклатуры,
| НоменклатураДокумента.КоличествоВДокументе,
| НоменклатураДокумента.СуммаВДокументе,
| ЕСТЬNULL(СтоимостьМатериаловОстатки.СтоимостьОстаток, 0) КАК Стоимость,
| ЕСТЬNULL(ОстаткиМатериаловОстатки.КоличествоОстаток, 0) КАК Количество
|ИЗ
| НоменклатураДокумента КАК НоменклатураДокумента
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СтоимостьМатериалов.Остатки(
| ,
| Материал В
| (ВЫБРАТЬ
| НоменклатураДокумента.Номенклатура
| ИЗ
| НоменклатураДокумента)) КАК СтоимостьМатериаловОстатки
| ПО НоменклатураДокумента.Номенклатура = СтоимостьМатериаловОстатки.Материал
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиМатериалов.Остатки(
| ,
| Материал В
| (ВЫБРАТЬ
| НоменклатураДокумента.Номенклатура
| ИЗ
| НоменклатураДокумента)) КАК ОстаткиМатериаловОстатки
| ПО НоменклатураДокумента.Номенклатура = ОстаткиМатериаловОстатки.Материал";
Вот и весь запрос, сохраняем его и далее по тексту - в цикле перебора данных из запроса прописываем наши расчеты Средней стоимости товара и функции самой записи расхода товара в регистры.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Сделаем первоначальные выводы - каким образом мы создавали Временную таблицу и связывали ее с регистрами:
1.Явно прописали МенеджерВременныхТаблиц
2.Отдельным первым запросом создали Временную таблицу и заполнили ее данными
3.Переподключили МенеджерВременныхТаблиц на второй запрос
4.Во втором запросе пришлось:
1) в ручную прописывать структуру нашей ВременнойТаблицы с обязательным указанием Типов полей;
2) создать связи между временной таблицей и двумя регистрами;
3) на каждый из регистров наложить предварительно фильтр из данных временной таблицы;
4) накладываемый фильтр на регистры - нам пришлось писать в ручную
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Довольно не простой вариант.....Поэтому давайте рассмотрим решение той же задачи с созданием временных таблиц, но более наглядное и простое, как по мне.
У нас есть очень хороший инструмент для создания запросов - КонсольЗапросов. Я пользуюсь своей //infostart.ru/public/719293/ Вы можете использовать любую КонсольЗапросов, которая Вам нравится.
Итак, откроем 1с в пользовательском режиме, вызовем КонсольЗапросов. И начнем сразу же с создания так необходимой нам временной таблицы (создайте поля и тип данных точно такой, как указан в Конфигураторе в документе "ОказанияУслуг" в его табличной части!):
Здесь нет ничего сложного, кроме одной очень важной ремарки - перед наименованием нашей таблички мы ставим амперсант!
&ВнешниеДанные
С помощью амперсанта - мы конкретно указываем, что создаем не просто табличку, а Параметр, который необходим к заполнению при отработке нашего запроса.
Далее идем в КонструктореЗапроса на вкладку "Дополнительно" - и здесь явно указываем что наш созданный набор данных - это ВременнаяТаблица! Назовем эту самую временную таблицу точно так же, как и в уроке по книжке: "НоменклатураДокумента".
Все, временная таблица есть, не нужно более над нею думать)). Но чтобы все заработало, причем в ОДНОМ запросе, нам нужно создать пакет запросов. Для этого в КонструктореЗапросов переходим на вкладку "ПакетЗапросов", нажимаем Добавить, и уже в полученном втором пакете запросов мы имеем возможность видеть,выбирать и работать с только что созданной нами временной таблицей!!!
Поэтому добавим в данный второй пакет запроса нашу ВременнуюТаблицу и нужных два регистра. Установим точно так же связи ВременнойТаблицы Левым Сооединением с регистрами.
Наш запрос практически готов, осталось только на сами регистры наложить фильтр из ВременнойТаблицы. Для этого точно так же выделяем каждый регистр в КонструктореЗапросов и жмем кнопку "Параметры виртуальной таблицы", далее в поле условие нажимаем на выборку и попадаем в окно "Произвольное выражение".
Здесь перетягиваем в расчетное окно реквизит Материал регистра, пишем одну единственную буковку В, ставим скобки и внутри скобок правой клавишей мыши - вызываем еще один КонструкторЗапросов, в котором просто из нашей временной таблицы выбираем поле Номенклатура,все сохраняем и точно так же накладываем Параметр на второй регистр.
Второй КонструкторЗапросов создаст нам следующий код, который и будет условием/фильтром на регистре:
Материал В
ВЫБРАТЬ
НоменклатураДокумента.Номенклатура
ИЗ
НоменклатураДокумента КАК НоменклатураДокумента
Все, запрос полностью готов, сохраняем,закрываем КонструкторЗапросов. Можно, конечно еще на выбранные поля в регистров в КонструктореЗапросов поставить условие ЕстьNull. Можем так же во втором пакете сгрупировать поля по Номенклатуре и просуммировать все остальные поля.
***********Вот такой запрос в единственном экземпляре у нас получился более простым и наглядным методом:
ВЫБРАТЬ
ВнешниеДанные.Номенклатура,
ВнешниеДанные.Количество КАК КоличествоВДокументе,
ВнешниеДанные.Сумма КАК СуммаВДокументе
ПОМЕСТИТЬ НоменклатураДокумента
ИЗ
&ВнешниеДанные КАК ВнешниеДанные
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
НоменклатураДокумента.Номенклатура,
СУММА(НоменклатураДокумента.КоличествоВДокументе) КАК КоличествоВДокументе,
СУММА(НоменклатураДокумента.СуммаВДокументе) КАК СуммаВДокументе,
СУММА(ЕСТЬNULL(ОстаткиМатериаловОстатки.КоличествоОстаток, 0)) КАК Количество,
СУММА(ЕСТЬNULL(СтоимостьМатериаловОстатки.СтоимостьОстаток, 0)) КАК Стоимость
ИЗ
НоменклатураДокумента КАК НоменклатураДокумента
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СтоимостьМатериалов.Остатки(
,
Материал В
(ВЫБРАТЬ
НоменклатураДокумента.Номенклатура
ИЗ
НоменклатураДокумента КАК НоменклатураДокумента)) КАК СтоимостьМатериаловОстатки
ПО НоменклатураДокумента.Номенклатура = СтоимостьМатериаловОстатки.Материал
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиМатериалов.Остатки(
,
Материал В
(ВЫБРАТЬ
НоменклатураДокумента.Номенклатура
ИЗ
НоменклатураДокумента КАК НоменклатураДокумента)) КАК ОстаткиМатериаловОстатки
ПО НоменклатураДокумента.Номенклатура = ОстаткиМатериаловОстатки.Материал
СГРУППИРОВАТЬ ПО
НоменклатураДокумента.Номенклатура
Только в данном варианте при создании сложного запроса с ВременнымиТаблицами в КонсолиЗапросов - мы имеем преимущество - это наглядно запустить наш запрос и увидеть сразу же результат его работы!!!!!!
Для этого нажмите все соответствующие кнопки в КонсолиЗапросов (я рассказывала как ею пользоваться с вариантом ВременныхТаблиц вот здесь //infostart.ru/public/719293/): НайтиПараметры, потом в поле "ТаблицаЗначений",на вкладке "ВремТаблицы" - заполните экспериментальные данные товара. И вот результат - все считает, все видно, все наглядно:
Осталось уже в модуле документа в процедуре Проведения - точно так же сделать обход цикла по результатам запроса и все готово. Да в данном варианте нужно будет перед запуском запроса - создать Таблицу значений и выгрузить в нее колонки табличной части документа.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Сделаем выводы по поводу второго метода создания временных таблиц в запросе, которым пользуюсь я:
1.Один единственный запрос с двумя пакетами, создается не в ручную, а в КонсолиЗапросов.
В первом пакете запросов - создали временную таблицу, которая наглядна видна при работе со вторым пакетом запросов!
2.Во втором пакете запросе пришлось:
1) Просто перетянули мышкой готовую ВременнуюТаблицу
2) создать связи между временной таблицей и двумя регистрами;
3) на каждый из регистров наложить предварительно фильтр из данных временной таблицы;
4) накладываемый фильтр на регистры - мы создавали автоматически,вызвав еще один КонструкторЗапросов!
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Итого, вывод - в учебнике "1С_ Предприятие 8.3. Практическое пособие разработчика. Примеры и типовые приемы" Радченко/Хрусталева показан очень интересный способ создания Временных таблиц для Сложных запросов, но все-таки на практике проще всего работать с тем, что можно сразу проверить,пощупать,отредактировать....
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
В загрузке прилагается учебная база данных (создавала с нуля на Платформе 1с8.3)по урокам из книги ("1С_ Предприятие 8.3. Практическое пособие разработчика. Примеры и типовые приемы" Радченко/Хрусталева) + описанный здесь мой вариант создания Временных таблиц (в проц.Проведения док."ОказаниеУслуг"). На данный момент как раз до уровня середины книги, то есть по описанный здесь урок о Временных таблицах в запросе.
Напомню, что данный пример рассматривается в процедуре Проведения документа "ОказаниеУслуг". В модуле этой процедуры Вы найдете оба варианта, описанного здесь: Попробуйте в режиме Предприятия посмотреть как работает один вариант, потом его заремарьте и снимите ремарку со второго варианта - посмотрите как работает другой - абсолютно индентично!
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
