Запрос, возвращающий все возможные пути и их длины в циклическом ориентированном графе
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 * FROM TC
ORDER BY src_pid, tgt_pid, distance;

Поделитесь материалом с коллегами и друзьями