Внимание! Перед вами ознакомительная версия урока, материалы которого могут быть неполными.
Войдите на сайт как ученик
Войдите как ученик, чтобы получить доступ к материалам школы
Соединение - одна из самых важных и нужных операций, выполняемых реляционными системами управления базами данных.
Соединения используются для того, чтобы сопоставить строки одной таблицы строкам другой таблицы.
Для того, чтобы осознать необходимость соединений давайте решим следующую задачу.
У нас в базе есть справочник Клиенты :
Справочник Цвета:
И справочник Ассоциации :
Наша задача вывести любимые ассоциации клиентов, основываясь на цвете.
Таким образом для Наташи любимой ассоциацией будет трава, так как её любимый цвет зелёный. А для Петра - солнце. Вы читаете ознакомительную версию урока, полноценные уроки находятся .
Для Андрея вообще нет подходящей ассоциации, так его любимый цвет красный, а ассоциаций красного цвета в базе нет.
Будем решать задачу постепенно.
Сначала запросим всех клиентов и их любимые цвета :
Затем запросим все ассоциации и их цвета :
Если мы попробуем выполнить этот запрос, то получим ошибку:
Причина ошибки в том, что поле Наименование присутствует сразу в обеих таблицах (Клиенты и Ассоциации ) и система просто не знает поле из какой именно таблицы имеется в виду.
Чтобы устранять подобные неоднозначности при выборке из более чем одной таблицы принято указывать полные названия полей. Полное название поля включает в себя полное имя таблицы (например, Справочник.Клиенты ) и имя самого поля (например, Наименование ).
Таким образом полное название поля Наименование из таблицы Клиенты будет Справочник.Клиенты.Наименование .
А полное названия поля Наименование из таблицы Ассоциации будет Справочник.Ассоциации.Наименование .
Перепишем предыдущий запрос с полными именами полей:
Только что мы произвели перекрёстное соединение двух таблиц. Обратите внимание на то, каким образом сформировался результат:
Очевидно, что результат перекрестного соединения двух таблиц не есть решение нашей задачи. Нам нужны не все записи из перекрёстного соединения, а только те у которых поля ЛюбимыйЦвет и Цвет имеют одинаковое значение:
Чтобы получить эти записи добавим к предыдущему запросу секцию ГДЕ :
Это то, что нужно - мы решили, поставленную задачу!
В последнем запросе мы использовали перекрёстное соединение с дополнительным условием (в секции ГДЕ ). Вы читаете ознакомительную версию урока, полноценные уроки находятся . Такое соединение называется внутренним .
Есть ещё один вариант написания того же самого внутреннего соединения :
Сравните этот и предыдущий запрос. Они совершенно одинаковы с точки зрения платформы, просто имеют разный синтаксис. И этот и предыдущий запросы содержат внутреннее соединение таблицы Клиенты с таблицей Ассоциации по полям ЛюбимыйЦвет и Цвет соответственно.
Обратите внимание на то, что в результат внутреннего соединения не попал Андрей. А всё потому, что его любимый цвет красный, а красных ассоциаций у нас в базе нет вовсе.
Получается, что для Андрея с его красным цветом просто не нашлось пары из таблицы ассоциаций.
Перепишем запрос так, чтобы в результат попадали в том числе те записи из первой таблицы, для которых не нашлось ни одной пары из второй таблицы (в данном случае Андрей):
Такое соединение называется левым соединением .
Р езультат левого соединения представляет из себя: все записи из внутреннего соединения ПЛЮС все записи из первой таблицы , не попавшие во внутреннее
Но давайте снова вернёмся к внутреннему соединению:
Обратите внимание на то, что результат внутреннего соединения не содержит ассоциацию белый снег, так как не нашлось ни одного клиента, у которого любимым цветом был бы белый.
Перепишем запрос так, чтобы в результат попадали в том числе те записи из второй таблицы, для которых не нашлась ни одной пары из первой таблицы (в данном случае белый снег):
Такое соединение называется правым соединением .
Результат правого соединения представляет из себя: ПЛЮС все записи из второй таблицы , не попавшие во внутреннее соединение (для которых не нашлось пары).
А что если нам нужно, чтобы в результат запроса попадали помимо внутреннего соединения Андрей и Снег одновременно?
Для этого потребуется совместить результаты левого и правого соединений. Такой вид соединения уже придуман и называется полным соединением:
Результат полного соединения представляет из себя: все записи из внутреннего соединения ПЛЮС все записи из первой таблицы, не попавшие во внутреннее соединение (для которых не нашлось пары) ПЛЮС все записи из второй таблицы, не попавшие во внутреннее соединение (для которых не нашлось пары).
Согласитесь, что все запросы, которые мы писали в этом уроке выглядят довольно громоздко. Это связано с тем, что мы вынуждены указывать полные имена полей, чтобы избежать возникновение неоднозначности.
Чтобы сократить полное написание имени таблицы (например, Справочник.Клиенты ) допустимо (как и для самих полей) использовать псевдонимы .
Давайте перепишем последний запрос так, чтобы при формировании полных имён полей вместо Справочник.Клиенты
можно было использовать псевдоним К
, а вместо Справочник.Ассоциации
- псевдоним А
:
А чтобы результат запроса был ещё нагляднее добавим псевдонимы полей, которые мы уже рассматривали на одном из прошлых уроков:
Присмотритесь к результатам последнего запроса (как впрочем и многих предыдущих на этом уроке).
Чему равны значения полей Ассоциация и ЕёЦвет для первой строчки? А что вы скажете насчет полей Клиент и ЕгоЦвет для последней строки?
Они равны NULL, которое как мы уже знаем означает отсутствие какого либо значения:
А так как NULL означает отсутствие значения, то любая попытка выполнить с ним какую-либо операцию (сравнение, сложение...) вызовет неопределенное поведение базы данных, непредсказуемую ошибку.
Поэтому обязательной считается обработка значений NULL всегда, когда они могут возникнуть.
Под обработкой подразумевается то, что мы должны сказать в нашем запросе, что если одно из полей будет равно NULL , то в это поле следует подставить какое-то другое значение.
В данном случае для полей Клиент и Ассоциация в случае обнаружения NULL мы будем подставлять пустую строку "".
А вот поля ЕгоЦвет и ЕёЦвет являются ссылками на элементы справочника Цвета , поэтому в них можно подставлять только значения являющиеся ссылками указанных типов. Каждый ссылочный тип (например, Справочник или Документ) имеет предопределенный элемент ПустаяСсылка . Чтобы указать его значение в запросе воспользуемся функцией ЗНАЧЕНИЕ .
Для определения того, что в поле попало NULL будем использовать уже знакомую нам по прошлым урокам функцию ЕСТЬNULL :
ВЫБРАТЬ ЕСТЬNULL( К. Наименование, "" ) КАК Клиент, ЕСТЬNULL( К. ЛюбимыйЦвет, ЗНАЧЕНИЕ(Справочник. Цвета. ПустаяСсылка) ) КАК ЕгоЦвет, ЕСТЬNULL( А. Наименование, "" ) КАК Ассоциация, ЕСТЬNULL( А. Цвет, ЗНАЧЕНИЕ(Справочник. Цвета. ПустаяСсылка) ) КАК ЕёЦвет ИЗ Справочник. Клиенты КАК К ПОЛНОЕ СОЕДИНЕНИЕ Справочник. Ассоциации КАК А ПО К. ЛюбимыйЦвет = А. Цвет |
С виду (из консоли запросов) результат не изменился. Мы по-прежнему видим пустые поля. Но это только потому, что строковые представления у NULL и у пустых полей всех типов совпадают и равны пустой строке.
На самом же деле эти пустые поля уже не есть NULL (отсутствие значения), теперь в них появились значения (пустые), с которыми уже можно работать (совершать операции).
Запомните пустое значение и отсутствие значение - это две большие разницы.
Можно последовательно соединять сколько угодно таблиц.
Предположим нужно решить следующую задачу. Вы читаете ознакомительную версию урока, полноценные уроки находятся . Вывести все возможные варианты клиентов и их любимых ассоциаций и их любимых продуктов исходя из их любимого цвета.
Для этого последовательно соединим по цвету таблицу Клиенты с таблицей Ассоциации , а затем (получившийся результат) с таблицей Еда :
Начать тест
1. Соединения используются для того, чтобы
Когда мы хотим видеть данные из нескольких таблиц одновременно, т.е. собрать несколько таблиц в одну возникает понятие соединения таблиц и связей между ними. Соединения бывают четырех типов:
Каждый тип мы рассмотрим на абстрактном примере. Имеется 2 таблицы, в первой храним описательную информацию о номенклатуре, во второй о ее остатках:
Для того, чтобы получить из этих таблиц одну нам необходимо явным образом указать какие поля мы будем связывать, по какому условию и типу. Сейчас станет более понятно.
Используя левое соединение мы говорим системе, что в результате хотим видеть все записи из левой таблицы и записи из правой удовлетворяющие условию связи. Допустим мы связываем таблицы по полю товар с условием равно, тогда получим таблицу вида:
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Товар,
| Номенклатура.Цвет КАК ЦветНоменклатура,
| Остатки.Цвет КАК ЦветОстатки,
| Остатки.Количество
|ИЗ
";
Для стула не нашлось сопоставлений из таблицы остатков, поэтому поля заполнились значениями NULL, которые обязательно нужно обработать функцией ЕСТЬNULL, см. Функции языка запросов 1С 8.
Левое соединение работает примерно как цикл в цикле - берется первая запись из левой таблицы и пробегаются все записи из правой на предмет удовлетворения условию связи. Затем берется вторая запись из левой таблицы и т.д. Если вдруг условию связи удовлетворяют несколько записей из правой таблицы, то в результирующую таблицу будет добавлено несколько строк (по количеству удачных связей).Как видим, полученная таблица не информативна, данные не отражают реальную суть, поэтому лучше связать эти таблицы по двум полям: Товар и Цвет, только на этот раз обработаем NULLы:
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Товар,
| Номенклатура.Цвет,
| ЕСТЬNULL(Остатки.Количество, 0) КАК Количество
|ИЗ
| Номенклатура КАК Номенклатура
| ЛЕВОЕ СОЕДИНЕНИЕ Остатки КАК Остатки
| ПО Номенклатура.Товар = Остатки.Товар
Правое соединение по сути ничем не отличается от левого. Если поменять таблицы местами, то правое соединение превратится в левое, более того, при использовании конструктора система сама преобразует все правые соединения в левые.
Используя внутреннее соединение мы говорим системе, что в результате хотим видеть только те записи, которые удовлетворяют условию связи как из правой таблицы, так и из левой. Таким образом, количество результирующих записей будет меньше или равно количеству записей самой "короткой таблицы", участвующей в соединении. Применим внутреннее соединение к полям Товар и Цвет наших таблиц:
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Товар,
| Номенклатура.Цвет,
| Остатки.Количество КАК Количество
|ИЗ
| Номенклатура КАК Номенклатура
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Остатки КАК Остатки
| ПО Номенклатура.Товар = Остатки.Товар
| И Номенклатура.Цвет = Остатки.Цвет";
Полное соединение передаст в результат все записи из обоих таблиц, те записи, которые удовлетворяет условию связи - будут связаны, те записи, которые не удовлетворяют условию связи все равно окажутся в результате запроса, но с частью NULLовых полей. Полное это как бы левое и правое соединения в одном.
Задач на эту тему может быть много, давайте попробуем решить одну из них. Наша организация является дилером 2 мебельных фабрик: "Заря" и "Рассвет". Ассортимент со стоимостью каждой из фабрик хранится в разных таблицах. Необходимо составить единый прайс лист, причем брать в него продукцию по минимальной цене:
Применим полное соединение с выборкой всех полей, связывать будем по товару:
Запрос.Текст =
"ВЫБРАТЬ
| НоменклатураЗаря.Товар КАК ТоварЗаря,
| НоменклатураЗаря.Цена КАК ЦенаЗаря,
| НоменклатураРассвет.Товар КАК ТоварРассвет,
| НоменклатураРассвет.Цена КАК ЦенаРассвет
|ИЗ
Это не совсем то, что нам нужно, давайте соединим поле товар в одно и обработаем NULLы:
Запрос.Текст =
"ВЫБРАТЬ
//конструкция ЕСТЬNULL рассматривалась в разделе функции языка запросов
//если цена не определена, то инициализируем ее
//почему 1000000 см. пояснения ниже
| ЕСТЬNULL(НоменклатураЗаря.Цена, 1000000) КАК ЦенаЗаря,
| ЕСТЬNULL(НоменклатураРассвет.Цена, 1000000) КАК ЦенаРассвет
|ИЗ
| НоменклатураЗаря КАК НоменклатураЗаря
| ПОЛНОЕ СОЕДИНЕНИЕ НоменклатураРассвет КАК НоменклатураРассвет
| ПО НоменклатураЗаря.Товар = НоменклатураРассвет.Товар";
Осталось только выбрать минимальную цену. Итоговый тект запроса будет выглядеть следующим образом:
Запрос.Текст =
"ВЫБРАТЬ
| ЕСТЬNULL(НоменклатураЗаря.Товар, НоменклатураРассвет.Товар) КАК Товар,
| ВЫБОР
| КОГДА ЕСТЬNULL(НоменклатураЗаря.Цена, 1000000) > ЕСТЬNULL(НоменклатураРассвет.Цена, 1000000)
| ТОГДА ЕСТЬNULL(НоменклатураРассвет.Цена, 1000000)
| ИНАЧЕ ЕСТЬNULL(НоменклатураЗаря.Цена, 1000000)
| КОНЕЦ КАК Цена
|ИЗ
| НоменклатураЗаря КАК НоменклатураЗаря
| ПОЛНОЕ СОЕДИНЕНИЕ НоменклатураРассвет КАК НоменклатураРассвет
| ПО НоменклатураЗаря.Товар = НоменклатураРассвет.Товар";
Если цена не определена (NULL), то ее необходимо инициализировать каким либо значением, иначе операция сравнения на больше/меньше вывалится с ошибкой. инициализируем цену нереально большой суммой, чтобы она "проиграла" в операции сравнения, ведь по условию задачи мы подбираем наименьшую цену.
← Функции языка запросов 1C 8 | Объединения в запросах 1С 8 →
А сейчас рассмотрим подробнее соединение таблиц.
Напомню, что видов соединений в языке запросов 1С8 может быть несколько, а именно: ЛЕВОЕ СОЕДИНЕНИЕ , ПРАВОЕ СОЕДИНЕНИЕ , ВНУТРЕННЕЕ СОЕДИНЕНИЕ , ПОЛНОЕ СОЕДИНЕНИЕ . Рассмотрим каждое из них подробно.
В случае левого соединения данные из одной таблицы выбираются полностью, а из таблицы, которая присоединяется справа, выбираются только те записи для которых выполняется одно или несколько условий по которым эти таблицы соединяются. Из этого предложения конечно же мало, что можно понять, поэтому разберем пример.
Есть таблица «Товары»:
Сформируем эту таблицу в консоли запросов 1С с помощью вот такого запроса:
ВЫБРАТЬ "001" КАК КодТовара, "Яблоки" КАК Наименование ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "002", "Апельсины" ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "003", "Мандарины"
А еще есть таблица «Страны» вот такого вида:
КодТовара | Страна |
---|---|
001 | Россия |
002 | Турция |
003 | Марокко |
Также сконструируем ее запросом:
ВЫБРАТЬ "001" КАК КодТовара, "Россия" КАК Страна ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "002", "Турция" ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "003", "Марокко"
А теперь соединим эти две таблицы с помощью левого соединения, чтобы получить одну, в которой будет и код товара, и наименование, и страна. Для этого поместим наши таблицы во временные и в пакетном запросе выполним левое соединение.
Записи будем связывать конечно же по полям КодТовара
ВЫБРАТЬ "001" КАК КодТовара, "Яблоки" КАК Наименование ПОМЕСТИТЬ ВТ_Товар ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "002", "Апельсины" ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "003", "Мандарины" ; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ "001" КАК КодТовара, "Россия" КАК Страна ПОМЕСТИТЬ ВТ_Страна ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "002", "Турция" ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "003", "Марокко" ; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ ВТ_Товар.КодТовара, ВТ_Товар.Наименование, ВТ_Страна.Страна ИЗ ВТ_Товар КАК ВТ_Товар ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Страна КАК ВТ_Страна ПО ВТ_Товар.КодТовара = ВТ_Страна.КодТовара
Можете скопировать этот код в консоль запросов и выполнив его получите вот такой результат:
Здесь мы рассмотрели идеальную ситуацию, когда каждой записи в левой таблице соответствует одна запись в правой. Давайте немного усложним.
Допустим для нашего товара у нас есть еще цены. Причем они могут меняться в зависимости от даты:
КодТовара | Дата | Цена |
---|---|---|
001 | 01.01.2016 | 120 |
001 | 01.02.2016 | 150 |
002 | 01.01.2016 | 200 |
004 | 01.01.2016 | 350 |
Здесь мы видим, что для товара с кодом 001 (Яблоки) у нас 2 записи в таблице цен. Для мандаринов цена отсутствует. И кроме того в ценах есть код товара 004, которого нет в таблице с товарами. Давайте выполним левое соединение для товаров и цен с условием связи по полю код и посмотрим, что у нас получится.
ВЫБРАТЬ "001" КАК КодТовара, "Яблоки" КАК Наименование ПОМЕСТИТЬ ВТ_Товар ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "002", "Апельсины" ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "003", "Мандарины" ; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ "001" КАК КодТовара, "01.01.2016" КАК Дата, 120 КАК Цена ПОМЕСТИТЬ ВТ_Цена ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "001", "01.02.2016", 150 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "002", "01.01.2016", 200 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "004", "01.01.2016", 350 ; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ ВТ_Товар.КодТовара, ВТ_Товар.Наименование, ВТ_Цена.Цена, ВТ_Цена.Дата ИЗ ВТ_Товар КАК ВТ_Товар ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Цена КАК ВТ_Цена ПО ВТ_Товар.КодТовара = ВТ_Цена.КодТовара
После выполнения запроса видим вот такую картину:
Проанализируем полученный результат. Из таблицы «Товары» были выбраны все записи, т.к. она у нас основная. Для мандаринов цена имеет значение NULL, т.к. записи с кодом 003 нет в таблице с ценами. А запись с кодом 004 из таблицы цен (а она у нас вспомогательная) была проигнорирована, т.к. для нее нет соответствия в таблице товаров. И как мы видим у нас появилось 2 записи с яблоками несмотря на то, что в таблице товаров запись с кодом 001 одна. В этом и заключается некоторое коварство соединений таблиц.
И на эти грабли наступают как начинающие, так и опытные программисты. Когда запись в основной таблице одна, а в присоединяемой несколько записей удовлетворяют условию соединения, то в итоговой таблице количество записей будет равно количеству записей во вспомогательной таблице. В нашем случае произошло задвоение записи с яблоками. Этот момент надо обязательно учитывать при написании запросов и проверять при тестировании.
Оно практически ничем не отличается от левого. В итоговую таблицу попадают все записи из правой таблицы, а из левой только те, для которых выполняется условие соединения. На практике правое соединение практически не используется и рассматривать его отдельно не будем. Есть даже небольшой забавный момент, связанный с правым соединением в конструкторе запросов 1С. Если при составлении запроса в конструкторе попытаться использовать правое соединение, то после нажатия кнопки ОК правое соединение будет автоматически преобразовано в левое.
В случае внутреннего соединения в итоговую таблицу попадут только те записи как из левой, так и из правой таблицы, для которых выполняется условие соединения. Если в нашем последнем запросе мы заменим строку
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Цена КАК ВТ_Цена
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Цена КАК ВТ_Цена
то в итоге получим:
То есть запись с мандаринами у нас исчезла, т.к. для нее не нашлось соответствия в таблице с ценами.
В случае полного соединения в итоговую таблицу попадут все записи из обоих таблиц.
В нашем случае при использовании строки соединения
ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Цена КАК ВТ_Цена
получим следующий результат
На практике полное соединение используется достаточно редко, но иногда бывает необходимо.
Для тех кто только начинает работать с запросами 1С8 советую посмотреть как на закладке «Связи».
; Вложенные запросы (в разработке).
Задача №1: выбрать запросом все проведенные документы Реализация товаров и услуг,
Пояснение: конфигурациях Бухгалтерия 2.0 / 3.0 в реализации не хранится ссылка на выставленную счет-фактуру. Связь этих документов обратная: в счет-фактуре (в реквизите ДокументОснование ) хранится ссылка на реализацию к которой она относится. Таким образом чтобы понять у каких реализаций нет счет-фактур, потребуется использовать в запросе две таблицы: РеализацияТоваровУслуг и СчетФактураВыданный .
Задача №2: допустим в базе у справочника Договоры контрагентов создано дополнительное сведение Отсрочка (место хранения регистр Дополнительные сведения ). Выбрать запросом все договоры указанного контрагента с отсрочкой от 0 до 10 дней.
Пояснение: в данной задаче можно было бы обойтись без использования двух таблиц, если бы не одно условие: выбор договоров с нулевой отсрочкой. Дело в том, что в регистре Дополнительные сведения не хранятся пустые значения для дополнительных сведений, соответственно записей для нулевой отсрочки там не будет. Поэтому придется присоединять данный регистр к справочнику Договоры контрагентов , если не найдется запись с отсрочкой для какого-то из договоров, значит она нулевая.
Новые вкладки: Связи.
Новые механизмы: проверка на null в запросе 1с.
Если в запросе 1с используется только одна таблица, то данные берутся только из нее. Но если вам необходимо выбирать данные из нескольких таблиц, тогда следует настроить связи между этими таблицами. Связи необходимы для того, чтобы сопоставить строки одной таблицы базы данных со строками другой таблицы и таким образом получить при формировании запроса итоговую таблицу в которой будут присутствовать необходимые данные всех таблиц запроса.
Каждая конкретная связь настраивается между двумя таблицами, одна из них называется Левая , другая Правая . Связи бывают трех типов:
В теории соединение таблиц запроса довольно сложно понять для неподготовленного программиста 1с, но при изучении примеров все непонятные моменты прояснятся.
В конструкторе запросов перейдем на вкладку Связи . Она представляет из себя таблицу в которой создается нужное количество связей для всех различных пар таблиц. Добавлять, удалять, копировать и изменять связи можно при помощи кнопок находящихся над таблицей (подробно разбирать каждую не будем).
После добавления новой связи необходимо заполнить все колонки таблицы. Разберем каждую колонку таблицы связей:
Заполнение и редактирование Условия связи , аналогично редактированию обычного условия, разобранного в прошлом уроке, за тем исключением, что по умолчанию в правой части условия задается не параметр, а поле правой таблицы. В произвольном режиме также доступен редактор произвольных выражений.
Разберем решение задач, приведенных в начале урока.
Выбрать запросом все проведенные документы Реализация товаров и услуг, за указанный период, у которых не создана счет-фактура.
При левом соединении, если для строки левой таблицы не нашлось ни одной строки правой таблицы удовлетворяющей условиям связи, все поля правой правой таблицы для данной строки левой таблицы имеют значение NULL . Воспользуемся этим для того, чтобы отобрать только те строки левой таблицы, для которых нет счет-фактуры;
В языке запросов 1с есть специальный логический оператор, позволяющий проверить содержится ли в поле значение NULL , его синтаксис <Поле> ЕСТЬ NULL . Он возвращает значение Истина , если поле имеет значение NULL и Ложь — в обратно случае.
Замечу, что проверить значение поля на NULL можно только при помощи указанной функции, конструкцией <Поле> = NULL осуществить данную проверку не получится.
Запрос = Новый Запрос; Запрос.УстановитьПараметр("НачалоПерида" , НачалоПерида); Запрос.УстановитьПараметр("ОкончаниеПериода", ОкончаниеПериода); Запрос.Текст = "ВЫБРАТЬ | РеализацияТоваровУслуг.Ссылка |ИЗ | Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг | ЛЕВОЕ СОЕДИНЕНИЕ Документ.СчетФактураВыданный КАК СчетФактураВыданный | ПО РеализацияТоваровУслуг.Ссылка = СчетФактураВыданный.ДокументОснование |ГДЕ | РеализацияТоваровУслуг.Дата МЕЖДУ &НачалоПерида И &ОкончаниеПериода | И РеализацияТоваровУслуг.Проведен | И СчетФактураВыданный.Ссылка ЕСТЬ NULL";
По условию в результат запроса должны попасть договоры только одного контрагента, значение которого будет передано в запрос при помощи параметра. Контрагент, к которому принадлежит договор хранится в поле Владелец справочника Договоры контрагентов .
Мы добавили связь, при помощи которой мы выберем все доп. сведения каждого договора. Но так как нам нужно только одно доп. сведение — Отсрочка , нам необходимо добавить еще одну связь. В данном случае Отсрочка — это дополнительное свойство. Дополнительные свойства имеют тип План видов характеристик ДополнительныеРеквизитыИСведения. В регистре ДополнительныеСведения значение доп. свойства хранится в измерении Свойство . Таким образом нам необходимо добавить связь ограничивающую дополнительные сведения только свойством Отсрочка.
Теперь осталось наложить условие на размер отсрочки. Так как у нас используется интервал от 0 до 10 необходимо будет использовать оператор сравнения Между. Так как нулевая отсрочка не хранится в дополнительных сведениях, при соединении в данном случае все поля регистра будут возвращать значение NULL . Для того чтобы в условии подменить значение NULL на 0, будем использовать функцию языка запросов ЕСТЬNULL(<Выражение1>, <Выражение2>) . Функция возвращает Выражение1 , если оно не равно NULL и Выражение2 в обратном случае.
Последнее, что необходимо сделать — вывести значение отсрочки в поля запроса. Для того чтобы вместо NULL выводилоcь значение 0, также используем редактор произвольных выражений и функцию ЕСТЬNULL .
В итоге у нас получится запрос со следующим текстом:
Запрос = Новый Запрос; Запрос.УстановитьПараметр("СвойствоОтсрочка", СвойствоОтсрочка); Запрос.УстановитьПараметр("Контрагент", Контрагент); Запрос.Текст = "ВЫБРАТЬ | ДоговорыКонтрагентов.Ссылка, | ЕСТЬNULL(ДополнительныеСведения.Значение, 0) КАК Поле1 |ИЗ | Справочник.ДоговорыКонтрагентов КАК ДоговорыКонтрагентов | ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДополнительныеСведения | КАК ДополнительныеСведения | ПО ДоговорыКонтрагентов.Ссылка = ДополнительныеСведения.Объект | И (ДополнительныеСведения.Свойство = &СвойствоОтсрочка) |ГДЕ | ДоговорыКонтрагентов.Владелец = &Контрагент | И ЕСТЬNULL(ДополнительныеСведения.Значение, 0) МЕЖДУ 0 И 10";
Также прочтите статьи о языке запросов 1с 8.
Язык запросов 1С — одно из основных отличий между версиями 7.7 и 8. Одним из важнейших пунктов в изучении 1С-программирования является язык запросов. В 1С 8.3 запросы — самый мощный и эффективный инструмент получения данных. Язык запросов позволяет в удобном виде получать информацию из базы данных.
Сам синтаксис очень сильно напоминает классический T-SQL, за исключением того, что в 1С с помощью языка запросов можно только получать данные, используя конструкцию Выбрать(select). Язык поддерживает и более сложные конструкции, например, (запрос в запросе). Запросы в 1С 8 можно составлять как на кириллице, так и на латинице.
В статье я постараюсь рассказать про основные ключевые слова в языке запросов 1С:
А так же небольшие хитрости языка 1С, применяя которые вы сможете оптимально построить текст запроса.
Для отладки запросов в системе 1С 8.2 предусмотрен специальный инструмент — консоль запросов. Увидеть описание и скачать его можно по ссылке — .
Рассмотрим самые важные и интересные операторы языка запросов 1С.
В языке запросов 1С предприятия 8 любой запрос начинается с ключевого слова ВЫБРАТЬ . В языке 1С нет конструкций UPDATE, DELETE, CREATE TABLE, INSERT, эти манипуляции производятся в объектной технике. Предназначение его — только чтение данных.
Например:
ВЫБРАТЬ
ТекущийСправочник.Наименование
ИЗ
Справочник.Номенклатура КАК ТекущийСправочник
Запрос вернёт таблицу с наименованиями номенклатуры.
Рядом с конструкцией ВЫБРАТЬ можно встретить ключевые слова ДЛЯ ИЗМЕНЕНИЯ , РАЗРЕШЕННЫЕ , РАЗЛИЧНЫЕ , ПЕРВЫЕ …
РАЗРЕШЕННЫЕ — выбирает только записи из таблицы, на которые есть права у текущего пользователя.
РАЗЛИЧНЫЕ — означает, что в результат не попадет дублирующих строк.
Очень часто данная конструкция недооценивается программистами. Пример её использования:
ТекущийСправочник.Наименование,
КОГДА ТекущийСправочник.Услуга ТОГДА
«Услуга»
КОНЕЦ КАК ВидНоменклатуры
Справочник.Номенклатура КАК ТекущийСправочник
Пример возвратит в поле «ВидНоменклатуры» текстовое значение — «Товар» или «Услуга».
Конструкция языка запросов 1C, позволяющая наложить отбор на получаемые данные. Учтите, что от сервера система получает все данные, а только потом они отбираются по данному параметру.
ВЫБРАТЬ
Справочник.Наименование
ИЗ
ТекущийСправочник.Номенклатура КАК ТекущийСправочник
ГДЕ ТекущийСправочник.Услуга = ИСТИНА
В примере мы отбираем записи, у которых значение реквизита «Услуга» установлено в положение «Истина». В данном примере можно было бы обойтись и таким условием:
«ГДЕ Услуга»
По сути, мы отбираем строки, у которых выражение после ключевого слова равно «Истина».
В выражениях можно использовать прямые условия:
ГДЕ Код = «005215»
С помощью оператора «ЗНАЧЕНИЕ()» в условиях использовать обращение к предопределенным элементам и перечислениям в запросе 1С:
ГДЕ ТипНоменклатуры= Значение(Перечисление.ТипыНоменклатуры.Товар)
Значения времени могут указываться следующим образом:
ГДЕ ДатаПоступления > ДАТАВРЕМЯ(2012,01,01):
Чаще всего условия указываются в виде параметров, передаваемых в запрос:
Получите 267 видеоуроков по 1С бесплатно:
ГДЕ НоменклатурнаяГруппа= &НоменклатурнаяГруппа
Условие можно наложить на тип реквизита, если он составного типа:
Если необходимо ограничивать отбор из списка значений или массива, можно поступить следующим образом:
ГДЕ РегистрНакопления.Регистратор В (&СписокДокументовДляОтбора)
Условие может быть и сложное, состоящее из нескольких условий:
ГДЕ ДатаПоступления > ДАТАВРЕМЯ(2012,01,01) И НоменклатурнаяГруппа= &НоменклатурнаяГруппа И НЕ Услуга
Конструкция языка запросов 1С 8.2, используемая для группировки результата.
Например:
ВЫБРАТЬ
ПоступлениеТоваровУслугTовары.Товар,
СУММА(ПоступлениеТоваровУслугTовары.Количество) КАК Количество,
СУММА(ПоступлениеТоваровУслугTовары.Сумма) КАК Сумма
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТоваровУслугTоварыСГРУППИРОВАТЬ ПО
ПоступлениеТоваровУслугTовары.Товар
Данный запрос просуммирует все поступления по сумме и количеству в разрезе номенклатуры.
Помимо ключевого слова СУММА можно использовать другие агрегатные функции: КОЛИЧЕСТВО , КОЛИЧЕСТВО РАЗНЫХ , МАКСИМУМ , МИНИМУМ , СРЕДНЕЕ .
Конструкция, о которой часто забывают, но она очень важна и полезна. Она позволяет указать отбор в виде агрегатной функции, этого нельзя сделать в конструкции ГДЕ .
Пример использования ИМЕЮЩИЕ в запросе 1С:
ВЫБРАТЬ
ПоступлениеТоваровУслугТовары.Товар,
СУММА(ПоступлениеТоваровУслугТовары.Количество) КАК Количество,
СУММА(ПоступлениеТоваровУслугТовары.Сумма) КАК Сумма
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТоваровУслугТоварыСГРУППИРОВАТЬ ПО
ПоступлениеТоваровУслугТовары.товарСУММА(ПоступлениеТоваровУслугТовары.Количество) > 5
Так мы отберем количество товаров, которых поступило более 5 штук.
Например:
ГДЕ Банк = Значение(Справочник.Банки.ПустаяСсылка)
ГДЕ ВидНоменклатуры = Значение(Справочник.ВидыНоменклатуры.Товар)
ГДЕ ТипНоменклатуры= Значение(Перечисление.ТипыНоменклатуры.Услуга)
Тип данных можно проверить следующим образом: с помощью функций ТИП() и ТИПЗНАЧЕНИЯ() или с помощью логического оператора ССЫЛКА.
Оператор Выразить в запросах 1С служит для преобразования типов данных.
Синтаксис: ВЫРАЗИТЬ(<Выражение> КАК <Тип значения>)
С помощью него можно преобразовать строковые значения в дату или ссылочные в строковые данные и так далее.
В практическом применении оператор Выразить() очень часто используется для преобразования полей неограниченной длины, потому что поля неограниченной длины нельзя отбирать, группировать и тд. Если такие поля не преобразовывать, Вы получите ошибку Нельзя сравнивать поля неограниченной длины и поля несовместимых типов .
ВЫБРАТЬ
КонтактнаяИнформация.Объект,
ВЫРАЗИТЬ(КонтактнаяИнформация.Представление КАК СТРОКА(150)) КАК Представление
ИЗ
РегистрСведений.КонтактнаяИнформация КАК КонтактнаяИнформацияСГРУППИРОВАТЬ ПО
ВЫРАЗИТЬ(КонтактнаяИнформация.Представление КАК СТРОКА(150)),
КонтактнаяИнформация.Объект
Достаточно полезная функция языка запросов 1С, которая проверяет значение в записи, и если оно равно NULL, то позволяет заменить на своё значение. Чаще всего используется при получении виртуальных таблиц остатков и оборотов, чтобы скрыть NULL и поставить понятный 0 (ноль).
ЕСТЬNULL(НалогиПредМесяца.ПримененнаяЛьготаФСС, 0)
Такая функция языка запросов 1С ЕСТЬNULL при отсутствии значения вернет ноль, что позволит избежать ошибки.
Соединения бывают 4 типов: ЛЕВОЕ , ПРАВОЕ , ПОЛНОЕ, ВНУТРЕННЕЕ .
Соединения используются для связи двух таблиц по определенному условию. Особенность при ЛЕВОМ СОЕДИНЕНИИ в том, что мы берём первую указанную таблицу полностью и привязываем по условию вторую таблицу. Поля второй таблицы, которые не удалось привязать по условию, заполняются значением NULL .
Пример левого соединения в запросе 1С:
Вернет всю таблицу и заполнит поле «Банк» лишь в тех местах, где будет соблюдаться условие «Контрагенты.Наименование = Банки.Наименование». Если условие не соблюдается, в поле Банк будет установлено NULL .
ПРАВОЕ СОЕДИНЕНИЕ в языке 1С 8.3 абсолютно аналогично ЛЕВОМУ соединению , за исключением одного отличия: в ПРАВОМ СОЕДИНЕНИИ «главная» таблица — вторая, а не первая.
ПОЛНОЕ СОЕДИНЕНИЕ отличается от левого и правого тем, что выводит все записи из двух таблиц, соединяет лишь те, которые может соединить по условию.
Например:
ПОЛНОЕ СОЕДИНЕНИЕ
Справочник.Банки КАК БанкиПО
Язык запросов вернет обе таблицы полностью лишь по выполненному условию Соединить записи. В отличие от левого/правого соединения возможно появления NULL в двух полях.
ВНУТРЕННЕЕ СОЕДИНЕНИЕ отличается от полного тем, что выводит только те записи, которые смогли соединить по заданному условию.
Например:
ИЗ
Справочник.Контрагенты КАК КлиентыВНУТРЕННЕЕ СОЕДИНЕНИЕ
Справочник.Банки КАК БанкиПО
Клиенты.Наименование = Банки.Наименование
Данный запрос вернет только строки, в которых у банка и контрагента будет одинаковое наименование.
Это лишь небольшая часть синтаксиса из языка запросов 1С 8, в дальнейшем я попробую рассмотреть более подробно некоторые моменты, показать и многое другое!