第一次结对作业4
今天我们在网上找到了相关的视频讲解,是利用SQL语句的递归查询,听了视频的相关讲解,也算是理解了吧。
以一号线的王府井到二号线的积水潭为例-----话不多说,上代码:
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
今日cp照: