计算天平的数量

Question

我有下表：

路线历史

+----+-----------------+-----------------+---------------+-----------------+--------------+
| id |departure        | arrival         |terminal_ori_id| terminal_dest_id|next_route_id |
+----+-----------------+-----------------+---------------+-----------------+--------------+
| 8  |2020-05-01 12:00 |2020-05-01 17:00 |5              | 1               |  [null]      |       
| 9  |2020-05-01 08:00 |2020-05-01 10:00 |4              | 5               |  8           |            
| 10 |2020-05-01 03:00 |2020-05-01 07:00 |3              | 4               |  9           |            
+----+---------------------+-----------------+---------------+-----------------+--------------+

我想以某种格式打印出发和到达时间、航站楼的名称以及航班的磅秤数量。例如，航班#10具有到航班#9的比例，航班#9具有到航班#8的比例。所以10号航班有2个刻度，9号航班有1个刻度。有办法做到这一点吗？

以下是我的查询：

SELECT h.id,
to_char(h.departure, 'Mon DD YYYY HH:12:MI AM') departure, 
to_char(h.arrival, 'Mon DD YYYY HH:12:MI AM') arrival, 
t.name as terminal_origin,
td.name as terminal_destiny,
FROM history_of_routes h
JOIN terminals t ON t.id = h.terminal_ori_id
JOIN terminals td ON td.id = h.terminal_dest_id;

我只是错过了天平的部分，我不知道该怎么做。

Answer 1

递归CTE可以完成以下工作：

WITH RECURSIVE cte AS (
SELECT id, 0 AS scale, next_route_id
FROM   history_of_routes h
UNION ALL
SELECT c.id, c.scale + 1, h.next_route_id
FROM   cte c
JOIN   history_of_routes h ON h.id = c.next_route_id
WHERE  c.next_route_id IS NOT NULL  -- end of path
)
SELECT id, scale
FROM   cte
WHERE  next_route_id IS NULL;

结果：

root_id|scale------：|----：8|09|110|2

db<>小提琴这里

假设next_route_id IS NULL终止每个飞行路径。集成：

WITH RECURSIVE cte AS (
SELECT id, 0 AS scale, next_route_id
FROM   history_of_routes h
UNION ALL
SELECT c.id, c.scale + 1, h.next_route_id
FROM   cte c
JOIN   history_of_routes h ON h.id = c.next_route_id
WHERE  c.next_route_id IS NOT NULL  -- end of path
)
SELECT c.id
, c.scale
, to_char(h.departure, 'Mon DD YYYY HH:12:MI AM') AS departure
, to_char(h.arrival  , 'Mon DD YYYY HH:12:MI AM') AS arrival
, o.name AS terminal_origin
, d.name AS terminal_destiny
FROM   cte c
JOIN   history_of_routes h USING (id)
JOIN   terminals o ON o.id = h.terminal_ori_id
JOIN   terminals d ON d.id = h.terminal_dest_id
WHERE  c.next_route_id IS NULL;