今天我们在网上找到了相关的视频讲解,是利用SQL语句的递归查询,听了视频的相关讲解,也算是理解了吧。
以一号线的王府井到二号线的积水潭为例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
WITH RECURSIVE transfer (start_station, stop_station, stops, path) AS (
SELECT station_name, next_station, 1 , CAST(CONCAT(line_name,station_name , '->' , line_name,next_station) AS CHAR( 1000 ))
FROM bj_subway WHERE station_name = '王府井'
UNION ALL
SELECT p.start_station, e.next_station, stops + 1 , CONCAT(p.path, '->' , e.line_name, e.next_station)
FROM transfer p
JOIN bj_subway e
ON p.stop_station = e.station_name AND (INSTR(p.path, e.next_station) = 0 )
)
SELECT * FROM transfer WHERE stop_station = '积水潭' ;
-- Oracle
WITH transfer (start_station, stop_station, stops, path) AS (
SELECT station_name, next_station, 1 , line_name||station_name|| '->' ||line_name||next_station
FROM bj_subway WHERE station_name = '王府井'
UNION ALL
SELECT p.start_station, e.next_station, stops + 1 , p.path|| '->' ||e.line_name||e.next_station
FROM transfer p
JOIN bj_subway e
ON p.stop_station = e.station_name AND (INSTR(p.path, e.next_station) = 0 )
)
SELECT * FROM transfer WHERE stop_station = '积水潭' ;
-- SQL Server
WITH transfer(start_station, stop_station, stops, paths) AS (
SELECT station_name, next_station, 1 stops,
CAST(concat(line_name,station_name, '->' ,line_name,next_station) AS varchar( 1000 )) AS paths
FROM bj_subway
WHERE station_name = '王府井'
UNION ALL
SELECT t.start_station, s.next_station, stops+ 1 , CAST(concat(paths, '->' , s.line_name, s.next_station) AS varchar( 1000 ))
FROM transfer t
JOIN bj_subway s
ON (t.stop_station = s.station_name AND charindex(s.next_station, paths)= 0 )
)
SELECT *
FROM transfer
WHERE stop_station = '积水潭' ;
-- PostgreSQL
WITH RECURSIVE transfer (start_station, stop_station, stops, path) AS (
SELECT station_name, next_station, 1 , ARRAY[station_name::text, next_station::text]
FROM bj_subway WHERE station_name = '王府井'
UNION ALL
SELECT p.start_station, e.next_station, stops + 1 , p.path || ARRAY[e.next_station::text]
FROM transfer p
JOIN bj_subway e
ON p.stop_station = e.station_name AND NOT e.next_station = ANY(p.path)
)
SELECT * FROM transfer WHERE stop_station = '积水潭' ;
-- SQLite
WITH RECURSIVE transfer(start_station, stop_station, stops, paths) AS (
SELECT station_name, next_station, 1 stops,
line_name||station_name|| '->' ||line_name||next_station AS paths
FROM bj_subway
WHERE station_name = '王府井'
UNION ALL
SELECT t.start_station, s.next_station, stops+ 1 , paths|| '->' ||s.line_name||s.next_station
FROM transfer t
JOIN bj_subway s
ON (t.stop_station = s.station_name AND instr(paths, s.next_station)= 0 )
)
SELECT *
FROM transfer
WHERE stop_station = '积水潭' ;
Footer
|