我正在嘗試運行以下查詢（我評論了一些我迄今為止嘗試過的事情）：
WITH
res AS (
SELECT * ...
), -- res has a column of integers called "node". I need to transform this column into an array to use it in viapath below
nodes AS (
-- SELECT ARRAY[node] FROM res
-- SELECT array_agg(node) FROM res
),
viapath AS (
SELECT * FROM pgr_dijkstraVia(
'SELECT id, source, target, cost FROM edge_net',
nodes -- array[54, 37, 897, 435]
)
)
SELECT * FROM viapath;
它適用于陣列 [54, 37, 897, 435] 但不適用于“節點”。通過我的試驗，我收到一個“列“節點”不存在”。如何將“res”中的“node”列用作“viapath”中的陣列？
uj5u.com熱心網友回復：
您可以使用子查詢運算式(select array_agg(node) from res).
WITH
res AS (SELECT * ...),
viapath AS
(
SELECT * FROM pgr_dijkstraVia
(
'SELECT id, source, target, cost FROM edge_net',
(select array_agg(node) from res)
)
)
SELECT * FROM viapath;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/348645.html
標籤：数组 PostgreSQL 公用表表达式