Листинг 7.

Запрос, возвращающий кратчайшие пути циклического ориентированного графа

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;