iAnywhere.com - 使用多个递归公用表表达式

使用多个递归公用表表达式

递归查询可以包括多个递归查询，只要这些查询不相交即可。它还可以混合包括递归公用表表达式和非递归公用表表达式。如果至少有一个公用表表达式是递归的，则必须有 RECURSIVE 关键字。

例如，下面的查询（它与上一个查询返回的结果相同）使用第二个非递归公用表表达式选择最短路线的长度。第二个公用表表达式的定义通过逗号与第一个公用表表达式的定义分隔开。

WITH RECURSIVE
  trip (route, destination, previous, distance, segments) AS
    ( SELECT CAST(origin || ', ' || destination AS VARCHAR(256)),
             destination, origin, distance, 1
      FROM travel
      WHERE origin = 'Kitchener'
      UNION ALL
      SELECT route || ', ' || v.destination,
             v.destination,
             v.origin,
             t.distance + v.distance,
             segments + 1
      FROM trip t JOIN travel v ON t.destination = v.origin
      WHERE v.destination <> 'Kitchener'
        AND v.destination <> t.previous
        AND v.origin      <> 'Pembroke'
        AND segments
              < ( SELECT count(*)/2 FROM travel ) ),
  shortest ( distance ) AS                 -- Additional,
    ( SELECT min(distance)                 -- non-recursive
      FROM trip                            -- common table
      WHERE destination = 'Pembroke' )     -- expression
SELECT route, distance, segments FROM trip
WHERE destination = 'Pembroke' AND
      distance < 1.5 * ( SELECT distance FROM shortest )
ORDER BY distance, segments, route;

像非递归公用表表达式一样，递归表达式在用于存储过程中时可以包含对局部变量或过程参数的引用。例如，下面定义的 best_routes 过程可识别两个指定城市间的最短路线。

CREATE PROCEDURE best_routes (
   IN initial VARCHAR(10),
   IN final   VARCHAR(10)
)
BEGIN
  WITH RECURSIVE
      trip (route, destination, previous, distance, segments) AS
  ( SELECT CAST(origin || ', ' || destination AS VARCHAR(256)),
           destination, origin, distance, 1
    FROM travel
    WHERE origin = initial
      UNION ALL
    SELECT route || ', ' || v.destination,
           v.destination,            -- current endpoint
           v.origin,                 -- previous endpoint
           t.distance + v.distance,  -- total distance
           segments + 1              -- total number of segments
    FROM trip t JOIN travel v ON t.destination = v.origin
    WHERE v.destination <> initial     -- Don't return to start
      AND v.destination <> t.previous  -- Prevent backtracking
      AND v.origin      <> final       -- Stop at the end
      AND segments                     -- TERMINATE RECURSION!
            < ( SELECT count(*)/2 FROM travel ) )
  SELECT route, distance, segments FROM trip
  WHERE destination = final AND
        distance < 1.4 * ( SELECT min(distance)
                           FROM trip
                           WHERE destination = final )
  ORDER BY distance, segments, route;
END;

CALL best_routes ( 'Pembroke', 'Kitchener' );