SQL Anywhere 12.0.1 » SQL Anywhere 服务器 - SQL 的用法 » 查询和修改数据 » 公用表表达式

### 最短距离问题

 ```CREATE TABLE travel ( origin VARCHAR(10), destination VARCHAR(10), distance INT, PRIMARY KEY ( origin, destination ) ); INSERT INTO travel SELECT 'Kitchener', 'Toronto', 105 UNION SELECT 'Kitchener', 'Barrie', 155 UNION SELECT 'North Bay', 'Pembroke', 220 UNION SELECT 'Pembroke', 'Ottawa', 150 UNION SELECT 'Barrie', 'Toronto', 90 UNION SELECT 'Toronto', 'Belleville', 190 UNION SELECT 'Belleville', 'Ottawa', 230 UNION SELECT 'Belleville', 'Pembroke', 230 UNION SELECT 'Barrie', 'Huntsville', 125 UNION SELECT 'Huntsville', 'North Bay', 130 UNION SELECT 'Huntsville', 'Pembroke', 245; INSERT INTO travel -- Insert the return trips SELECT destination, origin, distance FROM travel;```

• 路径返回到起始位置。

• 路径返回到前面的位置。

• 路径到达最后的终点。

 ```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, -- 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 <> 'Kitchener' -- Don't return to start AND v.destination <> t.previous -- Prevent backtracking AND v.origin <> 'Pembroke' -- Stop at the end AND segments -- TERMINATE RECURSION! < ( SELECT count(*)/2 FROM travel ) ) SELECT route, distance, segments FROM trip WHERE destination = 'Pembroke' AND distance < 1.5 * ( SELECT MIN( distance ) FROM trip WHERE destination = 'Pembroke' ) ORDER BY distance, segments, route;```

route distance segments
Kitchener, Barrie, Huntsville, Pembroke 525 3
Kitchener, Toronto, Belleville, Pembroke 525 3
Kitchener, Toronto, Barrie, Huntsville, Pembroke 565 4
Kitchener, Barrie, Huntsville, North Bay, Pembroke 630 4
Kitchener, Barrie, Toronto, Belleville, Pembroke 665 4
Kitchener, Toronto, Barrie, Huntsville, North Bay, Pembroke 670 5
Kitchener, Toronto, Belleville, Ottawa, Pembroke 675 4