sql - 计算路段成本

Question

路线表:

route_id | points 
       1 | [A,B] 
       2 | [L,G,C,F,E]

包含路线段成本的表格:

route_id | point A | pointB | cost
       1 | A       | B      |   10
       2 | L       | G      |   10
       2 | G       | C      |   20
       2 | C       | F      |   15
       2 | F       | E      |   13

需要计算route_id=2中点'G'和'E'之间的费用

Answer 1

您可以使用“Recursive With Clause”实现您想要的结果。

表格:

create table test(
route_id int, 
pointA char,
 pointB char, 
 cost int
);

值(value)观:

insert into test values(1 ,'A','B',10),
(2 ,'L','G',10),
(2 ,'G','C',20),
(2 ,'C','F',15),
(2 ,'F','E',13)

递归查询:

WITH RECURSIVE routecost AS (
SELECT pointA, pointB ,cost  /* non recursive part */ 
FROM test                   
WHERE pointA = 'G'
  and route_id = 2 
UNION ALL
SELECT b.pointA, a.pointB, a.cost + b.cost   /* recursive part */               
FROM test a                
JOIN routecost b ON(a.pointA = b.pointB)    
  where a.route_id = 2
)
SELECT * FROM routecost
where pointB = 'E'

解释:

1. 先用非递归的pointA和route_id过滤query部分。
2. 然后在递归部分按照查询所示加入它并添加成本
3. 我每次都获取 b.pointA(在非递归部分)，因为我们 需要起点。
4. 然后最后我将它过滤到 pointB 值以获得所需结果。

Reference: Recursive with clause