Запрос, возвращающий кратчайшие пути циклического ориентированного графа
WITH TC(src_pid, tgt_pid, distance, path)
AS
( SELECT src_pid, tgt_pid, 1,
CAST(?.? + CAST(src_pid AS varchar (10)) + ?.?
+ CAST(tgt_pid AS varchar (10)) + ?.?
AS varchar (900))
FROM Links
UNION ALL
SELECT P.src_pid, C.tgt_pid, P.distance + 1,
CAST(P.path + CAST(C.tgt_pid AS varchar (10)) + ?.?
AS varchar (900))
FROM Links AS C
JOIN TC AS P
ON C.src_pid = P.tgt_pid
WHERE P.path NOT LIKE ?%.? + CAST(C.tgt_pid AS varchar (10)) + ?.%?)
SELECT AP.*
FROM TC AS AP — All Paths
JOIN (SELECT src_pid, tgt_pid, MIN(distance) AS min_dist
FROM TC
GROUP BY src_pid, tgt_pid) AS MD — Min Distances
ON AP.src_pid = MD.src_pid
AND AP.tgt_pid = MD.tgt_pid
AND AP.distance = MD.min_dist
ORDER BY src_pid, tgt_pid;