表的结构是
origin destination distance
---------------------------------------
Germany New York 8000
Germany Chicago 8700
Chicago Austin 1300
New York Houston 2100
Houston Austin 300
New York Chicago 950
Italy New York 10000
Ireland Chicago 10700
Chicago Toronto 400
New York Toronto 350
Mexico Houston 770
SQL语句是
WITH path (origin, destination, distance, stops)
AS(
SELECT origin, destination, distance, 0
FROM flights
WHERE origin='Germany'
UNION ALL
SELECT p.origin, f.destination, p.distance+f.distance, p.stops+1
FROM flights f, path p
WHERE p.destination=f.origin)
SELECT origin, destination, distance, stops FROM path
结果是
origin destinat distance stops
-----------------------------------------------------------
Germany New York 8000 0
Germany Chicago 8700 0
Germany Houston 10100 1
Germany Chicago 8950 1
Germany Toronto 8350 1
Germany Austin 10000 1
Germany Toronto 9100 1
Germany Austin 10400 2
Germany Austin 10250 2
Germany Toronto 9350 2
第一次执行结果:
第一个select语句产生的结果为
path: origin destination distance stops
Germany New York 8000 0
Germany Chicago 8700 0
第二个select语句执行的结果为
path: origin destination distance stops
Germany Hunston 8000+2100 1
Germany Chicago 8000+950 1
Germany Toronto 8000+350 1
Germany Austin 8700+1300 1
Germany Toronto 8700+400 1
union 后的结果为:
path: origin destination distance stops
Germany New York 8000 0
Germany Chicago 8700 0
Germany Hunston 8000+2100 1
Germany Chicago 8000+950 1
Germany Toronto 8000+350 1
Germany Austin 8700+1300 1
Germany Toronto 8700+400 1
第二次递归调用结果:(path发生了改变,重新执行第二个select子句)
path: origin destination distance stops
Germany New York 8000 0
Germany Chicago 8700 0
Germany Hunston 8000+2100 1
Germany Chicago 8000+950 1
Germany Toronto 8000+350 1
Germany Austin 8700+1300 1
Germany Toronto 8700+400 1
Germany Austin 8700+2100+300 2
Germany Austin 8000+950+1300 2
Germany Toronto 8000+950+400 2