Как создать отчет по справочнику в 1С с отбором по количеству строк табличной части?

← На главную

При разработке отчетов на платформе «1С:Предприятие» программисты часто сталкиваются с задачей вывода данных из справочников, содержащих табличные части. Классический пример — отчет по сотрудникам и их детям, где информация о детях (имя, дата рождения) хранится в табличной части справочника Сотрудники. Ситуация усложняется, когда в отчет нужно вывести только тех сотрудников, у которых количество детей соответствует определенному условию (например, больше нуля или строго равно заданному числу).

В этой статье мы подробно разберем, почему возникает популярная ошибка «Операция не разрешена в предложении ГДЕ», как правильно спроектировать запрос в Системе компоновки данных (СКД) и какие подходы к построению отчета являются наиболее оптимальными с точки зрения производительности СУБД.

Проанализируем исходную задачу. Нам необходимо получить сведения о сотрудниках (ФИО, дата рождения, должность) и их детях (имя, год рождения), при этом в отчет должны попадать только те сотрудники, у которых есть дети (или количество детей соответствует заданному параметру).

Выясняем причину ошибки: почему ГДЕ не работает с КОЛИЧЕСТВО()

Попытка наложить условие на количество строк табличной части «напрямую» в секции ГДЕ выглядит следующим образом:


ГДЕ
    Количество(Сотрудники.Дети.НомерСтроки) = &КоличествоДетей

При выполнении такого запроса платформа выдаст ошибку: «Операция не разрешена в предложении ГДЕ». Разберем, почему так происходит.

Дело в том, что предложение ГДЕ (в SQL-терминологии — WHERE) предназначено для фильтрации исходных строк выборки до того, как будет выполнено их объединение, группировка или агрегирование. СУБД физически не может подсчитать количество детей для конкретного сотрудника на этапе обработки секции ГДЕ, так как группировка данных еще не произведена.

Для фильтрации данных по результатам агрегатных функций (таких как КОЛИЧЕСТВО, СУММА, СРЕДНЕЕ, МАКСИМУМ) в языке запросов 1С предусмотрена специальная секция — ИМЕЮЩИЕ (в SQL — HAVING). Она применяется строго после секции СГРУППИРОВАТЬ ПО.

---

Решение №1. Использование предложения ИМЕЮЩИЕ во вложенном запросе

Рассмотрим первый рабочий вариант решения проблемы. Чтобы отсечь сотрудников, у которых количество детей не соответствует условию, мы можем использовать вложенный запрос. В этом вложенном запросе мы сгруппируем табличную часть Дети по сотруднику (полю Ссылка) и отберем только нужные ссылки с помощью предложения ИМЕЮЩИЕ.

Разберем по шагам структуру такого запроса:


ВЫБРАТЬ
    Сотрудники.Наименование КАК ФИО,
    Сотрудники.ДатаРождения КАК ДатаРождения,
    Сотрудники.Должность КАК Должность,
    Сотрудники.Дети.(
        Имя КАК ИмяРебенка,
        ГодРождения КАК ГодРожденияРебенка
    ) КАК Дети
ИЗ
    Справочник.Сотрудники КАК Сотрудники
ГДЕ
    Сотрудники.Ссылка В
        (ВЫБРАТЬ
            СотрудникиДети.Ссылка КАК Ссылка
        ИЗ
            Справочник.Сотрудники.Дети КАК СотрудникиДети
        СГРУППИРОВАТЬ ПО
            СотрудникиДети.Ссылка
        ИМЕЮЩИЕ
            КОЛИЧЕСТВО(СотрудникиДети.НомерСтроки) >= &КоличествоДетей)

Как это работает: внутренний запрос обращается к подчиненной таблице (табличной части Сотрудники.Дети), группирует записи по родителю (Ссылка) и подсчитывает количество строк (детей) для каждого. Предложение ИМЕЮЩИЕ КОЛИЧЕСТВО(...) >= &КоличествоДетей оставляет только те ссылки сотрудников, которые удовлетворяют условию. Основной запрос выбирает анкетные данные сотрудников по этому списку ссылок.

---

Решение №2. Использование временных таблиц (Рекомендуемый стандарт 1С)

Хотя вложенный запрос из предыдущего примера полностью работоспособен, стандарты разработки «1С» не рекомендуют использовать конструкцию ГДЕ Ссылка В (ВЫБРАТЬ...) на больших базах данных. Это связано с тем, что оптимизаторы СУБД могут неэффективно строить план запроса, что приведет к замедлению работы системы.

Рассмотрим более профессиональный подход с использованием механизма временных таблиц. Мы разобьем задачу на два простых шага:

Выберем ссылки сотрудников с нужным количеством детей во временную таблицу ВТ_ОтфильтрованныеСотрудники.
Соединим эту временную таблицу с основной таблицей справочника для получения детальных сведений.

Посмотрим на пример реализации такого запроса:


// Шаг 1: Фильтрация сотрудников по количеству детей во временную таблицу
ВЫБРАТЬ
    СотрудникиДети.Ссылка КАК Сотрудник
ПОМЕСТИТЬ ВТ_ОтфильтрованныеСотрудники
ИЗ
    Справочник.Сотрудники.Дети КАК СотрудникиДети
СГРУППИРОВАТЬ ПО
    СотрудникиДети.Ссылка
ИМЕЮЩИЕ
    КОЛИЧЕСТВО(СотрудникиДети.НомерСтроки) >= &КоличествоДетей
;

// Шаг 2: Получение итоговых данных
ВЫБРАТЬ
    Сотрудники.Наименование КАК ФИО,
    Сотрудники.ДатаРождения КАК ДатаРождения,
    Сотрудники.Должность КАК Должность,
    Сотрудники.Дети.(
        Имя КАК ИмяРебенка,
        ГодРождения КАК ГодРожденияРебенка
    ) КАК Дети
ИЗ
    Справочник.Сотрудники КАК Сотрудники
    // Внутреннее соединение гарантирует, что останутся только отфильтрованные сотрудники
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ОтфильтрованныеСотрудники КАК ВТ_ОтфильтрованныеСотрудники
        ПО Сотрудники.Ссылка = ВТ_ОтфильтрованныеСотрудники.Сотрудник

Такой запрос работает значительно быстрее на базах данных с большим объемом информации, поскольку временная таблица индексируется и соединение происходит по ключевому полю.

---

Особый случай: как вывести сотрудников, у которых вообще нет детей?

Разберем ситуацию, когда пользователь хочет получить список сотрудников, у которых детей нет (то есть параметр &КоличествоДетей равен 0). Если мы воспользуемся предыдущими запросами, они вернут пустой результат. Почему?

Потому что у бездетного сотрудника в табличной части Дети нет ни одной строки. Следовательно, запись об этом сотруднике вообще отсутствует в таблице Справочник.Сотрудники.Дети. Фильтрация по этой таблице просто «проигнорирует» таких сотрудников.

Чтобы корректно обработать нулевое количество, нам необходимо выполнить ЛЕВОЕ СОЕДИНЕНИЕ основной таблицы сотрудников с их табличной частью детей и использовать функцию ЕСТЬNULL для замены отсутствующих значений нулем.

Проанализируем пример запроса для этой задачи:


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

ВЫБРАТЬ
    Сотрудники.Наименование КАК ФИО,
    Сотрудники.ДатаРождения КАК ДатаРождения,
    Сотрудники.Должность КАК Должность,
    Сотрудники.Дети.(
        Имя КАК ИмяРебенка,
        ГодРождения КАК ГодРожденияРебенка
    ) КАК Дети
ИЗ
    Справочник.Сотрудники КАК Сотрудники
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_КоличествоДетей КАК ВТ_КоличествоДетей
        ПО Сотрудники.Ссылка = ВТ_КоличествоДетей.Сотрудник
ГДЕ
    ВТ_КоличествоДетей.КолДетей = &КоличествоДетей

Этот универсальный запрос корректно отработает при любом значении параметра &КоличествоДетей (будь то 0, 1, 2 или более).

---

Как правильно настроить отчет в Системе компоновки данных (СКД)

В приведенных выше примерах запросов мы использовали выборку табличной части через точку: Сотрудники.Дети.(...). Это классический метод получения встроенной (вложенной) таблицы.

Однако при работе с Системой компоновки данных (СКД) использование вложенных таблиц в запросах считается устаревшим подходом. СКД обладает мощными встроенными механизмами для объединения и группировки данных, поэтому профессиональные разработчики предпочитают передавать в СКД «плоскую» таблицу, а иерархию настраивать непосредственно в структуре отчета.

Давайте по шагам разберем, как сделать отчет «правильно» и красиво средствами СКД:

Шаг 1. Создание оптимального запроса для СКД

В схеме компоновки данных напишем плоский запрос, в котором свяжем сотрудника и его детей левым соединением:


ВЫБРАТЬ
    Сотрудники.Ссылка КАК Сотрудник,
    Сотрудники.Наименование КАК ФИО,
    Сотрудники.ДатаРождения КАК ДатаРождения,
    Сотрудники.Должность КАК Должность,
    СотрудникиДети.Имя КАК ИмяРебенка,
    СотрудникиДети.ГодРождения КАК ГодРожденияРебенка
ИЗ
    Справочник.Сотрудники КАК Сотрудники
    // Соединяем с табличной частью для получения списка детей
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Сотрудники.Дети КАК СотрудникиДети
        ПО Сотрудники.Ссылка = СотрудникиДети.Ссылка

Шаг 2. Настройка группировок в СКД

Для того чтобы ФИО сотрудника не дублировалось в каждой строке для каждого ребенка, мы настроим иерархическую структуру вывода на вкладке «Настройки» в схеме компоновки данных:

Добавим новую группировку по полю Сотрудник (или по полям ФИО, ДатаРождения, Должность).
Внутри этой группировки создадим подчиненную группировку Детальные записи.
На уровне родительской группировки (Сотрудник) в выбранные поля добавим только ФИО, Должность и Дату рождения.
На уровне детальных записей в выбранные поля добавим только ИмяРебенка и ГодРожденияРебенка.

При таком подходе СКД сформирует аккуратный структурированный отчет: имя сотрудника выведется один раз, а под ним списком будут выведены его дети. Более того, встроенные механизмы компоновки автоматически оптимизируют выполнение запросов к СУБД, обеспечивая наилучшую производительность вашей системы.