Mehrere rekursive allgemeine Tabellenausdrücke verwenden

Eine rekursive Abfrage kann mehrere rekursive Abfragen enthalten, solange sie voneinander unabhängig sind. Sie kann auch eine Mischung aus rekursiven und nicht-rekursiven allgemeinen Tabellenausdrücken enthalten. Das RECURSIVE-Schlüsselwort muss verwendet werden, wenn zumindest einer der allgemeinen Tabellenausdrücke rekursiv ist.

Beispiel: Die folgende Abfrage, die dasselbe Ergebnis wie die vorherige Abfrage zurückgibt, verwendet einen zweiten, nicht-rekursiven allgemeinen Tabellenausdruck, um die Länge der kürzesten Route auszuwählen. Die Definition des zweiten allgemeinen Tabellenausdrucks wird von der Definition des ersten durch ein Komma getrennt.

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;

Wie nicht-rekursive allgemeine Tabellenausdrücke können auch rekursive Ausdrücke, wenn sie in gespeicherten Prozeduren verwendet werden, Referenzen zu lokalen Variablen oder Prozedurparametern enthalten. Beispiel: Die unten definierte Prozedur "best_routes" findet die kürzeste Route zwischen den zwei benannten Städten heraus.

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;

Die folgende Anweisung ruft die obige Prozedur auf.

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

Kommentieren Sie diese Seite in DocCommentXchange.