Вместо предисловия: статья о запросах не может обойтись без ссылки на ildarovich'a, в частности - на эту публикацию.
Не прошло и недели, как в теме Можно ли в запросе для поля убрать пробелы? было дано теоретическое решение, как жизнь подкинула реальную пару тесно связанных задач.
Задача 1. В справочнике Номенклатура помимо артикула "2630035503" хранится строка замен вида "2630035501 2630035502 ..." (нет, я не несу ответственности за выбранный способ хранения этой информации!).
При формировании прайса хотелось бы видеть предложения по артикулам замены отдельными строками, не "2630035503 фильтр 150 руб. 10 шт", а
2630035501 фильтр 150 руб. 10 шт
2630035502 фильтр 150 руб. 10 шт
2630035503 фильтр 150 руб. 10 шт
Задача 2. Внезапно оказалось, что есть и обратная ситуация. В регистре сведений хранятся замены номенклатуры, а при формировании прайса их нужно уже слеплять в одну строку. В регистре
Номенклатура - Замена
26300035501 - 2630035502
26300035501 - 2630035503
а в прайсе должно быть
2630035501, 2630035502, 2630035503 фильтр 150 руб. 10 шт.
Тут необходимо сделать оговорку, что, хотя постановка задачи изобилует артикулами, в примерах решения я буду использовать наименования и/или характеристики - просто на момент написания статьи под рукой только демка УПП оказалась.
Для решения первой задачи был использован адаптированный механизм из темы про пробелы.
- Подготовка служебной таблицы чисел от 0 до 255.
ВЫБРАТЬ 0 КАК зн ПОМЕСТИТЬ т01 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 1;
ВЫБРАТЬ т1.зн * 2 + т0.зн КАК зн ПОМЕСТИТЬ т02 ИЗ т01 КАК т0, т01 КАК т1;
ВЫБРАТЬ т1.зн * 4 + т0.зн КАК зн ПОМЕСТИТЬ т03 ИЗ т02 КАК т0, т02 КАК т1;
ВЫБРАТЬ т1.зн * 16 + т0.зн КАК зн ПОМЕСТИТЬ т04 ИЗ т03 КАК т0, т03 КАК т1;
- Выборка исходных данных, я решил поразделять наименования номенклатуры. Попутно вычисляются позиции разделителей.
ВЫБРАТЬ
Номенклатура.Ссылка КАК Ссылка,
Номенклатура.Наименование,
т04.зн КАК Позиция
ПОМЕСТИТЬ тНоменИсходная
ИЗ
Справочник.Номенклатура КАК Номенклатура,
т04 КАК т04
ГДЕ ПОДСТРОКА(Номенклатура.Наименование, т04.зн, 1) + "#" = " #"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ Номенклатура.Ссылка, Номенклатура.Наименование, 0 ИЗ Справочник.Номенклатура КАК Номенклатура;
- Позиции разделителей преобразуем в позции начала и окончания содержательных частей наименований:
ВЫБРАТЬ
т1.Ссылка КАК Ссылка,
т1.Наименование,
т1.Позиция КАК Позиция,
МИНИМУМ(т2.Позиция) КАК Позиция1
ПОМЕСТИТЬ тНоменГотовая
ИЗ
тНоменИсходная КАК т1 ЛЕВОЕ СОЕДИНЕНИЕ тНоменИсходная КАК т2 ПО т1.Ссылка = т2.Ссылка И т1.Позиция < т2.Позиция
СГРУППИРОВАТЬ ПО
т1.Ссылка,
т1.Наименование,
т1.Позиция
ИМЕЮЩИЕ (т1.Позиция + 1 <> МИНИМУМ(т2.Позиция) ИЛИ МИНИМУМ(т2.Позиция) ЕСТЬ NULL );
- И наконец получаем нужные подстроки.
ВЫБРАТЬ
тНоменГотовая.Ссылка КАК Ссылка,
ВЫБОР
КОГДА тНоменГотовая.Позиция1 ЕСТЬ NULL
ТОГДА ПОДСТРОКА(тНоменГотовая.Наименование, тНоменГотовая.Позиция + 1, 1000)
ИНАЧЕ ПОДСТРОКА(тНоменГотовая.Наименование, тНоменГотовая.Позиция + 1, тНоменГотовая.Позиция1 - тНоменГотовая.Позиция - 1)
КОНЕЦ КАК Часть,
тНоменГотовая.Позиция КАК Позиция
ИЗ
тНоменГотовая КАК тНоменГотовая
УПОРЯДОЧИТЬ ПО
Ссылка,
Позиция
В результате получаем что-то наподобие
Что с этим делать дальше?
В моей задаче - про строки замен - я получал их разбиение на конкретные артикулы, лепил остатки по полю ссылки и получал требуемый вид прайса.
Также можно обратно слепить полученные части (см. дальше), и в результате поиметь наименования с удаленными лишними пробелами, как в задаче из вступления, но без оговорок типа замены символов "пробел" на неразрывные пробелы:
Решение второй задачи - склейка строк - была уже рассмотрена в публикации ildarovich'a, но есть и альтернативный вариант:
- Нумеруем строки
ВЫБРАТЬ
т1.Владелец КАК Номенклатура,
т1.Наименование КАК Характеристика,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ т2.Ссылка) КАК Номер
ПОМЕСТИТЬ т0
ИЗ
Справочник.ХарактеристикиНоменклатуры КАК т1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ХарактеристикиНоменклатуры КАК т2 ПО т1.Владелец = т2.Владелец И т1.Ссылка >= т2.Ссылка
СГРУППИРОВАТЬ ПО
т1.Владелец, т1.Наименование;
- И складываем, подставляя при необходимости требуемый разделитель:
ВЫБРАТЬ
т0.Номенклатура,
ВЫБОР КОГДА т1.Номенклатура ЕСТЬ NULL ТОГДА т0.Характеристика ИНАЧЕ т0.Характеристика + "; " + т1.Характеристика КОНЕЦ КАК Характеристика,
ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0)) КАК Номер
ПОМЕСТИТЬ т1
ИЗ
т0 КАК т0 ЛЕВОЕ СОЕДИНЕНИЕ т0 КАК т1 ПО т0.Номенклатура = т1.Номенклатура И (т0.Номер + 1 = т1.Номер)
ГДЕ (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0))) <> (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 1)));
- Предыдущий шаг повторяем нужное кличество раз, мне для примера хватило еще двух:
ВЫБРАТЬ
т0.Номенклатура, ВЫБОР КОГДА т1.Номенклатура ЕСТЬ NULL ТОГДА т0.Характеристика ИНАЧЕ т0.Характеристика + "; " + т1.Характеристика КОНЕЦ КАК Характеристика,
ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0)) КАК Номер
ПОМЕСТИТЬ т2
ИЗ т1 КАК т0 ЛЕВОЕ СОЕДИНЕНИЕ т1 КАК т1 ПО т0.Номенклатура = т1.Номенклатура И (т0.Номер + 1 = т1.Номер)
ГДЕ (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0))) <> (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 1)));
ВЫБРАТЬ
т0.Номенклатура,
ВЫБОР КОГДА т1.Номенклатура ЕСТЬ NULL ТОГДА т0.Характеристика ИНАЧЕ т0.Характеристика + "; " + т1.Характеристика КОНЕЦ КАК Характеристика,
ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0)) КАК Номер
ИЗ т2 КАК т0 ЛЕВОЕ СОЕДИНЕНИЕ т2 КАК т1 ПО т0.Номенклатура = т1.Номенклатура И (т0.Номер + 1 = т1.Номер)
ГДЕ (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0))) <> (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 1)))
Понятно, что такой запрос писать вручную не имеет смысла, построить его автоматически элементарно. Нужное число повторений определяется эмпирически, десяти хватит на объединение до 1014 подстрок. В результате
превращается в
Для моей задачи с артикулами все выглядело точно также, только вместо справочника регистр сведений использовался.
Для чего еще это можно использовать? Например, построение строки со свойствами какого либо объекта. Задача вывести список документов с указанием каких-либо примечаний, записанных в дополнительные свойства не так уж редка. Фантазируйте.
P.S. Данные алгоритмы применимы не только для 8х - первая задача была также успешно переписана на запросы 1sqlite для 7.7.
