Как в 1С 7.7 через прямой SQL-запрос получить количество документов по каждой номенклатуре

← На главную

При работе с высоконагруженными базами на платформе 1С:Предприятие 7.7 стандартных средств встроенного языка часто становится недостаточно. В таких случаях разработчики прибегают к использованию прямых SQL-запросов (например, через внешнюю компоненту 1С++). Одной из классических задач является получение статистики: сколько раз та или иная номенклатура встречалась в документах за определенный период.

В данной статье мы подробно разберем, как построить эффективный SQL-запрос для решения этой задачи, как правильно сгруппировать данные и как сделать так, чтобы в отчет попадали даже те товары, по которым не было ни одного движения (с нулевым результатом).

Постановка задачи и выбор инструментов

Предположим, у нас есть список номенклатуры, переданный в запрос через временную таблицу #TempTable. Нам необходимо подсчитать количество уникальных документов вида ПоступлениеТМЦ_Склад, в табличных частях которых фигурирует данная номенклатура в заданном периоде. Важное условие: если одна и та же номенклатура встречается в документе в нескольких строках, документ должен быть посчитан только один раз.

Проанализируем базовую структуру таблиц SQL в 1С 7.7:

_1SJourn — общий журнал документов. Здесь хранятся даты, номера и общие реквизиты всех документов.
$ДокументСтроки.ПоступлениеТМЦ_Склад — таблица строк конкретного вида документа.
#TempTable — временная таблица, в которую мы предварительно выгрузили список идентификаторов номенклатуры (Val).

Шаг 1: Базовая выборка и соединение таблиц

Для начала рассмотрим, как соединить журнал документов с их табличными частями. Мы используем поле IDDoc, которое является уникальным идентификатором документа в системе.


SELECT 
    count(distinct Док.IDDOC) AS КолПоступ 
FROM
    _1SJourn Жур
INNER JOIN
    $ДокументСтроки.ПоступлениеТМЦ_Склад AS Док ON Док.IDDoc = Жур.IDDoc
WHERE
    Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
    ($ДокументСтроки.ПоступлениеТМЦ_Склад.Номенклатура IN (SELECT Val FROM #TempTable))

Проанализируем этот код. Здесь используется count(distinct Док.IDDOC). Это критически важный момент: функция DISTINCT гарантирует, что если товар указан в трех строках одной накладной, запрос вернет "1", а не "3". Однако этот запрос вернет лишь одно общее число для всей номенклатуры сразу. Чтобы получить данные в разрезе каждого товара, нам потребуется группировка.

Шаг 2: Группировка данных по номенклатуре

Чтобы разделить общее количество по позициям номенклатуры, добавим поле номенклатуры в SELECT и соответствующее условие в GROUP BY. Выясним причину, по которой группировка по самому документу (IDDoc) здесь будет лишней: нам нужно конечное число документов на товар, а не список строк.


SELECT 
    $ДокументСтроки.ПоступлениеТМЦ_Склад.Номенклатура AS [Номенклатура],
    count(distinct Док.IDDOC) AS КолПоступ 
FROM
    _1SJourn Жур
INNER JOIN
    $ДокументСтроки.ПоступлениеТМЦ_Склад AS Док ON Док.IDDoc = Жур.IDDoc
WHERE
    Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
    ($ДокументСтроки.ПоступлениеТМЦ_Склад.Номенклатура IN (SELECT Val FROM #TempTable))
GROUP BY 
    $ДокументСтроки.ПоступлениеТМЦ_Склад.Номенклатура

Теперь запрос возвращает список: Номенклатура | Количество документов. Но здесь кроется подвох: если товара из нашего списка #TempTable нет ни в одном документе за период, он просто не попадет в выборку. В отчетах же часто требуется видеть все позиции, даже с нулевыми показателями.

Шаг 3: Обработка нулевых значений с помощью UNION ALL

Для решения проблемы "пропавших" товаров мы можем применить два подхода: LEFT JOIN от временной таблицы или объединение результатов через UNION ALL. Рассмотрим вариант с UNION ALL, так как он часто оказывается более наглядным при построении сложных отчетов в 1С 7.7.

Идея заключается в том, чтобы выбрать все данные из документов, а затем "приклеить" к ним все данные из временной таблицы с нулевым количеством. После этого мы еще раз сгруппируем результат по номенклатуре и просуммируем количество.

Посмотрим на итоговую структуру запроса, учитывая требования MS SQL Server 2000 (в котором нет оконных функций):


SELECT
    Номенклатура [Номенклатура $Справочник.Номенклатура],    
    SUM(КолПоступ) AS ОбщееКоличество
FROM
(
    -- Часть 1: Получаем реальные данные из документов
    SELECT
        $ДокументСтроки.ПоступлениеТМЦ_Склад.Номенклатура [Номенклатура],    
        count(distinct Док.IDDOC) AS КолПоступ
    FROM
        _1SJourn Жур
    INNER JOIN
        $ДокументСтроки.ПоступлениеТМЦ_Склад AS Док ON Док.IDDoc = Жур.IDDoc
    WHERE
        Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
        ($ДокументСтроки.ПоступлениеТМЦ_Склад.Номенклатура IN (SELECT Val FROM #TempTable))
    GROUP BY $ДокументСтроки.ПоступлениеТМЦ_Склад.Номенклатура

    UNION ALL 

    -- Часть 2: Добавляем все товары из списка с нулями
    SELECT 
        Val,
        0 
    FROM  
        #TempTable 
) AS t1
GROUP BY Номенклатура

Важное замечание по синтаксису: Обратите внимание на конструкцию ) AS t1 в конце подзапроса. В MS SQL Server любой вложенный запрос в секции FROM обязан иметь псевдоним (алиас), иначе система выдаст ошибку "Неправильный синтаксис около конструкции ')'".

Альтернативный вариант: Использование LEFT JOIN

Проанализируем ситуацию: можно ли обойтись без объединения таблиц? Да, если мы сделаем #TempTable главной таблицей запроса. В этом случае мы "навешиваем" данные документов на наш список товаров.


SELECT 
    tmp.Val AS [Номенклатура $Справочник.Номенклатура],
    COUNT(DISTINCT Док.IDDoc) AS КолПоступ
FROM 
    #TempTable tmp
LEFT JOIN 
    $ДокументСтроки.ПоступлениеТМЦ_Склад AS Док ON Док.Номенклатура = tmp.Val
LEFT JOIN 
    _1SJourn AS Жур ON Док.IDDoc = Жур.IDDoc 
    AND Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~
GROUP BY 
    tmp.Val

Этот подход также вернет нули для отсутствующих позиций, так как LEFT JOIN сохраняет все строки из левой таблицы (#TempTable). Однако стоит помнить, что при использовании LEFT JOIN условия по дате журнала должны находиться именно в условии соединения (ON) или должна производиться предварительная фильтрация, чтобы не превратить LEFT JOIN в INNER JOIN через секцию WHERE.