Язык запросов: выборка, соединения, группировка и итоги

В этом уроке ты узнаешь

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

Если ты знаешь SQL, язык запросов 1С покажется знакомым — ключевые слова просто написаны по-русски. Но есть два важных отличия: запросы только читают данные (изменяют их объекты и наборы записей), а таблицы — это не таблицы СУБД, а объекты метаданных: Справочник.Товары, Документ.РасходТовара.Товары, РегистрНакопления.ОстаткиТоваров.

Первый запрос

1C Query

ВЫБРАТЬ
    Товары.Ссылка КАК Товар,
    Товары.Код КАК Код,
    Товары.Цена КАК Цена
ИЗ
    Справочник.Товары КАК Товары
ГДЕ
    Товары.Цена >= &МинЦена
    И НЕ Товары.ПометкаУдаления
УПОРЯДОЧИТЬ ПО
    Цена УБЫВ

&МинЦена — параметр; КАК задаёт псевдонимы полей и таблиц; УБЫВ — сортировка по убыванию.

1С	Английский синтаксис / SQL
`ВЫБРАТЬ`, `РАЗЛИЧНЫЕ`, `ПЕРВЫЕ 10`	`SELECT`, `DISTINCT`, `TOP 10`
`ИЗ`, `КАК`, `ГДЕ`	`FROM`, `AS`, `WHERE`
`СГРУППИРОВАТЬ ПО`, `ИМЕЮЩИЕ`	`GROUP BY`, `HAVING`
`УПОРЯДОЧИТЬ ПО … УБЫВ`	`ORDER BY … DESC`
`ЛЕВОЕ / ВНУТРЕННЕЕ / ПОЛНОЕ СОЕДИНЕНИЕ … ПО`	`LEFT / INNER / FULL JOIN … ON`
`ОБЪЕДИНИТЬ ВСЕ`	`UNION ALL`
`И`, `ИЛИ`, `НЕ`, `В`, `МЕЖДУ`, `ПОДОБНО`, `ЕСТЬ NULL`	`AND`, `OR`, `NOT`, `IN`, `BETWEEN`, `LIKE`, `IS NULL`
`СУММА`, `КОЛИЧЕСТВО`, `МАКСИМУМ`, `МИНИМУМ`, `СРЕДНЕЕ`	`SUM`, `COUNT`, `MAX`, `MIN`, `AVG`

В запросе можно обращаться через точку от поля-ссылки: РасходТовары.Ссылка.Дата даёт дату документа, а Товары.Ссылка.Родитель.Наименование — наименование группы. Для этого платформа за кулисами строит неявное соединение.

Чтобы выбрать значение по условию прямо в запросе, используют конструкцию ВЫБОР КОГДА … ТОГДА … ИНАЧЕ … КОНЕЦ, а чтобы указать значение перечисления, предопределённый элемент или пустую ссылку — функцию ЗНАЧЕНИЕ(). Так значение перечисления можно записать прямо в тексте, без параметра.

1C Query

ВЫБРАТЬ
    Оплаты.Ссылка КАК Документ,
    ВЫБОР
        КОГДА Оплаты.ВидОплаты = ЗНАЧЕНИЕ(Перечисление.ВидыОплаты.Наличные)
            ТОГДА "Наличные"
        ИНАЧЕ "Безналичные"
    КОНЕЦ КАК Способ
ИЗ
    Документ.Оплата КАК Оплаты
ГДЕ
    Оплаты.Контрагент <> ЗНАЧЕНИЕ(Справочник.Контрагенты.ПустаяСсылка)

ЗНАЧЕНИЕ(Справочник.Контрагенты.ПустаяСсылка) исключает документы с незаполненным контрагентом.

Объект `Запрос` в коде

Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
|    Товары.Наименование КАК Наименование,
|    Товары.Цена КАК Цена
|ИЗ
|    Справочник.Товары КАК Товары
|ГДЕ
|    Товары.Цена >= &МинЦена
|УПОРЯДОЧИТЬ ПО
|    Цена УБЫВ";
Запрос.УстановитьПараметр("МинЦена", 5);

Результат = Запрос.Выполнить();
Если Результат.Пустой() Тогда
    Сообщить("Ничего не найдено");
КонецЕсли;

Выборка = Результат.Выбрать();
Пока Выборка.Следующий() Цикл
    Сообщить(Выборка.Наименование + " - " + Выборка.Цена);
КонецЦикла;

Таблица = Результат.Выгрузить();  // ТаблицаЗначений

В английском синтаксисе: Новый Запрос = New Query, УстановитьПараметр = SetParameter, Выполнить = Execute, Выбрать = Select, Следующий = Next, Выгрузить = Unload.

Выбрать() возвращает выборку, которая читает результат построчно, — экономно для больших результатов. Выгрузить() загружает всё в память как ТаблицаЗначений — её удобно сортировать, сворачивать или передать в форму. Никогда не «вклеивай» значения в текст запроса конкатенацией строк — всегда используй &Параметр и УстановитьПараметр.

Соединения и группировка

1C Query

ВЫБРАТЬ
    Товары.Ссылка КАК Товар,
    ЕСТЬNULL(Продажи.Количество, 0) КАК Продано
ИЗ
    Справочник.Товары КАК Товары
        ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
            РасходТовары.Товар КАК Товар,
            СУММА(РасходТовары.Количество) КАК Количество
        ИЗ
            Документ.РасходТовара.Товары КАК РасходТовары
        ГДЕ
            РасходТовары.Ссылка.Проведен
            И РасходТовары.Ссылка.Дата МЕЖДУ &Начало И &Конец
        СГРУППИРОВАТЬ ПО
            РасходТовары.Товар) КАК Продажи
        ПО Товары.Ссылка = Продажи.Товар
УПОРЯДОЧИТЬ ПО
    Продано УБЫВ

Все товары, даже ни разу не проданные: ЛЕВОЕ СОЕДИНЕНИЕ сохраняет все строки левой таблицы, а ЕСТЬNULL заменяет NULL на 0.

Соединение	Какие строки остаются
`ВНУТРЕННЕЕ СОЕДИНЕНИЕ`	только строки, у которых есть пара в обеих таблицах
`ЛЕВОЕ СОЕДИНЕНИЕ`	все строки левой таблицы; без пары правые поля равны `NULL`
`ПОЛНОЕ СОЕДИНЕНИЕ`	все строки обеих таблиц

СГРУППИРОВАТЬ ПО группирует строки, а агрегатные функции СУММА, КОЛИЧЕСТВО, МАКСИМУМ считают по группе. Для условия на группу пишут не ГДЕ, а ИМЕЮЩИЕ: ИМЕЮЩИЕ СУММА(РасходТовары.Сумма) > 1000. ГДЕ отбирает строки до группировки, ИМЕЮЩИЕ — после.

Итоги: `ИТОГИ`

ИТОГИ — возможность 1С, которой нет в SQL: она добавляет в результат строки итогов по группировкам. Результат обходят вложенными выборками с ОбходРезультатаЗапроса.ПоГруппировкам: сначала итог по складу, затем его товары.

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

ВыборкаСклад = Запрос.Выполнить().Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
Пока ВыборкаСклад.Следующий() Цикл
    Сообщить(СтрШаблон("%1: %2", ВыборкаСклад.Склад, ВыборкаСклад.Сумма));
    ВыборкаСтрок = ВыборкаСклад.Выбрать();
    Пока ВыборкаСтрок.Следующий() Цикл
        Сообщить(СтрШаблон("    %1: %2", ВыборкаСтрок.Товар, ВыборкаСтрок.Сумма));
    КонецЦикла;
КонецЦикла;

1
Открой конструктор
Правый щелчок в модуле → Конструктор запроса…. Если нужен и код, выбери Конструктор запроса с обработкой результата… — он напишет и объект Запрос, и цикл обхода.
2
Выбери таблицы и поля
На закладке Таблицы и поля перетащи таблицы и поля; соединения задай на Связи, условия — на Условия, остальное — на Группировка и Порядок.
3
Проверь в консоли
Прежде чем запускать сложный запрос из кода, проверь его в консоли запросов (обработка Консоль запросов) в режиме 1С:Предприятие: задай параметры и сразу увидь результат.

Главное

Запросы только читают; источники — объекты метаданных (Справочник.X, Документ.X.Товары).
Передавай значения через &Параметр и УстановитьПараметр.
ЛЕВОЕ СОЕДИНЕНИЕ + ЕСТЬNULL — стандартный способ сохранить строки без пары.
ГДЕ отбирает до группировки, ИМЕЮЩИЕ — после; ИТОГИ добавляет итоги по группам.
Выбрать() — построчная выборка, Выгрузить() — ТаблицаЗначений.

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

1 / 10

Как передать в запрос значение извне?