Нумерация строк в запросе
Ключевые слова: нумерация строк запроса, ПЕРВЫЕ, нумерация строк в построителе, пронумеровать.
Иногда возникает необходимость добавить в запрос колонку с номером строки, например, когда нужно вывести номера строк в отчет, а используется построитель отчета. Также нумерация строк полезна, когда необходимо для определенных группировок вывести ограниченное количество строк, что-то типа "ПЕРВЫЕ 5 документов для каждого контрагента".
Данная статья навеяна обсуждением в теме v8: Раздумья над запросом
Начнем с того, что в 1С 8 используется ограниченный диалект SQL, поэтому многие способы нумерации строк запроса, возможные в стандартном SQL, для нас не доступны (если кому-то интересно, как это делать в SQL, - http://www.sql.ru/faq/faq_topic.aspx?fid=126).
Итак, приступим. Есть запрос, выбирающий номенклатуру. Результат отсортирован по наименованию:
ВЫБРАТЬ
Номенклатура.Ссылка
ИЗ
Справочник.Номенклатура КАК Номенклатура
УПОРЯДОЧИТЬ ПО
Номенклатура.Наименование
исходный запрос
Требуется добавить колонку с порядковым номером номенклатуры (в рамках данной сортировки):
ВЫБРАТЬ
Номенклатура.Ссылка КАК Номенклатура,
КОЛИЧЕСТВО(Номенклатура_1.Ссылка) КАК Номер
ИЗ
Справочник.Номенклатура КАК Номенклатура
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Номенклатура_1
ПО Номенклатура.Наименование >= Номенклатура_1.Наименование // Условие сортировки для нумерации
СГРУППИРОВАТЬ ПО
Номенклатура.Ссылка
УПОРЯДОЧИТЬ ПО
Номенклатура.Наименование
запрос с номерами строк
В вышеуказанном запросе мы соединяем таблицу саму с собой довольно хитрым способом: по правой таблице подсчитываем количество номенклатуры с наименованием меньше текущего левого, используя группировку.
Аналогичную методику можно применить, если нужно задать нумерацию строк с учетом группировки по какому-либо полю:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Номенклатура,
Номенклатура.Родитель КАК Родитель,
КОЛИЧЕСТВО(Номенклатура_1.Ссылка) КАК Номер
ИЗ
Справочник.Номенклатура КАК Номенклатура
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Номенклатура_1
ПО Номенклатура.Наименование >= Номенклатура_1.Наименование
И Номенклатура.Родитель = Номенклатура_1.Родитель
СГРУППИРОВАТЬ ПО
Номенклатура.Родитель,
Номенклатура.Ссылка
запрос с номерами строк в рамках группировки
Это тот же самый запрос, в него просто добавлена группировка по полю "Родитель". Нумерация для каждого родителя - своя.
Быстродействие:
Если говорить о быстродействии, то запросы подобного рода, естественно, ресурсоемки (математическая сложность порядка N^2). Для большинства повседневных задач их использование вполне приемлемо.
Кстати, второй запрос выполняется несколько быстрее первого - это связано с меньшим размером вспомогательных таблиц.
Пример реального использования:
Теперь немного о том, ради чего всё это делается. Какую пользу которую можно извлечь из нумерации строк запроса?
Например, перед нами стоит задача получить для каждого контрагента пять последних документов поступления товаров и услуг:
ВЫБРАТЬ
ПоступлениеТоваровУслуг.Контрагент КАК Контрагент,
ПоступлениеТоваровУслуг.Ссылка КАК Ссылка,
КОЛИЧЕСТВО(ПоступлениеТоваровУслуг_1.Ссылка) КАК Номер
ИЗ
Документ.ПоступлениеТоваровУслуг КАК ПоступлениеТоваровУслуг
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ПоступлениеТоваровУслуг КАК ПоступлениеТоваровУслуг_1
ПО ПоступлениеТоваровУслуг.Дата <= ПоступлениеТоваровУслуг_1.Дата
И ПоступлениеТоваровУслуг.Контрагент = ПоступлениеТоваровУслуг_1.Контрагент
СГРУППИРОВАТЬ ПО
ПоступлениеТоваровУслуг.Ссылка,
ПоступлениеТоваровУслуг.Контрагент
ИМЕЮЩИЕ
КОЛИЧЕСТВО(ПоступлениеТоваровУслуг_1.Ссылка) <= 5
УПОРЯДОЧИТЬ ПО
Контрагент,
Номер
запрос с номерами строк при наличии дублей интересующего поля(того,относительно которого строится счетчик)
ВЫБРАТЬ
КОЛИЧЕСТВО(ФизическиеЛица_1.Наименование) КАК Номер,
ФизическиеЛица.Ссылка КАК ФИО
ИЗ
Справочник.ФизическиеЛица КАК ФизическиеЛица
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ФизическиеЛица КАК ФизическиеЛица_1
ПО (ФизическиеЛица.Наименование + ФизическиеЛица.Код >= ФизическиеЛица_1.Наименование + ФизическиеЛица_1.Код)
СГРУППИРОВАТЬ ПО
ФизическиеЛица.Ссылка
УПОРЯДОЧИТЬ ПО
Номер
Собственно говоря, идея такова: использовать "составное поле" для получения возрастающего итога (при этом одно поле может содержать дубли, а второе, как в данном примере, - нет.).
Родилось в голове НЕКРОН'а |