C, PHP, VB, .NET

В предишната статия показах един прост пример за използване на оператор WITH. Тук ще дам още няколко.

Задача 1. Да се генерира таблица с 100 произволни числа (разбира се може да промените това число на колкото искате)

Решение: В рекурсивната заявка дефинираме брояч, с който лимитираме броя на рекурсивните извиквания до 100:

WITH RECURSIVE random_nums AS (
     SELECT 1 AS counter, RAND() AS rand_num
     UNION ALL
     SELECT counter+1, RAND()
     FROM random_nums
     WHERE counter < 100
)
SELECT rand_num FROM random_nums;

Задача 2. Дадена е таблица със служители, в която е описано кой на кого е пряк ръководител:

CREATE TABLE employees(
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  managerID INT NULL,
  FOREIGN KEY (managerID) REFERENCES employees(id)
              ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO employees(id, name, managerID)
VALUES (1, "Ivan", NULL),
       (2, "Petar", 1),
       (3, "Alexandra", 1),
       (4, "Maria", 2),
       (5, "Marian", 2),
       (6, "Margarita", 3),
       (7, "Mihail", 3),
       (8, "Eva", 6);

Трябва да изкараме списък с имената на всички служители и до тях да добавим имената на техните мениджъри.

Решение: Класическото решение на тази задача е със SELF JOIN:

SELECT slujitel.name AS slujitel, manager.name AS reportsTo
FROM employees AS slujitel LEFT JOIN employees AS manager
     ON slujitel.managerID = manager.id;

Ето как може същото да се осъществи с рекурсивна заявка:

WITH RECURSIVE empl_managers(id, slujitel, reportsTo) AS(
	SELECT id, name, CAST(NULL AS VARCHAR(255))
	FROM employees
	WHERE managerID IS NULL
	UNION ALL
	SELECT employees.id, employees.name, empl_managers.slujitel
	FROM employees
	JOIN empl_managers
	     ON empl_managers.id = employees.managerID
)
SELECT * FROM empl_managers;

В тази заявка показвам и как може да именовате колоните на резултатната таблица без да използвате „AS“ в инициализиращата SELECT заявка (подават се в скоби непосредствено след името на таблицата). Използваме оператор CAST, за да дефинираме типа данни на колоната – ако не го направим, MariaDB ще го вземе автоматично от заявката с началното условие, а после ще прави автоматичен CAST на данните при рекурсивната заявка.

В тази задача ползата не е съвсем очевидна. Можем обаче да усложняваме условието. Например:

Задача 3. Вземете таблицата от задача 2 и изведете списък с преките и непреките подчинени на Alexandra. Преки подчинени на Alexandra са Margarita и Mihail, а непряк подчинен е Eva (тя е подчинена на Margarita, която от своя страна на Alexandra).

Решение: с минимални усилия променяме началното условие (вместо да започне от топ-мениджърите, да се започне конкретно от Alexandra), а единствено в изходната таблица премахваме самата нея (да са спазим условието на задачата):

WITH RECURSIVE empl_managers(id, slujitel, reportsTo) AS(
	SELECT id, name, CAST(NULL AS VARCHAR(255))
	FROM employees
	WHERE name = "Alexandra"
	UNION ALL
	SELECT employees.id, employees.name, empl_managers.slujitel
	FROM employees
	JOIN empl_managers
	     ON empl_managers.id = employees.managerID
)
SELECT * FROM empl_managers
WHERE reportsTo IS NOT NULL;

Друг поглед над това решение е, че по този начин имитирахме, че все едно Alexandra е мениджър от най-високо ниво (дефинирахме нейният мениджър като NULL в началното условие и от там насетне пуснахме предишната рекурсия).

Задача 4. Нека усложним задача 3, като добавим още един непряк подчинен на Alexandra:

INSERT INTO employees(id, name, managerID)
VALUES (9, "Tony", 8);

Виждаме, че вече имаме по-дълбока йерархия. Нека ги дефинираме като „нива“. Преките ѝ подчинени Margarita и Mihail ще са от първо ниво, Eva ще е от второ ниво (подчинена на подчинена), а Tony ще е от трето ниво (подчинен на подчинена на подчинена). Новата задача е да се изкарат подчинените на Alexandra до второ ниво.

Решение: Ще дефинираме променлива lvl, с която ще мерим нивото, на което се намираме. С всяко рекурсивно извикване ще я увеличаваме с единица, а в WHERE условие ще спираме рекурсията когато това ниво е прескочило 1:

WITH RECURSIVE empl_managers(id, slujitel, reportsTo, lvl) AS(
	SELECT id, name, CAST(NULL AS VARCHAR(255)), 0
	FROM employees
	WHERE name = "Alexandra"
	UNION ALL
	SELECT employees.id, employees.name, empl_managers.slujitel, lvl+1
	FROM employees
	JOIN empl_managers
	     ON empl_managers.id = employees.managerID
	WHERE lvl<2
)
SELECT * FROM empl_managers
WHERE reportsTo IS NOT NULL;

Задача 5. От таблица employees да се изведе списък с номерата на служителите и конкатенирана поредица от техните мениджъри. Например за Eva трябва да се изведе следния резултат: 8, Eva->Margarita->Alexandra->Ivan

Решение: Можем да се възползваме от функцията CONCAT по следния начин:

WITH RECURSIVE empl_managers(id, slujitel) AS(
	SELECT id, name
	FROM employees
	WHERE managerID IS NULL
	UNION ALL
	SELECT employees.id, CONCAT(employees.name, "->", empl_managers.slujitel)
	FROM employees
	JOIN empl_managers
	     ON empl_managers.id = employees.managerID
)
SELECT * FROM empl_managers;

Виждате, че при изваждане на резултатите вече пропускаме колона reportsTo, защото тя е ненужна – всичко натрупваме в slujitel.

Задача 6. Дадена са таблици с градове и възможни автобусни маршрути между тях:

CREATE TABLE cities(
	id INT UNSIGNED PRIMARY KEY,
	city VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE bus_routes(
	source INT UNSIGNED,
	destination INT UNSIGNED,
	FOREIGN KEY(source) REFERENCES cities(id),
	FOREIGN KEY(destination) REFERENCES cities(id),
	PRIMARY KEY(source, destination)
);

INSERT INTO cities(id, city) VALUES
(1, "Sofia"), (2, "Plovdiv"), 
(3, "Asenovgrad"), (4, "Stara Zagora");

INSERT INTO bus_routes(source, destination)
VALUES (1,2), (2,1), (1,4), (4,1), 
       (2,3), (3,2), (2,4), (4,2);

Изведете списък с всички възможни маршрути.

Решение: Класическото решение чрез SELF JOIN ще бъде следното:

SELECT sources.id, sources.city, destinations.id, destinations.city
FROM bus_routes
JOIN cities AS sources
     ON sources.id = bus_routes.source
JOIN cities AS destinations
     ON destinations.id = bus_routes.destination
ORDER BY sources.id;

Използвайки рекурсивна подзаявка можем да осъществим същото чрез:

WITH RECURSIVE sofia_routes(source_id, source, destination_id, destination) AS(
	SELECT id, city, CAST(NULL AS INT), CAST(NULL AS VARCHAR(255))
	FROM cities
	UNION ALL
	SELECT sofia_routes.source_id, sofia_routes.source, cities.id, cities.city
	FROM bus_routes
	JOIN sofia_routes
	     ON sofia_routes.source_id = bus_routes.source
	JOIN cities
	     ON cities.id = bus_routes.destination
	WHERE sofia_routes.destination_id IS NULL
)
SELECT * FROM sofia_routes WHERE sofia_routes.destination_id IS NOT NULL
ORDER BY source_id;

Забележете, че спираме рекурсията чрез условието „WHERE sofia_routes.destination_id IS NULL“. Ако не го направим, ще достигнем до безкрайно зацикляне (обяснете защо). Алтернативно можем да постигнем същото чрез оператор UNION вместо UNION ALL:

WITH RECURSIVE sofia_routes(source_id, source, destination_id, destination) AS(
	SELECT id, city, CAST(NULL AS INT), CAST(NULL AS VARCHAR(255))
	FROM cities
	UNION
	SELECT sofia_routes.source_id, sofia_routes.source, cities.id, cities.city
	FROM bus_routes
	JOIN sofia_routes
	     ON sofia_routes.source_id = bus_routes.source
	JOIN cities
	     ON cities.id = bus_routes.destination
)
SELECT * FROM sofia_routes WHERE sofia_routes.destination_id IS NOT NULL
ORDER BY source_id;

Обяснете защо и как UNION решава проблема със зациклянето.

Допълнителна задача 1: Напишете заявка, с която изведете всички градове, от които може да се достигне пряко или непряко (с прекачване през друг град) тръгвайки от Sofia. Например между Sofia и Asenovgrad в примерните данни няма пряка автобусна линия, но има връзка с прекачване през Plovdiv.

Допълнителна задача 2: Решете допълнителта задача 1, като опишете маршрутите чрез конкатенация. Например: Sofia->Plovdiv->Asenovgrad