Вкладені запити. Вкладені запити Робота з пакетними запитами

22.01.2024

Ця стаття розрахована на читачів, які знайомі з мовою SQL.

Мова запитів у 1С, що застосовується починаючи з версії 8, сьогодні стала корисним інструментом для роботи з базами даних, що дозволяє читати з них, але не записувати. Синтаксично мова запитів дуже схожа з мовою SQL, але російською мовою.

Нижче наведено таблицю відповідності основних операторів мови запитів та SQL:

Оператори мови запитів 1С	Оператор SQL


РІЗНІ



З'ЄДНАННЯ
ЗГРУПУВАТИ ПО
ОБ'ЄДНАТИ
ВПОРЯДКУВАТИ ЗА

І це далеко не повний перелік. Більш повну довідкову інформацію про доступні оператори мови запитів можна отримати у конструкторі запитів, про який буде розказано нижче.

Виконання запиту 1С із програмного коду здійснюється за допомогою об'єкта вбудованої мови «Запит». Приклад написання запиту до бази даних із використанням вбудованої мови програмування:

Запит = Новий Запит; Запрос.Текст = "ВИБРАТИ | Синонім.Посилання ЯК Посилання |З | Довідник.Довідник1 ЯК Синонім"; Вибірка = Запит.Виконати().Вибрати(); Поки Вибірка.Наступний() Цикл // Вставити обробку вибірки ВибіркаДетальніЗаписи КінецьЦикл;

Метод «Виконати» виконує запит, метод «Вибрати» повертає значення типу «Вибірка Результату Запиту». Можна також використовувати метод «Вивантажити», який повертає таблицю значень.

Параметри запиту зберігаються як «Параметри» (у разі це структура, тому всі методи структури тут застосовні – вставити, видалити тощо.).

Приклад установки параметра «Запит.Параметри.Вставити» («Довідник», ДовідникПосилання). У запиті звернутися до параметрів можна за допомогою амперсанд «&Довідник». Нижче наведено приклад запиту з використанням параметрів:

Запит = Новий Запит; Запит.Текст = "ВИБРАТИ | Користувачі.Посилання ЯК Посилання, | Користувачі.Батько ЯК Батько, | Запит.Параметри.Вставити("Довідник", ДовідникПосилання); Вибірка = Запит.Виконати().Вибрати(); Поки Вибірка.Наступний() Цикл // Вставити обробку вибірки ВибіркаДетальніЗаписи КінецьЦикл;

Нагадаємо, що мова запитів призначена лише для читання даних із бази, тому в ній відсутні аналоги таких операторів SQL, як INS ERT та UPDATE. Дані можна модифікувати лише через об'єктну модель вбудованої мови програмування 1С. Також у мові запитів 1С існують оператори, аналогів яких немає в SQL, наприклад:

В ІЄРАРХІЇ
ПОМІСТИТИ
ІНДЕКСУВАТИ ПО

В ІЄРАРХІЇ– дозволяє вибрати всі елементи ієрархічного довідника, що входять до ієрархії переданого посилання. Приклад запиту з використанням В ІЄРАРХІЇ:

ВИБРАТИ Товари.Посилання, Товари.Артикул З Довідник.Товари ЯК Товари ДЕ Товари.Посилання В ІЄРАРХІЇ(&Цитрусові)"

В даному випадку в результат повернуться всі підлеглі елементи довідника номенклатури «Цитрусові», байдуже, скільки рівнів ієрархії має цей довідник.

Також, наприклад, є завдання знайти товар з ім'ям «Ручка». Товар має входити до ієрархії «Канц. Товарів», тобто нам не треба шукати ручку дверей. Структура номенклатури у разі така:

Канцелярія

Фурнітура

|_ Ручки дверні |_ Ручка дверна проста |_ Ручка дверна люкс |

Пишемо такий запит:

ВИБРАТИ Товари.Посилання, Товари.Артикул З Довідник.Товари ЯК Товари ДЕ Товари.Найменування Подібно "Ручка%" І Товари.Посилання В ІЄРАРХІЇ(&Канцелярія)"

При використанні конструкції В ІЄРАРХІЇнеобхідно враховувати, що якщо параметр «Канцелярія» передати порожнє посилання, виконання запиту сповільниться, оскільки платформа перевірятиме кожен елемент на приналежність кореню.

ПОМІСТИТИ– Цей оператор поміщає результат у тимчасову таблицю. Приклад запиту:

ВИБРАТИ Користувачі.Посилання ЯК Посилання, Користувачі.Батько ЯК Батько, Користувачі.Найменування ЯК Найменування ПОМІСТИТИ ВідібраніКористувачі З Довідник.Користувачі ЯК Користувачі ДЕ Користувачі.Посилання = &Довідка ВИБРАТИ Відібрані Користувачі. Посилання ЯК Посилання, Відібрані Користувачі. Батько ЯК Батько, Відібрані Користувачі.

Цей запит на SQL буде виконаний кількома запитами:

створення тимчасової таблиці (платформа вміє «перевикористовувати» раніше створені тимчасові таблиці, тому створення відбувається який завжди);
Приміщення даних у тимчасову таблицю;
Виконання основного запиту, а саме SEL ECT із цієї тимчасової таблиці;
Знищення/очищення часової таблиці.

Тимчасова таблиця може бути знищена явно через конструкцію ЗНИЩИТИ, або неявно – під час закриття менеджера тимчасових таблиць.

Об'єкт «Запит» вбудованої мови програмування має властивість «Менеджер Тимчасових Таблиць», яка призначена для роботи з тимчасовими таблицями. Приклад коду:

МВТ = Новий Менеджер Тимчасових Таблиць (); Запит = Новий Запит; Запрос.Менеджер Тимчасових Таблиць = МВТ;

Після виконання запиту змінну МВТ можна використовувати вдруге іншому запиті, що, безсумнівно, є ще одним плюсом використання тимчасових таблиць. У цьому випадку тимчасова таблиця з бази буде видалена при виклику методу «Закрити».

МВТ.Закрити();

...або ж при очищенні змінної з пам'яті, тобто при виконанні методу, в якому була оголошена змінна. Тимчасові таблиці підвищують навантаження на дискову підсистему, тому слід створювати дуже багато тимчасових підсистем (у циклі, наприклад), чи підсистем великого обсягу.

ІНДЕКСУВАТИ ПО– цей оператор застосовується разом із оператором ПОМІСТИТИ.При створенні тимчасової таблиці цим оператором можна проіндексувати створювану таблицю, що суттєво прискорює роботу з нею (але якщо індекс підходить під ваш запит).

Безкоштовна консультація експерта

Дякую за ваше звернення!

Спеціаліст 1С зв'яжеться з вами протягом 15 хвилин.

Особливості деяких операторів мови запитів

ДЛЯ ЗМІНИ- Цей оператор призначений для блокування певної таблиці запиту (або всіх таблиць, які беруть участь у запиті). Блокування здійснюється накладенням блокування U на таблицю. SQL це реалізується через hint UPDLOCK. Дана конструкція необхідна для запобігання блокуванням типу deadlock. Приклад запиту з конструкцією ДЛЯ ЗМІНИ:

ВИБРАТИ Користувачі.Посилання ЯК Посилання, Користувачі.Батько ЯК Батько, Користувачі.Найменування ЯК Найменування З Довідник.Користувачі ЯК Користувачі ЛІВОЕ З'ЄДНАННЯ Довідник.РФК ЯК РФК ПОГЛ. Користувачі

У цьому прикладі U блокування буде встановлено на таблицю "Користувачі". Якщо не вказувати таблицю для блокування, вона буде накладена на всі таблиці, що беруть участь у запиті. Дана конструкція працює тільки в конфігураціях, в яких включений автоматичний режим керування блокуванням.

З'ЄДНАННЯ– запит підтримує з'єднання ЛІВА/ПРАВА, ПОВНА, ВНУТРІШНЯ,що відповідає з'єднанням SQL - LEFT/RIGHT JOIN, OUTER JOIN, INNER JOIN.

Однак при використанні конструктора запитів ви не зможете зробити ПРАВА З'ЄДНАННЯ.Конструктор просто мінятиме місцями таблиці, але оператор буде завжди лівий. З цієї причини в 1С ніколи не зустрінеш застосування правої сполуки.

Синтаксично з'єднання виглядає так:

ВИБРАТИ Таблиця1.Посилання ЯК Посилання З Довідник.Довідник1 ЯК Таблиця1 ЛІВОЕ З'ЄДНАННЯ Довідник.Довідник2 ЯК Таблиця2 ПО Таблиця1.Реквізит = Таблиця2.Реквізит

У мові запитів 1С відсутній оператор для з'єднання декартового твору (CROSS JOIN). Однак, відсутність оператора не означає, що мова запитів не підтримує такого з'єднання. З'єднати таблиці за необхідності можна таким чином:

ВИБРАТИ Таблиця1.Посилання ЯК Посилання З Довідник.Довідник1 ЯК Таблиця1 ЛІВОЕ З'ЄДНАННЯ Довідник.Довідник2 ЯК Таблиця2 ПО ІСТИНА

Як видно з прикладу, заданий ключ з'єднання ПО ІСТИНА, тобто кожен рядок однієї таблиці відповідає рядку іншого. Тип з'єднання (ЛІВИЙ, ПРАВИЙ, ПОВНИЙ, ВНУТРІШНІЙ) не важливий, якщо у вас є рядки в обох таблицях, але якщо в якійсь із таблиць немає рядків (пускова таблиця) – результат буде відрізнятися. Наприклад, при використанні ВНУТРІШНІЙз'єднання результат буде порожнім. При використанні ЛІВОЕ/ПРАВЕз'єднання в результаті буде або не буде даних, залежно від того, до якої таблиці ми приєднуємося - з даними чи ні. При використанні ПОВНОГОЗ'єднання дані будуть завжди (природно, тільки одна таблиця, тому що в іншій порожнечі), вибір типу з'єднання залежить від конкретної прикладної задачі.

Невелика візуальна підказка, як працюють різні типи з'єднань:

ПОДІБНО.На відміну від аналогічного оператора мови SQL – LIKE, шаблон для ПОДІБНОможна задати, використовуючи тільки деякі спеціалізовані символи:

% (відсоток): послідовність, що містить будь-яку кількість довільних символів;
_ (підкреслення): один довільний символ;
/ - наступний символ слід інтерпретувати як звичайний символ.

ПІДСУМКИ ПОаналогом SQL можна назвати оператор ROLLUP. Приклад використання оператора ПІДСУМКИ:

ВИБРАТИ Товари.Ціна ЯК Ціна, Товари.Товар ЯК Товар З Довідник.Номенклатура ЯК Товари ПІДСУМКИ СЕРЕДНІЙ(Ціна) ПО Товар

Результат буде такий:


Ліжко	9833,333



Праска





Ручка

Тобто в результат додається додатковий рядок, що містить значення поля, за яким проводиться угруповання та значення функції, що агрегує.

Робота з пакетними запитами

1С дозволяє працювати із пакетами запитів. У пакетному запиті тексти запитів поділяються крапкою з комою (;). Виконання пакетного запиту 1С здійснюється послідовно. Приклад тексту пакетного запиту:

ВИБРАТИ Користувачі.Посилання ЯК Посилання, Користувачі.Батьки ЯК Батько, Користувачі.Найменування ЯК Найменування З Довідник.Користувачі ЯК Користувачі;
ВИБРАТИ ГрафікРоботи.Користувач ЯК Користувач, ГрафікРоботи.Дата ЯК Дата, ГрафікРоботи.РобочихГодинників ЯК РобочихГодин З РеєстрВідомостей.ГрафікРоботи ЯК ГрафікРоботи

Для отримання результату всіх запитів, що входять до пакета, необхідно скористатися методом об'єкта запиту "Виконати Пакет" замість "Виконати". Цей метод послідовно виконує всі запити. Результат запиту – масив результатів кожного запиту з пакета, а послідовність розташування у масиві така сама, як послідовність запитів у тексті пакета.

Розглядаючи мову запитів, варто згадати про таку особливість, як віртуальні таблиці. Віртуальні таблиці відсутні у базі даних, це своєрідна обгортка, яка виконується за СУБД як запит із використанням підзапитів. Приклад запиту 1С із використанням віртуальних таблиць:

ВИБРАТИ РеєстрЗобов'язаньОбороти.Зобов'язання ЯК Зобов'язання З РегістрНакопичення.РегістрЗобов'язань.Обороти() ЯК РеєстрЗобов'язаньОбороти

Такий запит на СУБД виглядатиме так:

SEL ECT T1.Fld25931RRef FR OM (SELECT T2._Fld25931RRef AS Fld25931RRef, CAST(SUM(T2._Fld25936) AS NUMERIC(38, 8)) AS Fld25936Turnover_, CAST , 8)) AS Fld25937Turnover_ FR OM dbo._AccumRgTn25938 T2 WH ERE ((T2._Fld949 = @P1)) AND ((T2._Fld25936 @P2 OR T2._Fld25937 @P3)) GROUP 2 ._Fld25936 ) AS NUMERIC(38, 8))) 0.0 OR (CAST(SUM(T2._Fld25937) AS NUMERIC(38, 8))) 0.0) T1>>>>

Видно, що не схоже на SQL, оскільки є підзапит, угруповання. Віртуальні таблиці, за великим рахунком – це «синтаксичний цукор», тобто створені загалом для зручності розробки запитів, щоб запити були компактнішими і читабельнішими.

Віртуальні таблиці є лише регістрів, але які саме віртуальні таблиці доступні регістру, можна побачити в конструкторі запитів.

При використанні віртуальних таблиць необхідно завжди давати умову відбору. В іншому випадку можуть виникнути проблеми з продуктивністю.

У тексті запиту це виглядає так:

РегістрНакопичення.РегістрЗобов'язань.Обороти(, Операція = &Операція) ЯК РегістрЗобов'язаньОбороти

Для зручності написання запитів, тобто створення текстів запитів, 1С існує конструктор, який можна викликати через контекстне меню (правою кнопкою миші):

У конструкторі запитів можна побачити повний список підтримуваних функцій та операторів мови запитів.

Конструктор запитів є дуже гнучким візуальним інструментом створення запитів будь-якої складності. Він доступний лише у режимі конфігуратора. У режимі Підприємства є так звана «Консоль запитів» – це зовнішня обробка, яка постачається на диску ІТС. Для керованого додатка консоль запитів можна завантажити на сайті its.1c.ru.

Опис роботи в конструкторі запитів виходить за межі тематики цієї статті, тому докладно розглядатися не буде.

Причини неоптимальної роботи запитів

Нижче наведено список основних причин (але не всіх), які призводять до уповільнення виконання запиту.

Використання з'єднання з підзапитами

Не рекомендується виконувати з'єднання з підзапитами, підзапити необхідно замінити на тимчасові таблиці. З'єднання підзапитів може призводити до значної втрати у продуктивності, при цьому виконання запиту на різних СУБД може значно різнитися у швидкості. Швидкість виконання таких запитів також є чутливою до статистики в СУБД. Причина такої поведінки в тому, що оптимізатор СУБД не завжди коректно може визначити оптимальний план виконання запитів, оскільки оптимізатор нічого не знає про те, скільки рядків поверне підзапит після свого виконання.

Використання віртуальних таблиць у з'єднаннях запиту

Віртуальні таблиці лише на рівні СУБД виконуються як підзапити, тому причини такі самі, як у першому пункті.

Використання умов у запиті, які не підходять під існуючі індекси

Якщо в умовах запиту (в операторі ДЕабо в умовах віртуальної таблиці) використовуються поля, які не всі входять до індексу, даний запит буде виконаний з використанням SQL конструкції table scan або index scan (повністю або частково). Це позначиться не тільки на часі виконання запиту, але також буде накладено надлишкове S блокування на зайві рядки, що в свою чергу може призвести до ескалації блокувань, тобто буде заблоковано всю таблицю.

Використання АБО в умовах запиту

Використання логічного оператора АБОу конструкції ДЕможе також спричиняти сканування таблиці (table scan). Це відбувається через те, що СУБД не може коректно використати індекс. Замість АБОможна застосувати конструкцію ОБ'ЄДНАТИ ВСЕ.

Отримання даних через точку для полів складового типу

Не рекомендується отримувати значення через точку (у конструкції ВИБРАТИ, ДЕ), оскільки якщо реквізит об'єкта виявиться складовим типом, з'єднання відбуватиметься з кожною таблицею, що входить до цього складового типу. В результаті, запит на СУБД буде значно ускладнений, це може перешкодити оптимізатору у виборі коректного плану виконання запиту.

З бази даних за певною умовою. Для цього у 1С 8.3 необхідно використовувати вкладені запити.

Але слід враховувати, що здебільшого вкладені запити в 1С марні без з'єднання їхнього результату коїться з іншими таблицями. Таке з'єднання практично у будь-якому разі призведе до сильного уповільнення виконання запиту загалом.

Приклад вкладеного запиту мовою запитів

Наведу приклад вкладеного запиту на . Допустимо, нам потрібно зробити вибірку суми деякого залишку за окремими клієнтами на певну дату:

ВИБРАТИ
НерозплатиЗалишки.Замовник,
НерозплатиЗалишки.СуммаОст
З

Отримайте 267 відеоуроків з 1С безкоштовно:

ЛІВОЕ З'ЄДНАННЯ Реєстр Накопичення. Нерозплати. Залишки ЯК Нерозплати
ПО ВложЗапрос.ПосиланняНаСпрЗамовники = НерозпОплатиЗалишки.Замовник

Коли СУБД виконуватиме такий запит, не виключені неправильні дії оптимізатора, оскільки важко визначитися із планом обробки запиту. Коли СУБД з'єднує дві таблиці, оптимізатор будує алгоритм з урахуванням обчислення кількості записів у цих таблицях.

Коли ж використовується вкладений запит, обчислити кількість записів, які повернулися із вкладеного запиту, дуже важко.

Як краще?

// Тимчасова таблиця
ВИБРАТИ
Замовники.Посилання ЯК Замовники
ПОМІСТИТИ табЗамовники
З
Довідник. Замовники ЯК Замовники
ДЕ Замовники. Посилання В (& Замовники)
;

// Основний запит
ВИБРАТИ
табКлієнти.Посилання,
НерозплатиЗалишки.СуммаОст,
З
табЗамовники ЯК табЗамовники
ЛІВОЕ З'ЄДНАННЯ РеєстрНакопичення.Нерозплати.Залишки(
,
Замовник В
(ВИБРАТИ
табЗамовники.
З
табЗамовники)) ЯК НерозплатиЗалишки
ПО таб Замовники. Замовники = Нерозплати Залишки. Замовники

Дивіться також відео-урок про вкладені запити:

Тепер оптимізатор знає заздалегідь, скільки записів у тимчасовій таблиці, і легко оптимізує алгоритм виконання з'єднання таблиць.

Якщо Ви починаєте вивчати програмування 1С, рекомендуємо наш безкоштовний курс (не забудьте

Конструктор запитів у 1С 8.3 та 8.2 – найпотужніший інструмент розробки. Він дозволяє скласти текст запиту за допомогою спеціального візуального середовища. Таким чином, щоб створити запит 1с не обов'язково знати вбудовану мову запитів, достатньо орієнтуватися в нескладному та інтуїтивно зрозумілому інтерфейсі конструктора.

Конструктор запитів є набором вкладок, кожна з яких відповідає за свою частину запиту. Так заповнюючи вкладку Таблиці та полями вибираємо таблиці з яких запит 1с отримуватиме дані та поля цих таблиць необхідні для вирішення конкретної задачі. Заповнюючи у кладку Умовими накладаємо умови на вибрані таблиці, щоб вибрати з них тільки потрібні нам дані і так далі.

Опис конструктора запитів на офіційному сайті 1С 8: v8.1c.ru

Таблиці та поля; ; ; ; ; ; Вкладені запити (у розробці).

Для того, щоб викликати конструктор запитів 1с 8 у програмному коді необхідно:

Створити новий запит

Запит = Новий Запит;

Задати порожній рядок тексту запиту

Запит. Текст = "";

Поставити курсор мишки між лапками, натиснути праву кнопку миші. У контекстному меню, що відкрилося, вибрати пункт Конструктор запитута відповісти Такна питання щодо створення нового запиту. Якщо текст запиту вже записаний, необхідно клацнути на будь-якому місці всередині і викликати конструктор ;

Розглянемо на невеликих прикладах із зростаючою складністю усі основні вкладки конструктора запитів. Такий підхід дозволить програмісту-початківцю 1с більш ефективно вивчити конструктор і всі його можливості. Для прикладів будемо використовувати конфігурацію Бухгалтерія 3.0.

Урок №1. Конструктор запитів – найпростіший приклад використання.

Завдання: написати запит до довідника, вибрати всю номенклатуру довідника.

Нові вкладки: Таблиці та поля.

Нові механізми: перегляд та редагування тексту запиту за допомогою кнопки «Запит».

Для початку створення запиту створимо новий запит та викличемо конструктор (як це робиться написано кількома абзацами вище). Після цього відкриється вікно конструктора на вкладці Таблиці та поля.

Теоретична частина уроку №1

Вкладка Таблиці та поляскладається з трьох розділів:

База даних. У цьому розділі представлені всі таблиці бази даних, які можна використовувати для побудови запиту;

Таблиці. У цьому розділі вибираються таблиці, необхідні для даного запиту. Для того щоб перемістити їх з розділу база данихпотрібно:

Або двічі клацнути по таблиці;
Або скористатися кнопками ">" або ">>".

Над розділом Таблиціє ряд кнопок. Більшість із них буде докладніше розказано в наступних уроках. А поки що дам тільки короткі пояснення.

Створити вкладений запит(Червона лінія). Призначена для створення нового вкладеного запиту;
Створити опис тимчасової таблиці(жовта лінія). Дозволяє задати ім'я тимчасової таблиці, яка розташована поза цим запитом, також можна використовувати для передачі в запит таблиці значень;
Змінити поточний елемент(Зелена лінія). Дозволяє перейти до виділеного вкладеного запиту, тимчасової таблиці або опису тимчасової таблиці;
Видалити поточний елемент(Блакитна лінія). Видаляє виділену таблицю з вибраних таблиць;
Замінити таблицю(Синя лінія). Відкриває діалог заміни виділеної таблиці. Корисно, якщо ви неправильно вибрали віртуальну таблицю регістру, оскільки відбувається позиціонування на поточній вибраній таблиці у списку.
Параметри віртуальної таблиці(Фіолетова лінія). Відкриває параметри віртуальної таблиці регістру.

Поля. У цьому розділі вибираються поля таблиць з попереднього розділу. Ці поля будуть колонками таблиці або вибірки, отриманої в результаті виконання запиту. Потрібні вони передусім, щоб одержати з вибраних таблиць лише необхідну у разі інформацію. Для того, щоб перемістити їх з розділу Таблиці необхідно:

Або двічі клацнути по полю;
Або скористатися кнопками ">" або ">>";
Також можна додати нове поле самостійно, використовуючи довільний вираз із полів вибраних таблиць та функцій мови запитів.

Над розділом Поляє ряд кнопок. Про створення полів за допомогою довільних виразів буде детальніше розказано на наступних уроках. А поки що дам тільки короткі пояснення.

Додати(Зелена лінія). Призначена для додавання нового поля за допомогою редактора довільних виразів;
Змінити поточний елемент(Червона лінія). Дозволяє змінити виділене поле за допомогою редактора;
Видалити поточний(Синя лінія). Видаляє виділене поле зі списку.

Практична частина уроку №1

Ми розібралися з теорією необхідної для виконання завдання, передбаченого в цьому уроці. Нагадаю, як воно звучить: написати запит до довідника номенклатура, вибрати всю номенклатуру довідника.

Приступимо до створення запиту за номенклатурою:

Створимо новий запит і відкриємо конструктор методом, зазначеним на початку уроку;
В розділі База данихвідкриємо гілку Довідникиі знайдемо там довідник Номенклатура;
Виділимо його і за допомогою кнопки «>» перенесемо до розділу Таблиці;
В розділі Таблицірозкриємо довідник номенклатура за допомогою значка "+";
У списку полів, що розкрився, знайдемо поле Посиланняі перенесемо його в розділ Поляза допомогою кнопки «>»
Запит по номенклатурі готовий, натискаємо кнопку «ОК» у нижній частині вікна конструктора.

Розглянемо, що таке вкладені запитиі для чого вони потрібні.
Часто зустрічається ситуація, коли над таблицею необхідно зробити кілька послідовних дій. Досить показовим є приклад, коли спочатку треба, а потім вже на згруповану таблицю накласти деяку умову, або з'єднати з іншою таблицею. У таких випадках на допомогу і приходять вкладені запити.

Вкладений запит практично нічим не відрізняється від звичайного запиту. Він полягає в дужках і в ньому доступні майже всі методи та функції мови запитів 1С. А для вищого запиту доступні всі поля вкладеного запиту.
Структура найпримітивнішого вкладеного запиту виглядає так

ВИБРАТИ З (ВИБРАТИ З) ЯК Вкладений Запит

Звісно ж у такому вигляді використання вкладеного запиту немає сенсу, т.к. можна відразу вибрати потрібні поля без використання вкладеності. Тут все дуже спрощено для простоти розуміння.

А тепер розглянемо все вищесказане на прикладі.
Нехай у нас є таблиця Співробітники Підрозділів:

І ми хочемо вибрати із цієї таблиці всі підрозділи, де працює більше одного співробітника.

ВИБРАТИ Співробітники Підрозділів. Підрозділ ЯК Підрозділ, КІЛЬКІСТЬ (Співробітники Підрозділів. Співробітник) ЯК Кількість Співробітників З Співробітники Підрозділів ЯК Співробітники Підрозділів ЗГРУПУВАТИ ПО Співробітники Підрозділів.

Якщо виконати цей запит, то в результаті отримаємо наступну таблицю

Другим кроком нам необхідно накласти на цю таблицю обмеження кількості співробітників. Для цього зробимо наведений вище запит вкладеним і у вищестоящому запиті пропишемо відповідну умову

ВИБРАТИ Вкладений Запит. Підрозділ ЯК Підрозділ З (ВИБРАТИ Співробітники Підрозділів. поділ) ЯК Вкладений Запит ДЕ Вкладений Запит. Кількість Співробітників > 1

Таким чином, результат підсумкового запиту буде наступним

Заради справедливості варто відзначити, що той же результат можна досягти за допомогою функції МАЮЧІмови запитів 1С, а також із використанням тимчасових таблиць.
На практиці ви звичайно ж зіткнетеся з складнішими вкладеними запитами, в яких може використовуватися як , так і таблиць. Також може бути кілька рівнів вкладеності.

Вирішив зробити свій внесок і описати ті особливості мови, які не були розглянуті в наведених вище статтях. Стаття орієнтована на розробників-початківців.

1. Конструкція "ІЗ".

Для того, щоб отримати дані з бази, зовсім необов'язково використовувати конструкцію "З".
Приклад: Нам необхідно вибрати всю інформацію про банки з довідника.
Запит:

ВИБРАТИ Довідник.Банки.*

Вибирає всі поля із довідника Банки. І є аналогічним запиту:

ВИБРАТИ Банки. * З Довідник. Банки ЯК Банки

2. Упорядкування даних по посилальному полю

Коли нам необхідно впорядкувати дані запиту за примітивними типами: "Рядок", "Кількість", "Дата" і т.д., то все вирішується використанням конструкції "Упорядкувати за", якщо вам необхідно впорядкувати дані по посилальному полю? Посилальне поле є посилання, унікальний ідентифікатор, тобто. Власне кажучи якийсь довільний набір символів і просте впорядкування може видати неочікуваний результат. Для упорядкування посилальних полів використовується конструкція "АВТОПОРЯДОЧУВАННЯ". Для цього необхідно спочатку впорядкувати дані безпосередньо за посиланням типу конструкцією "Упорядкувати за", а потім конструкція "автоупорядкування".

У цьому випадку для документів упорядкування відбуватиметься в порядку "Дата->Номер" для довідників за "Основним поданням". Якщо упорядкування відбувається не за посиланнями, то використовувати конструкцію "АВТОПОРЯДОЧУВАННЯ" не рекомендується.

У деяких випадках конструкція "АВТОПОРЯДОЧУВАННЯ" може уповільнювати процес вибірки. Аналогічно можна переписати без автоупорядкування для документів:

3. Отримання текстового подання типу посилання. Конструкція "ПРЕДСТАВЛЕННЯ".

Коли вам необхідно вивести для показу поле посилання типу, наприклад поле "Банк", яке є посиланням на елемент довідника "Банки", то необхідно розуміти, що при виведенні цього поля автоматично виконається підзапит до довідника "Банки", щоб отримати подання довідника. Це уповільнюватиме виведення даних. Для того, щоб цього уникнути, необхідно використовувати конструкцію "ПРЕДСТАВЛЕННЯ" в запиті, щоб відразу отримати подання об'єкта і вже його виводити для перегляду.

У система компонування даних цей механізм використовується за замовчуванням, а ось при формуванні макетів в комірках слід вказувати подання поля посилання, а наприклад, у розшифровку поміщати саме посилання.

4. Умова на вибірку даних за шаблоном.

Наприклад, вам необхідно отримати мобільні телефони працівників виду (8-123-456-78-912). Для цього необхідно поставити таку умову у запиті:

ВИБРАТИ Співробітник.Найменування, Співробітник.Телефон ЯК Телефон З Довідник.Співробітники ЯК Співробітники ДЕ Телефон ПОДІБНО "_-___-___-__-__"

Символ "_" є службовим та замінює будь-який символ.

5. Одночасне використання підсумків та угруповань.

Підсумки часто використовуються разом із угрупованнями, у разі агрегатні функції в підсумках можна не вказувати.

ВИБРАТИ ОказаниеПослуг.Організація ЯК Організація, НаданняПослуг.Номенклатура ЯК Номенклатура, СУМА(НаданняПослуг.СумаДокумента) ЯК СумаДокумента З Документ. Організація, Номенклатура

У цьому випадку запит поверне практично те саме, що і такий запит:

ВИБРАТИ Надання Послуг. Організація ЯК Організація, Надання Послуг. Номенклатура ЯК Номенклатура, Надання Послуг.

Тільки перший запит згорне записи з однаковою номенклатурою.

6. Розіменування полів.

Звернення до полів через точку називається операцією розіменування поля посилання. Наприклад Оплата. Організація. Адміністративна Одиниця. В цьому випадку в полі "Організація" документа "Оплата" посилається на іншу таблицю "Організації", в якій буде отримано значення реквізиту "Адміністративна Одиниця". Важливо розуміти, що при зверненні до полів через точку платформа неявно створює підзапит і з'єднує ці таблиці.

Запит:

Можна уявити у вигляді:

ВИБРАТИ Оплата.Посилання, Оплата.Організація, Оплата.Організація, Організації. Адміністративна Одиниця З Документ. Оплата ЯК Оплата ЛІВОЕ З'ЄДНАННЯ Довідник. Організації ЯК Організації ПЗ Оплата.

При розіменуванні полів посилань складового типу платформа намагається створити неявні з'єднання з усіма таблицями, які входять в тип цього поля. У цьому випадку запит буде неоптимальним. Якщо чітко відомо, якого типу поле, необхідно обмежувати такі поля за типом конструкцією ВИРАЗИТИ().

Наприклад, є регістр накопичення "Нерозподілені оплати", де реєстратором можуть виступати кілька документів. У цьому випадку неправильно отримувати значення реквізитів реєстратора таким чином:

ВИБРАТИ НерозподіленіОплати.Реєстратор.Дата, ..... З РеєстрНакопичення.НерозподіленіОплати ЯК НерозподіленіОплати

слід обмежити тип складеного поля реєстратор:

ВИБРАТИ ВИРАЗИТИ(НерозподіленіОплати.Реєстратор ЯК Документ.Оплата).Дата, ..... З РеєстрНакопичення.НерозподіленіОплати ЯК НерозподіленіОплати

7. Конструкція "ДЕ"

При лівому з'єднанні двох таблиць, коли ви накладаєте умову "ДЕ" на праву таблицю, то ми отримаємо результат аналогічний результату при внутрішньому з'єднанні таблиць.

приклад. Необхідно вибрати всіх Клієнтів із Довідника клієнти і для тих клієнтів, у яких є документ оплата зі значенням реквізиту "Організація" = &Організація вивести документ "Оплата", для тих, хто не має, не виводити.

Результат запиту поверне записи лише для тих клієнтів, які мали оплату по організації в параметрі, а інших клієнтів відсіє. Тому необхідно спочатку отримати всі оплати за "такоюсь" організацією у тимчасовій таблиці, а потім уже з'єднувати з довідником "Клієнти" лівим з'єднанням.

ВИБРАТИ Оплата.Посилання ЯК Оплата, Оплата.Пайщик ЯК Клієнт ПОМІСТИТИ оплати З Документ.Оплата ЯК Оплата ДЕ Оплата.Відділення = &Відділення; //////////////////////////////////////////////////// ////////////////////////////// ВИБРАТИ Клієнти.Посилання ЯК Клієнт, ЄNULL(тОплати.Оплата, "") ЯК Оплата З Довідник .Клієнти ЯК Клієнти ЛІВОЕ З'ЄДНАННЯ тОплати ЯК тОплати ПО Клієнти.Посилання = тОплати.Клієнт

Можна обійти цю умову іншим способом. необхідно накласти умову "ДЕ" у зв'язку двох таблиць. Приклад:

ВИБРАТИ Клієнти.Посилання, Оплата.Посилання З Довідник.УС_Абоненти ЯК УС_Абоненти ЛІВОЕ З'ЄДНАННЯ Документ. . Посилання

8. З'єднання з Вкладеними та Віртуальними таблицями

Вкладені запитинайчастіше необхідні вибірки даних за будь-якою умовою. Якщо ж потім використовувати їх у поєднанні з іншими таблицями це може критично уповільнити виконання запиту.

Для прикладу нам необхідно для деяких клієнтів отримати суму залишку на поточну дату.

ВИБРАТИ Нерозподілені Оплати Залишки. Клієнт, Нерозподілені Оплати Залишки. ЯК Нерозподілені Оплати ПЗ Вкладений Запит. Посилання = Нерозподілені Оплати Залишки.

При виконанні такого запиту можливі помилки оптимізатора СУБД при виборі плану, що призведе до неоптимального виконання запиту. При з'єднанні двох таблиць оптимізатор СУБД вибирає алгоритм з'єднання таблиць виходячи з кількості записів в обох таблицях. У разі наявності вкладеного запиту визначити кількість записів, яка поверне вкладений запит вкрай складно. Тому замість вкладених запитів завжди варто використовувати часові таблиці. Тому перепишемо запит.

ВИБРАТИ Клієнти.Посилання ЯК Посилання ПОМІСТИТИ тКлієнти З Довідник.Клієнти ЯК Клієнти ДЕ
Посилання В (&Клієнти) ; //////////////////////////////////////////////////// ////////////////////////////// ВИБРАТИ тКлієнти. Посилання, Нерозподілені Оплати Залишки. (, Клієнт В (ВИБРАТИ тКлієнти.Посилання З тКлієнти)) ЯК НерозподіленіОплатиЗалишки ПО тКлієнти.Посилання = НерозподіленіОплатиЗалишки.Клієнти

В даному випадку оптимізатор зможе визначити, скільки записів використовує тимчасова таблиця тКлієнти і зможе підібрати оптимальний алгоритм з'єднання таблиць.

Віртуальні таблиці , дозволяють отримати практично готові дані для більшості прикладних завдань. Ці таблиці є фізичними, а компонуються системою нальоту, тобто. при отриманні даних із віртуальних таблиць система збирає дані з підсумкових таблиць регістрів, компонує, групує та видає користувачеві.

Тобто. при з'єднанні з віртуальною таблицею відбувається з'єднання з підзапит. У цьому випадку оптимізатор СУБД також може вибрати неоптимальний план з'єднання. Якщо запит формується недостатньо швидко і в запиті використовуються з'єднання у віртуальними таблицями, то рекомендується винести звернення до віртуальних таблиць у тимчасову таблицю, а потім зробити з'єднання між двома тимчасовими таблицями. Перепишемо попередній запит.

ВИБРАТИ Клієнти.Посилання ЯК Посилання ПОМІСТИТИ тКлієнти З Довідник.Клієнти ЯК Клієнти ІНДЕКСОВАТИ ПО Посилання ДЕ
Посилання В (&Клієнти) ; //////////////////////////////////////////////////// ////////////////////////////// ВИБРАТИ Нерозподілені Оплати. Сума Залишок, Нерозподілені Оплати. Клієнт В (ВИБРАТИ тКлієнти.Посилання З тКлієнти)) ЯК Нерозподілені Оплати Залишки; //////////////////////////////////////////////////// ////////////////////////////// ВИБРАТИ тКлієнти.Посилання, залишки.СумаЗалишок ЯК СумаЗалишок З тКлієнти ЯК тКлієнти тКлієнти.Посилання = тЗалишки.Клієнт

9.Перевірка результату виконання запиту.

Результат виконання запиту може бути порожнім, для перевірки порожні значення слід використовувати конструкцію:

РезЗапроса = Запрос.Выполнить(); Якщо резЗапроса.Пустой() Тоді Повернення; КінецьЯкщо;

Метод Порожній()слід використовувати до методів Вибрати()або Вивантажити(), тому що на отримання колекції витрачається час.

Ні для кого не є відкриттям те, що в циклі запити використовувати вкрай небажано. Це може критично позначитися на час роботи тієї чи іншої функції. Дуже бажано отримувати всі дані в запиті і потім обробляти дані в циклі. Але іноді трапляються випадки, коли винести запит за межі циклу стає неможливим. І тут для оптимізації можна винести створення запиту межі циклу, а циклі підставляти необхідні параметри і виконувати запит.

Це позбавить систему синтаксичної перевірки запиту в циклі.

11. Конструкція "МАЮЧІ".

Конструкція, що досить рідко зустрічається в запитах. Дозволяє накладати умови на значення агрегатних функцій (СУМА, МІНІМУМ, СЕРЕДНЕ і т.д.). Наприклад, вам необхідно вибрати тільки тих клієнтів, у яких сума оплат у вересні була більшою за 13 000 рублів. Якщо використовувати умову "ДЕ", то доведеться спочатку створювати тимчасову таблицю або вкладений запит, там групувати записи по сумі оплати та потім накладати умову. Конструкція "МАЮЧІ" допоможе цього уникнути.

ВИБРАТИ Оплата.Клієнт, СУМА(Оплата.Сума) ЯК Сума З Документ.Оплата ЯК Оплата ДЕ МІСЯЦЬ(Оплата.Дата) = 9 ЗГРУПУВАТИ ПО Оплата.

У конструкторі для цього достатньо перейти на вкладку "Умови", додати нову умову та поставити галочку на "Довільне". Далі просто написати Сума(Оплата.Сума) > 13000

12. Значення NULL

Я не описуватиму тут принципи тризначної логіки в БД, є безліч статей на цю тему. Просто коротко про те як NULLможе вплинути результат запиту. Значення NULL насправді не значення, а факт, що значення не визначено, невідомо. Тому будь-які операції з NULL повертають NULL, чи то додавання, віднімання, розподіл чи порівняння. Значення NULL не можна порівняти зі значенням NULL, оскільки ми знаємо, що саме порівнювати. Тобто. обидва ці порівняння: NULL = NULL, NULL<>NULL - це не Істина чи не Брехня, це невідомо.

Давайте розглянемо приклад.

Нам необхідно для тих клієнтів, які не мають оплат, вивести поле "Ознака" зі значенням "Ні оплат". Причому ми достеменно знаємо, що такі клієнти у нас є. І для того, щоб відобразити суть того, що писав вище, зробимо це так.

ВИБРАТИ "Ні оплат" ЯК Ознака, NULL ЯК Документ ПОМІСТИТИ оплати; //////////////////////////////////////////////////// ////////////////////////////// ВИБРАТИ Клієнти.Посилання ЯК Клієнт, Оплата.Посилання ЯК Оплата ПОМІСТИТИ тКлієнтОплата З Довідник.Клієнти ЯК Клієнти ЛІВОЕ З'ЄДНАННЯ Документ.Оплата ЯК Оплата ПО Клієнти.Посилання = Оплата.Пайщик; //////////////////////////////////////////////////// ////////////////////////////// ВИБРАТИ тКлієнтОплата.Клієнт З тКлієнтОплата ЯК тКлієнтОплата ВНУТРІШнє З'ЄДНАННЯ тОплати ЯК тОплати ПО тКлієнтОплата. Документ

Зверніть увагу на другу тимчасову таблицю тКлієнтОплата. Лівим з'єднанням я вибираю всіх клієнтів та всі оплати за цими клієнтами. Для тих же клієнтів, які не мають оплат у полі "Оплата", буде NULL . Наслідуючи логіку, в першій тимчасовій таблиці "тОплати" я позначив 2 поля, одне з них NULL, друге рядок "Не має оплат". У третій таблиці я з'єдную внутрішнім з'єднанням таблиці "тКлієнтОплата" та "тОплати" по полях "Оплата" та "Документ". Ми знаємо, що в першій таблиці поле "Документ" це NULL, і в другій таблиці у тих, хто не має оплат у полі "Оплата" теж NULL. Що ж поверне нам таке з'єднання? А нічого не поверне. Тому що порівняння NULL = NULL не набуває значення Істина.

Щоб запит повернув нам очікуваний результат, перепишемо його:

ВИБРАТИ "Ні оплат" ЯК Ознака, ЗНАЧЕННЯ(Документ.Оплата.ПустаПосилання) ЯК Документ ПОМІСТИТИ оплати; //////////////////////////////////////////////////// ////////////////////////////// ВИБРАТИ Клієнти.Посилання ЯК Клієнт, ЄNULL(Оплата.Посилання, ЗНАЧЕННЯ(Документ.Оплата.ПустаПосилання) )) ЯК Оплата ПОМІСТИТИ тКлієнтОплата З Довідник.Клієнти ЯК Клієнти ЛІВОЕ З'ЄДНАННЯ Документ.Оплата ЯК Оплата ПО Клієнти.Посилання = Оплата.Пайщик; //////////////////////////////////////////////////// ////////////////////////////// ВИБРАТИ тКлієнтОплата.Клієнт З тКлієнтОплата ЯК тКлієнтОплата ВНУТРІШнє З'ЄДНАННЯ тОплати ЯК тОплати ПО тКлієнтОплата. Документ

Тепер у другій тимчасовій таблиці ми вказали, що у випадку, якщо поле "Оплата" є NULL, тоді це поле = порожнє посилання на документ оплата. У першій таблиці ми також замінили NULL на порожнє посилання. Тепер у поєднанні беруть участь не NULL поля і запит поверне нам очікуваний результат.

Усі запити, які містяться у статті, відображають ситуації, які я хотів би розглянути і нічого більше. Про ні можуть бути маячні чи оптимальні, головне, щоб відбивали суть прикладу.

13. Недокументована особливість конструкції "ВИБІР КОЛИ...ТОДІ....КІНЕЦЬ".

У разі, коли необхідно описувати у запиті контрукцію "Умови", ми використовуємо стандартний синтаксис:

ВИБРАТИ ВИБІР КОЛИ Користувачі.Найменування = "Вася Пупкін" ТОДИ "Наш улюблений співробітник" Інакше "Не знаємо такого" КІНЕЦЬ ЯК Поле1 З Довідник.Користувачі ЯК

А що робити, якщо, наприклад, нам треба отримати назву місяця у запиті? Писати величезну конструкцію у запиті негарно і довго, тому нас може виручити така форма запису вище:

ВИБІР МІСЯЦЬ (УС_РозрахунокСпоживання_ГрафікОбороти.ПеріодРозрахунку) КОЛИ 1 ТОДІ "Січень" КОЛИ 2 ТОДІ "ЛЮТИЙ" КОЛИ 3 ТОДІ "КОДИ нь" КОЛИ 7 ТОДІ "Липень" КОЛИ 8 ТОДИ "Август" КОЛИ 9 ТОДИ "Вересень" КОЛИ 10 ТОДИ "Жовтень" КОЛИ 11 ТОДИ "Листопад" КОЛИ 12 ТОДИ "Грудень"

Тепер конструкція виглядає не такою громіздкою та легко сприймається.

14. Пакетне виконання запиту.

Щоб не плодити запити, можна створити один великий запит, розбити його на пакети і працювати вже з ним.
Наприклад, мені потрібно отримати з довідника "Користувачі" поля: "ДатаНародження" та доступні ролі для кожного користувача. вивантажити це в різні табличні частини на формі. Звичайно, можна зробити це в одному запиті, тоді доведеться перебирати записи або згортати, а можна так:

ВИБРАТИ Користувачі.Посилання ЯК ПІБ, Користувачі.ДатаНародження, Користувачі.Роль ПОМІСТИТИ в Користувачі З Довідник.Користувачі ЯК Користувачі; //////////////////////////////////////////////////// ////////////////////////////// ВИБРАТИ в Користувачі. ПІБ, ВТ Користувачі. Дата народження; //////////////////////////////////////////////////// ////////////////////////////// ВИБРАТИ втКористувачі.ПІБ, втКористувачі.Роль З втКористувачі ЯК втКористувачі ЗГРУПУВАТИ ПО вкористувачі.ПІБ, вт. Дата народження

тПакет = Запрос.ВыполнитьПакет();

ТП_ДатиНародження = тПакет.Выгрузить();
ТП_Ролі = тПакет.Вивантажити();

Як ми бачимо, запит можна виконати в пакеті та працювати з результатом як з масивом. У деяких випадках дуже зручно.

15. Умови пакетного запиту

Наприклад, у нас є пакетний запит, де спочатку ми отримуємо поля: "Найменування, ДатаНародження, Код" з довідника "Користувачі" і хочемо з довідника "ФізОсоба" отримати записи з умовою по цих полях.

ВИБРАТИ Користувачі.ФізОсоба.Найменування ЯК Найменування, Користувачі.ФізОсоба.ДатаНародження ЯК ДатаНародження, Користувачі.ФізОсоба.Код ЯК Код ПОМІСТИТИ в Користувачі З Довідник.Користувачі ЯК //////////////////////////////////////////////////// ////////////////////////////// ВИБРАТИ ФізичніОбличчя.Посилання ЯК ФізОбличчя З Довідник.

Можна накласти умови таким чином:

ДЕ ФізичніОбличчя.Код В (ВИБРАТИ втПользователи.Код З втКористувачі) І ФізичніОбличчя.Найменування В (ВИБРАТИ втКористувачі.Код З втКористувачі) І ФізичніОбличчя.

А можна й так:

ДЕ (Фізичні Особи. Код, Фізичні Особи. Найменування, Фізичні Особи.

Причому обов'язково дотримуватися порядку.

16. Виклик конструктора запитів для "умови" у пакетному запиті

Коли необхідно накласти умову, як у прикладі вище, можна забути те чи інше поле називається у віртуальній таблиці.
Наприклад треба накласти умову на полі "ДатаНародження", а у віртуальній таблиці це поле називається "ДатаНародженняДебітора", і якщо ви забули назву, то доведеться виходити з редагування умови без збереження та дивитися назву поля. Для того, щоб уникнути цього, можна скористатися наступним прийомом.

Необхідно після конструкції "В" поставити дужки і між дужками залишити порожнє місце (пробіл), виділити це місце і викликати контруктор запитів. Контруктору буде доступно всі таблиці пакетного запиту. Прийом працює як на віртуальних таблицях регістрів, так вкладки "Умови". В останньому випадку необхідно поставити галочку "П(вільна умова)" і увійти в режим редагування "F4".

Запитів часто вигадував на ходу і вони служать просто для відображення "прийомів", які я розглядав.

Хотів розглянути використання індексів у запитах, але дуже велика тема. Винесу до окремої статті, або пізніше додам тут.

upd1. Пункти 11,12
upd2. Пункти 13,14,15,16

Використовувана література:
Мова запитів "1С:Підприємства 8" - Е.Ю. Кришталева
Професійна розробка у системі 1С:Підприємство 8".

переглядів

Зберегти