Блог

Допустим, у нас есть простая вкладка DB-table, созданная в MySQL с

 CREATE TABLE `tab` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `val` int(11) DEFAULT NULL,
 `altval` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
);
 

и заполняется набором данных examplary:

 INSERT INTO tab VALUES(1,1,NULL);
INSERT INTO tab VALUES(2,2,3);
INSERT INTO tab VALUES(3,4,NULL);
INSERT INTO tab VALUES(4,5,6);
 

Каждая строка имеет значение val, а для id=2,4 — альтернативное значение altval. Есть ли способ в SQL получить все комбинации для стандартных наборов данных? (Количество комбинаций равно 2 степени количества строк с альтернативными наборами данных, здесь: 4, поэтому ‘abitrary’, конечно, ограничено.) Для данного примера результат должен быть:
(1,2,4,5),(1,2,4,6),(1,3,4,5),(1,3,4,6)

На языке сценариев, таком как php, я бы решил эту проблему, создав вложенные циклы for с помощью eval() .

Использование рекурсивного CTE:

 WITH RECURSIVE cte AS (
   -- continuous rn
   SELECT CAST(val AS CHAR(11)) val, CAST(altval AS CHAR(11)) AS altval,
          ROW_NUMBER() OVER(ORDER BY id) AS rn
   FROM tab
), rec AS (
  -- anchor
  SELECT val AS result, rn
  FROM cte
  WHERE rn = 1
  UNION ALL
  SELECT altval AS result, rn
  FROM cte
  WHERE rn = 1
    AND altval IS NOT NULL
  UNION ALL
  -- recursive part
  SELECT CONCAT(rec.result, ',', cte.val), cte.rn
  FROM rec
  JOIN cte ON cte.rn = rec.rn 1
  UNION ALL
  SELECT CONCAT(rec.result, ',', cte.altval), cte.rn
  FROM rec
  JOIN cte ON cte.rn = rec.rn 1
  WHERE cte.altval IS NOT NULL
)
SELECT result
FROM rec
WHERE rn = (SELECT COUNT(*) FROM cte);  -- choosing only longest string
 

db<>демо-версия скрипки