MySQL: цикл со счетчиком, который используется для получения остальных полей

Задал вопрос на StackOverflow (на английском). И, видать, так тщательно его описал, что сам понял, как на него ответить.

Абстракт: нужно организовать цикл со счетчиком, и счетчик цикла использовать для выборки чего-либо из базы данных MySQL без использования временных таблиц и прочей ереси.

Теперь опишу здесь ситуацию по-русски:

Итак, как я уже писал, в моей конторе используются кодированные имена компьютеров. Кодировка описывается простым регэкспом:

cnt30[0-9]{3}[1-9a-z]{1}

где часть  [0-9]{3} по сути - номер рабочего места, а хвост [1-9a-z]{1} (в предыдущем посте я неправильно указал, что хвост начинается с нуля) - "квалификатор", указывающий на номер компьютера для этого рабочего места. Например, cnt300022 может быть десктопом на рабочем месте №002, а cnt30002a - ноутбуком, закрепленным за этим местом.



За каждым отделом закреплён свой диапазон номеров рабочих мест, например, для бухгалтерии зарезервированы имена компов от cnt302751 до cnt30294z включительно, то есть 20 рабочих мест с номерами от 275 до 294. Понятие "рабочее место" здесь довольно условное и больше имеет значение для нас, службы ИТ, чем для пользователей или еще кого. С учетными документами в отделе кадров или той же бухгалтерии оно никак не связано.

Откуда это взялось? При упорядочении структуры именования компов, нашему филиалу крупной конторы был выделен префикс "30" (супер-префикс 'cnt' общий для всей конторы) и расписано, что в пределах от 300000 до 30999z мы можем именовать компьютеры (читай: "рабочие места") по своему разумению.

Одним из вариантов "разумения" была сквозная нумерация. Но такое упорядочение привело бы к бардаку: если раньше компы назывались buh1, prog5 и т.п., что давало возможность группировать в различных выборках компы по отделам, то бездумное переименование в последовательные числовые имена лишило бы нас (особенно меня) такой возможности. Пришлось бы где-то вести отдельный список типа "комп 1234 находится в службе транспорта, комп 1235 - в бухгалтерии, 1236 в отделе закупок и т.п.". Не, это - ведение такого списка - тоже вариант, но насколько он удобен? На мой взгляд - неудобен совершенно.

Тогда я почесал репу и вспомнил свою работу в ПФР, где применялось кодированное обозначение отделов и должностей. Например, компы отдела автоматизации имели имена вида 08xx. Дабы не изобретать велосипед, я решил воспользоваться этим опытом. Еще пару дней чесания репы (и не только её), и родилась табличка со списком отделов, каждому из которых выделялось определенное количество рабочих мест и автоматически рассчитывался диапазон номеров этих рабочих мест. Табличка актуальна и сейчас: новые компы в отделах получают ближайшие свободные номера. 
И чтобы выбрать относящееся к определенному отделу, я просто беру имена компов в соответствующем этому отделу диапазоне. Например, при формировании отчета по имеющимся компам, мне не составляет труда вычислять отделы, к которым эти компы относятся, и сортировать отчет именно по отделам. Делать это же, справляясь со списком "комп - отдел" было бы накладнее.

Но вот какая незадача: сортируя бумажные паспорта компов, я обнаружил, что в некоторых диапазонах есть пропуски. Например, есть компы cnt30123x и cnt30125x но нет компов cnt30124x. То есть, есть рабочие места №123 и №125, но нет рабочего места №124.

Ладно, в случае некоторых отделов, где номера выделены с очень большим запасом, это не существенно, но кое-где запас свободных номеров почти исчерпан, а значит нужно "заделывать дырки". Вот и встала задача: вычислить эти дырки.

Как ее можно было бы решить на некоем псевдо-sql-бейсике:

for a=0 to MaxComp
  a$="CNT30"+right(a+1000,3)
'сравниваем только первые 8 знаков, игнорируя 9-ый, который квалификатор
  b$=(select name from table where left(name,8) like a$)
  print a$;b$
next a

(MaxComp=664, бОльшие номера рабочих мест пока не предусмотрены)

На выходе должна получиться табличка из двух столбцов: в первом потенциально возможное имя, во втором - фактически имеющееся. (Тот факт, что на одном рабочем месте может числиться несколько компов, оставим пока без внимания). Но как то же самое сделать в виде SQL-запроса?

Способ нумерации строк в результатах запроса известен давно, и желающие без проблем нагуглят или наяндексят, если понадобится, поэтому глубоко вдаваться в подробности не буду. Родил вот такой код:

select @cnum:=@cnum+1 as CompNum, group_concat(name separator ',')
# Сведения о компах хранятся в OCS-NG Inventory, отсюда и название таблицы
  from hardware
  cross join (select @cnum:=0) cnt
  where left(hardware.name,8)=concat('CNT30',right(@cnum+1000,3))

Однако, этот код, как я с ним ни бился, всегда возвращал только одну строку. Когда я понял, что зашел в тупик, тогда сел и поплакался на Stack Exchange. Плакаться начал еще на работе, закончил уже дома. Чтобы не тратить время, ожидая, что кто-нибудь решит мою проблему за меня, продолжил самостоятельно искать решение.

Как известно, в MySQL есть два вида языков: язык запросов, тот самый SQL, и язык описания хранимых процедур и функций. Причём второй куда ближе к классическим алгоритмическим языкам - есть привычные циклы, ветвления и т.п. Но создание "хранимок" имеет свои недостатки и я решил добиться результата с использованием SQL.

Хрен там плавал. Дальше вышеприведённого кода продвинуться не получалось. Тогда я решил попробовать создать хранимую функцию, которая, будучи вызванной в цикле, доставала бы данные из таблицы hardware, используя счетчик цикла как ключ для поиска в базе. Написал такую функцию:

CREATE FUNCTION `count_comps`(num smallint) RETURNS tinytext CHARSET utf8
BEGIN
    return (select group_concat(name separator ',')
        from hardware where left(hardware.name,8)=concat('CNT30',right(num+1000,3))
        );
END

Вставил ее в цикл и получил на выходе... совершенно правильный результат, но снова только одну строку (в таблице hardware почти 500 записей). Вопрос: что же я делаю не так? Убрал целиком строку с cross join и оно заработало: запрос вернул ровно столько строк, сколько записей в hardware: в первом столбце были возможные имена, во втором - либо null, либо реальное имя, либо через запятую (group_concat) список компов для данного рабочего места.

Но, как я уже сказал, создание и использование хранимых подпрограмм имеет свои недостатки, и я решил избавиться от этой функции. Для начала впихнул код из нее прямо в select в виде суб-запроса. И получил табличку из трёх столбцов. Два первых остались от предыдущего кода, третий содержал null в каждой своей строке. Стало быть, код из функции не работает внутри запроса так, как мне нужно. Пришлось его допилить и получилось вот такое прекрасное:

# Начальный номер рабочего места. Можно увеличить для сужения диапазона результатов
set @cnum:=0;
select
    @cnum:=@cnum+1 as CompNum,
    concat('CNT30',right(@cnum+1000,3)) as CalcNum,
# вот это
    count_comps(@cnum) as hwns,
# и вот это теперь дают одинаковые результаты
    (select group_concat(name separator ',')
        from hardware where left(name,8)=calcnum
        ) hwn2
    from hardware
# и никаких псевдотаблиц в операторе where!
# Конечный номер рабочего места. Можно уменьшить для сужения диапазона результатов
  where @cnum<665;

Вот как выглядит возвращаемый результат (для проверки был использован диапазон номеров рабочих мест от set @cnum:=479 до where @cnum<530 - выбранному мной подразделению выделен диапазон номеров от 480 до 529 включительно, результат приведён частично).

CompNum, CalcNum, hwns, hwn2 '488', 'CNT30488', 'CNT304881', 'CNT304881' '489', 'CNT30489', 'CNT304892', 'CNT304892' '490', 'CNT30490', 'CNT304901,CNT304902,CNT304903', CNT304901,CNT304902,CNT304903' '491', 'CNT30491', NULL, NULL '492', 'CNT30492', NULL, NULL '493', 'CNT30493', 'CNT304932', 'CNT304932' '494', 'CNT30494', 'CNT304941', 'CNT304941'

Сразу стало видно, что у меня нет рабочих мест №№ 491 и 492, а значит, если в это подразделение будем добавлять новые компы, то два из них получат имена cnt304911 и cnt304921.

Осталось прикрутить автоматическую привязку поиска дырок к названиям подразделений, и можно составить список, в каких случаях новым компам давать имена не из конца, а из середины диапазона, чтобы заткнуть бреши в нумерации. Но это уже дело техники и свободного времени, а основная секс-миссия таки выполнена!