SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE IF NOT EXISTS graph ( point1 TEXT NOT NULL, point2 TEXT NOT NULL, cost INT NOT NULL ); INSERT INTO graph (point1, point2, cost) VALUES ('a', 'b', 10), ('b', 'a', 10), ('a', 'c', 15), ('c', 'a', 15), ('a', 'd', 20), ('d', 'a', 20), ('b', 'd', 25), ('d', 'b', 25), ('c', 'd', 30), ('d', 'c', 30), ('b', 'c', 35), ('c', 'b', 35); CREATE OR REPLACE VIEW route AS ( WITH RECURSIVE search AS ( SELECT point1 AS track, point2 AS next_point, cost AS total_cost FROM graph WHERE point1 = 'a' UNION SELECT search.track || ',' || search.next_point AS track, graph.point1 AS next_point, search.total_cost + graph.cost AS total_cost FROM search JOIN graph ON search.next_point = graph.point2 WHERE track NOT LIKE '%' || graph.point2 || '%') SELECT total_cost, '{' || track || ',' || 'a' || '}' AS tour FROM search WHERE LENGTH(track) = 7 AND next_point = 'a'); SELECT * FROM route WHERE total_cost = (SELECT MIN(total_cost) FROM route) ORDER BY total_cost, tour;