中转范围 共2个表[就是哪些城市可以中转，不在范围内不能中转]
jiangxi
id city
1 南昌
2 九江
hubei
id city
1 武汉
2 襄阳
起始表 1个表[起始地]字段viaFrom表示，从上海出来，可以在哪里中转一下。
start
id from viaFrom
1 上海 jiangxi[表示jiangxi表内的所有城市都可中转]
2 上海 beijing
3 上海 hubei[表示hubei表内的所有城市都可中转]
4 上海 shangdong
目的地 1个表[目的地]字段viaTo表示，要到此目的地，必须由viaTo中转过来
dest
id dest viaTo
1 成都 北平
2 南充 武汉
3 宜宾 广州
4 达州 九江
整个过程：一定是，起始->中转->目的，现在来查询可以的路径。
答案是
from via viacity dest
上海 jiangxi 九江 达州
上海 hubei 武汉 南充
一条语句，可以实现吗？谢谢了。【就是要处理那个表，有难度。】
------解决方案--------------------
其实就是表的连接：
- SQL code
WITH jiangxi AS ( SELECT 1 id,'南昌' city FROM DUAL UNION ALL SELECT 2 id,'九江' city FROM DUAL),hubei AS ( SELECT 1 id,'武?' city FROM DUAL UNION ALL SELECT 2 id,'襄?' city FROM DUAL),start_tb AS ( SELECT 1 id,'上海' sfrom,'jiangxi' viaFrom FROM DUAL UNION ALL SELECT 2 id,'上海' sfrom,'beijing' viaFrom FROM DUAL UNION ALL SELECT 3 id,'上海' sfrom,'hubei' viaFrom FROM DUAL UNION ALL SELECT 4 id,'上海' sfrom,'shandong' viaFrom FROM DUAL),dest_tb AS ( SELECT 1 id,'成都' dest,'北平' viaTo FROM DUAL UNION ALL SELECT 2 id,'南充' dest,'武?' viaTo FROM DUAL UNION ALL SELECT 3 id,'宜?' dest,'广州' viaTo FROM DUAL UNION ALL SELECT 4 id,'?州' dest,'九江' viaTo FROM DUAL)SELECT s.sfrom, jh.via, d.viaTo viacity, d.dest FROM dest_tb d, ( SELECT 'jiangxi' via,city FROM jiangxi UNION ALL SELECT 'hubei' via,city FROM hubei ) jh, start_tb s WHERE d.viaTo = jh.city AND jh.via = s.viaFrom;
------解决方案--------------------
select a.from,b.viaFrom as via, b.city as viacity ,c.dest
from start a ,(
select 'jiangxi' as viaFrom, city from jiangxi
union all
select 'hubei' as viaFrom, city from hubei
) b ,dest c
where a.viaFrom=b.viaFrom
and b.city=c.viaTo