При создании отчетов в 1С на Системе Компоновки Данных (СКД) часто возникает задача, требующая выборки записей, удовлетворяющих сложному условию: найти объекты, которые связаны со всеми элементами из заданного списка. Это классический пример так называемого "реляционного деления" в SQL.
Рассмотрим конкретную ситуацию: необходимо сформировать отчет, который покажет все готовые изделия, в состав которых входят все материалы, выбранные нами в качестве параметра. Если мы указываем в параметрах материалы [Мат1, Мат2], то в результате должны быть только те изделия, в которых присутствуют ОБА материала. Изделия, содержащие только Мат1 или только Мат2, должны быть исключены.
Первая мысль при такой задаче часто сводится к использованию оператора В в условии ГДЕ. Например, следующим образом:
ВЫБРАТЬ
СпецификацииНоменклатурыИсходныеКомплектующие.Ссылка КАК Изделие,
СпецификацииНоменклатурыИсходныеКомплектующие.Номенклатура КАК Материал,
СпецификацииНоменклатурыИсходныеКомплектующие.Количество КАК Количество
ИЗ
Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК СпецификацииНоменклатурыИсходныеКомплектующие
ГДЕ
СпецификацииНоменклатурыИсходныеКомплектующие.Номенклатура В(&СписокСсылокМатериалов)
Однако такой запрос не решит нашу задачу. Оператор В работает по принципу логического "ИЛИ" (OR). Он выберет все изделия, в составе которых есть хотя бы один из перечисленных материалов. То есть, если в нашем списке [Мат1, Мат2], то будут выведены и Изделие1 (содержит Мат1), и Изделие2 (содержит Мат2), и Изделие3 (содержит Мат1 и Мат2). Нам же нужно только Изделие3.
Для решения этой задачи требуется подход, основанный на подсчете и сравнении количества уникальных материалов в изделии с общим количеством выбранных материалов. Мы рассмотрим два эффективных способа реализации этого в 1С с использованием SQL-запросов.
Этот подход является одним из наиболее распространенных и часто рекомендуемых в 1С, поскольку временные таблицы улучшают читаемость кода и позволяют оптимизатору СУБД лучше строить планы выполнения запросов. Мы разобьем решение на три логических шага.
На этом этапе мы создаем первую временную таблицу ВТ_Спецификации. В нее попадут все строки из справочника СпецификацииНоменклатуры.ИсходныеКомплектующие, где номенклатура комплектующего (Материал) присутствует в нашем списке &СписокСсылокМатериалов. Этот шаг отфильтровывает заведомо неинтересные нам спецификации.
ВЫБРАТЬ
СпецификацииНоменклатурыИсходныеКомплектующие.Ссылка КАК ГотовоеИзделие,
СпецификацииНоменклатурыИсходныеКомплектующие.Номенклатура КАК Материал,
СпецификацииНоменклатурыИсходныеКомплектующие.Количество КАК КоличествоШтукМатериалов
ПОМЕСТИТЬ ВТ_Спецификации
ИЗ
Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК СпецификацииНоменклатурыИсходныеКомплектующие
ГДЕ
СпецификацииНоменклатурыИсходныеКомплектующие.Номенклатура В(&СписокСсылокМатериалов)
;
Здесь:
СпецификацииНоменклатурыИсходныеКомплектующие.Ссылка – это ссылка на саму спецификацию, которая косвенно указывает на готовое изделие. В контексте задачи, это наше "Изделие".СпецификацииНоменклатурыИсходныеКомплектующие.Номенклатура – это материал, входящий в состав изделия.&СписокСсылокМатериалов – это параметр СКД, который является списком выбранных нами материалов.Это ключевой шаг, где реализуется логика "реляционного деления". Мы группируем полученные на первом шаге данные по ГотовоеИзделие. Затем используем условие ИМЕЮЩИЕ (HAVING) с функцией КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Материал). Мы подсчитываем количество уникальных материалов из нашего списка, которые входят в состав каждого изделия. Если это количество совпадает с общим количеством материалов в нашем параметре &СписокСсылокМатериалов, значит, изделие содержит все необходимые материалы.
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Спецификации.ГотовоеИзделие КАК ГотовоеИзделие
ПОМЕСТИТЬ ВТ_Изделия
ИЗ
ВТ_Спецификации КАК ВТ_Спецификации
СГРУППИРОВАТЬ ПО
ВТ_Спецификации.ГотовоеИзделие
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТ_Спецификации.Материал) = &КоличествоВыбранныхМатериалов
;
Важным моментом здесь является параметр &КоличествоВыбранныхМатериалов. Этот параметр необходимо получить программно в 1С перед выполнением запроса, посчитав количество элементов в вашем списке &СписокСсылокМатериалов. Например, если СписокСсылокМатериалов это массив или список значений, его количество можно получить как СписокСсылокМатериалов.Количество() и передать это значение в СКД.
Почему = (равно), а не >= (больше или равно)? Если в условии ИМЕЮЩИЕ использовать >= &КоличествоВыбранныхМатериалов, это будет означать, что изделие содержит *по крайней мере* столько же выбранных материалов, сколько было указано. Это может быть полезно в некоторых случаях, но для строгой задачи "содержит *все*" нам нужно именно равенство, поскольку количество уникальных материалов из нашего списка не может превысить общее количество материалов в этом списке.
Наконец, мы объединяем полученный список отобранных изделий (из ВТ_Изделия) с исходным справочником спецификаций, чтобы получить полный состав этих изделий (не только те материалы, которые были в нашем списке).
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Изделия.ГотовоеИзделие КАК ГотовоеИзделие,
СпецификацииНоменклатурыИсходныеКомплектующие.Номенклатура КАК Материал,
СпецификацииНоменклатурыИсходныеКомплектующие.Количество КАК КоличествоШтукМатериалов
ИЗ
ВТ_Изделия КАК ВТ_Изделия
ЛЕВОЕ СОЕДИНЕНИЕ
Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК СпецификацииНоменклатурыИсходныеКомплектующие
ПО
ВТ_Изделия.ГотовоеИзделие = СпецификацииНоменклатурыИсходныеКомплектующие.Ссылка
Мы используем ЛЕВОЕ СОЕДИНЕНИЕ, чтобы гарантировать, что все найденные изделия будут выведены, даже если по какой-то причине у них не нашлось соответствующих строк в СпецификацииНоменклатурыИсходныеКомплектующие (что маловероятно в данном контексте). В большинстве случаев ВНУТРЕННЕЕ СОЕДИНЕНИЕ тоже подошло бы, но ЛЕВОЕ СОЕДИНЕНИЕ является более безопасным выбором.
Альтернативный подход позволяет достичь того же результата, но без явного использования временных таблиц, используя вложенные запросы. Этот способ может быть более компактным, но иногда менее читаемым, а производительность может зависеть от оптимизатора конкретной СУБД. Главное преимущество этого решения в том, что оно позволяет динамически подсчитать количество выбранных материалов прямо внутри запроса, без необходимости передавать отдельный параметр &КоличествоВыбранныхМатериалов из 1С.
ВЫБРАТЬ
СпецификацииНоменклатурыИсходныеКомплектующие.Ссылка КАК Изделие,
СпецификацииНоменклатурыИсходныеКомплектующие.Номенклатура КАК Материал,
СпецификацииНоменклатурыИсходныеКомплектующие.Количество КАК Количество
ИЗ
Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК СпецификацииНоменклатурыИсходныеКомплектующие
ГДЕ
СпецификацииНоменклатурыИсходныеКомплектующие.Ссылка В
(ВЫБРАТЬ
Исходные.Ссылка
ИЗ
Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК Исходные
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
(ВЫБРАТЬ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Номенклатура.Ссылка) КАК КоличествоМатериалов
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
Номенклатура.Ссылка В (&СписокСсылокМатериалов)) КАК МатериалыВыборки
ПО ИСТИНА
ГДЕ
Исходные.Номенклатура В (&СписокСсылокМатериалов)
СГРУППИРОВАТЬ ПО
Исходные.Ссылка,
МатериалыВыборки.КоличествоМатериалов
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Исходные.Номенклатура) = МатериалыВыборки.КоличествоМатериалов)
Разберем этот запрос по частям:
Самый внутренний подзапрос (подсчет количества выбранных материалов):
(ВЫБРАТЬ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Номенклатура.Ссылка) КАК КоличествоМатериалов
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
Номенклатура.Ссылка В (&СписокСсылокМатериалов)) КАК МатериалыВыборки
Этот подзапрос выполняет важнейшую функцию: он подсчитывает общее количество уникальных материалов, которые были переданы в параметре &СписокСсылокМатериалов. Результатом будет одна строка с одним числовым полем КоличествоМатериалов. Это позволяет динамически определить, сколько именно материалов мы ищем, не передавая это число отдельным параметром извне.
Средний подзапрос (отбор изделий по условию "все материалы"):
ВЫБРАТЬ
Исходные.Ссылка
ИЗ
Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК Исходные
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
(...) КАК МатериалыВыборки // Результат самого внутреннего подзапроса
ПО ИСТИНА
ГДЕ
Исходные.Номенклатура В (&СписокСсылокМатериалов)
СГРУППИРОВАТЬ ПО
Исходные.Ссылка,
МатериалыВыборки.КоличествоМатериалов
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Исходные.Номенклатура) = МатериалыВыборки.КоличествоМатериалов
Этот подзапрос делает следующее:
Исходные) и результатом первого подзапроса (МатериалыВыборки). Соединение ПО ИСТИНА по сути является кросс-соединением, но так как МатериалыВыборки содержит всего одну строку, это просто позволяет нам получить доступ к значению КоличествоМатериалов для каждой строки спецификации.ГДЕ Исходные.Номенклатура В (&СписокСсылокМатериалов) отбирает только те компоненты спецификаций, которые входят в наш список искомых материалов. Это критически важно, чтобы не подсчитывать "лишние" материалы в изделии.Исходные.Ссылка (наше изделие) и по МатериалыВыборки.КоличествоМатериалов.ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Исходные.Номенклатура) = МатериалыВыборки.КоличествоМатериалов выполняет проверку: если количество уникальных материалов из нашего списка, найденных в данном изделии, равно общему количеству материалов в нашем списке, то это изделие удовлетворяет условию.Результатом этого среднего подзапроса будет список ссылок на те изделия, которые содержат все выбранные материалы.
Внешний запрос (получение полного состава изделий):
ВЫБРАТЬ
СпецификацииНоменклатурыИсходныеКомплектующие.Ссылка КАК Изделие,
СпецификацииНоменклатурыИсходныеКомплектующие.Номенклатура КАК Материал,
СпецификацииНоменклатурыИсходныеКомплектующие.Количество КАК Количество
ИЗ
Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК СпецификацииНоменклатурыИсходныеКомплектующие
ГДЕ
СпецификацииНоменклатурыИсходныеКомплектующие.Ссылка В (...) // Результат среднего подзапроса
Этот внешний запрос просто выбирает все строки из справочника спецификаций, но только для тех изделий, ссылки на которые были получены из среднего подзапроса. Таким образом, мы получаем полный состав всех отобранных готовых изделий.
Оба представленных решения корректно справляются с задачей реляционного деления. Выбор между ними часто сводится к личным предпочтениям, читаемости кода и потенциальной производительности на конкретной СУБД:
&КоличествоВыбранныхМатериалов в коде 1С перед передачей в СКД.Для обоих решений в СКД вам потребуется:
&СписокСсылокМатериалов с типом "Список значений" или "Массив", ссылающийся на справочник Номенклатура (или соответствующий тип для материалов). Пользователь сможет выбрать несколько материалов в отчете.&КоличествоВыбранныхМатериалов с числовым типом. Значение этого параметра необходимо устанавливать программно, например, в обработчике события ПриКомпоновкеРезультата или в коде формы отчета, подсчитав количество элементов в &СписокСсылокМатериалов.&КоличествоВыбранныхМатериалов не требуется, так как его значение вычисляется внутри самого запроса.Для удобства работы пользователей в СКД можно настроить доступные значения для параметра &СписокСсылокМатериалов, например, ограничив его только номенклатурой с типом "Материал" или определенной группой номенклатуры.
Мы рассмотрели, как эффективно решить задачу "реляционного деления" в 1С при помощи SQL-запросов в СКД. Независимо от выбранного подхода (с временными таблицами или вложенными запросами), ключевым моментом является использование группировки с условием ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) = &КоличествоНеобходимыхЭлементов. Понимание этих механизмов позволит вам создавать более гибкие и точные отчеты, отвечающие сложным бизнес-требованиям.