SQL Anywhere 12.0.1 » SQL Anywhere サーバー SQL の使用法 » データの問い合わせと修正 » 共通テーブル式 » 再帰共通テーブル式

#### 複数の再帰共通テーブル式

たとえば、前述のクエリと同じ結果を返す次のクエリは、別の非再帰共通テーブル式を使用して最短ルートの距離を選択しています。2 つめの共通テーブル式の定義は、1 つめの定義からカンマで区切られています。

 ```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;```

 ```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' );`