3.18

今天我们在网上找到了相关的视频讲解，是利用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
posted @ 2024-06-18 20:06  yblll  阅读(15)  评论(0)    收藏  举报