8 – Que faire de plus ?

routes inverses :

JNY_FROM_TOWN JNY_TO_TOWN JNY_MILES
————– ———— ———
LYON         PARIS             470

Cela peut être fait par une requête on ne peut plus simple :

INSERT INTO T_JOURNEY SELECT JNY_TO_TOWN, JNY_FROM_TOWN, JNY_MILES FROM T_JOURNEY

Mais dès lors nos requêtes précédentes sont prises en défaut :

WITH journey (TO_TOWN) AS (SELECT DISTINCT JNY_FROM_TOWN FROM T_JOURNEY WHERE JNY_FROM_TOWN = ‘PARIS’ UNION ALL SELECT JNY_TO_TOWN FROM T_JOURNEY AS arrival INNER JOIN journey AS departure ON departure.TO_TOWN = arrival.JNY_FROM_TOWN) SELECT * FROM journey

TO_TOWN ——————————–
PARIS
NANTES
CLERMONT-FERRAND
LYON
….
LYON
MONTPELLIER
MARSEILLE
PARIS
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Que s’est-il passé ? Très simplement, le système essaye toutes les routes, y compris les trajets "ping-pong" comme Paris, Lyon, Paris, Lyon, Paris… ad infinitum… Est-il possible de se débarrasser des trajets "cycliques" ? Sans doute. Dans l’une de nos précédentes requête nous avons utilisé une colonne donnant la liste des étapes du trajet. Pourquoi ne pas l’utiliser pour empêcher un cycle de se produire ? La condition serait : ne pas passer par une ville dont le nom se trouve déjà dans la liste de ville du chemin (WAY). Ce qui peut s’écrire comme ceci :

WITH journey (TO_TOWN, STEPS, DISTANCE, WAY)
AS (SELECT DISTINCT JNY_FROM_TOWN, 0, 0, CAST(‘PARIS’ AS VARCHAR(MAX))
FROM T_JOURNEY
WHERE JNY_FROM_TOWN = ‘PARIS’ UNION ALL SELECT JNY_TO_TOWN, departure.STEPS + 1, departure.DISTANCE + arrival.JNY_MILES, departure.WAY + ‘, ‘ + arrival.JNY_TO_TOWN
FROM T_JOURNEY AS arrival INNER JOIN journey AS departure
ON departure.TO_TOWN = arrival.JNY_FROM_TOWN
WHERE departure.WAY NOT LIKE ‘%’ + arrival.JNY_TO_TOWN + ‘%’)
SELECT * FROM journey WHERE TO_TOWN = ‘TOULOUSE’

TO_TOWN STEPS DISTANCE WAY
——– —– ——– —————————————————–
–
TOULOUSE 3 1015 PARIS, LYON, MONTPELLIER, TOULOUSE
TOULOUSE 4 1485 PARIS, LYON, MONTPELLIER, CLERMONT-FERRAND, TOULOUSE
TOULOUSE 2 795 PARIS, CLERMONT-FERRAND, TOULOUSE
TOULOUSE 3 995 PARIS, CLERMONT-FERRAND, MONTPELLIER, TOULOUSE

Comme vous pouvez le constater, une nouvelle route apparaît. C’est la plus longue, mais peut être la plus belle !