Конвертировать CTE из Postgres в MSSQL
Я должен конвертировать CTE из Postgresql zu MSSQL. Проблема в том, что Postgresql-Query использует массивы. Я понятия не имею, как конвертировать их в MSSQL.
Вот полный запрос с созданием данных:
CREATE TABLE pairs (
from_city VARCHAR(255) NOT NULL,
to_city VARCHAR(255) NOT NULL,
distance INTEGER NOT NULL,
PRIMARY KEY(from_city, to_city),
CHECK (from_city < to_city)
);
INSERT INTO pairs
VALUES
('Bari','Bologna',672),
('Bari','Genova',944),
('Bari','Milano',881),
('Bari','Napoli',257),
('Bari','Palermo',708),
('Bologna','Genova',190),
('Bologna','Milano',200),
('Bologna','Napoli',470),
('Bologna','Palermo',730),
('Bologna','Roma',300),
('Genova','Milano',120),
('Genova','Napoli',590),
('Genova','Palermo',790),
('Genova','Roma',400),
('Milano','Napoli',660),
('Milano','Palermo',890),
('Milano','Roma',480),
('Napoli','Palermo',310),
('Napoli','Roma',190),
('Palermo','Roma',430);
SELECT * FROM pairs;
-- Paths from Palermo to Milano...
;WITH RECURSIVE both_ways (
from_city,
to_city,
distance
) /* Working Table containing all ways */
AS (
SELECT
from_city,
to_city,
distance
FROM pairs
UNION ALL
SELECT
to_city AS "from_city",
from_city AS "to_city",
distance
FROM pairs
),
paths (
from_city,
to_city,
distance,
path
)
AS (
SELECT
from_city,
to_city,
distance,
ARRAY[from_city] AS "path"
FROM both_ways b1
WHERE b1.from_city = 'Palermo' --<<< Start Node >>>
UNION ALL
SELECT
b2.from_city,
b2.to_city,
p.distance + b2.distance,
p.path + b2.from_city
FROM both_ways b2
JOIN paths p
ON (p.to_city = b2.from_city
AND b2.from_city <> ALL (p.path[2:array_upper(p.path,1)]) /* Prevent re-tracing */
AND array_upper(p.path,1) < 6)
)
SELECT
path || to_city AS "path",
distance
FROM paths
WHERE to_city = 'Milano' --<<< End node >>>
AND ARRAY['Napoli','Roma','Bari'] <@ path -- <<< via... >>> - <@ means 'is contained by'
ORDER BY distance, path
LIMIT 5;
Я надеюсь, что кто-то может мне помочь, как преобразовать
ARRAY[from_city] AS "path"
в определении путей
Это условия в путях-CTE:
AND b2.from_city <> ALL (p.path[2:array_upper(p.path,1)]) /* Prevent re-tracing */
AND array_upper(p.path,1) < 6)
И это условие выбора, который использует CTE:
AND ARRAY['Napoli','Roma','Bari'] <@ path -- <<< via... >>> - <@ means 'is contained by'
1 ответ
Решение
Попробуйте следующее
-- Paths from Palermo to Milano...
;WITH both_ways (
from_city,
to_city,
distance
) /* Working Table containing all ways */
AS (
SELECT
from_city,
to_city,
distance
FROM pairs
UNION ALL
SELECT
to_city AS "from_city",
from_city AS "to_city",
distance
FROM pairs
),
paths (
from_city,
to_city,
distance,
path,
Step
)
AS (
SELECT
from_city,
to_city,
distance,
CAST(CONCAT('[',from_city,']') AS varchar(MAX)) AS "path",
1 Step
FROM both_ways b1
WHERE b1.from_city = 'Palermo' --<<< Start Node >>>
UNION ALL
SELECT
b2.from_city,
b2.to_city,
p.distance + b2.distance,
p.path + CONCAT('[',b2.from_city,']'),
p.Step+1
FROM both_ways b2
JOIN paths p
ON (p.to_city = b2.from_city
AND CHARINDEX(CONCAT('[',b2.from_city,']'),p.path)=0 /* Prevent re-tracing */
AND p.Step < 6)
)
SELECT TOP 5
REPLACE(REPLACE(REPLACE(path,'][',','),'[',''),']',',') + to_city AS "path",
distance
FROM paths
WHERE to_city = 'Milano' --<<< End node >>>
AND CHARINDEX('[Napoli]',path)>0
AND CHARINDEX('[Roma]',path)>0
AND CHARINDEX('[Bari]',path)>0 -- <<< via... >>> - <@ means 'is contained by'
ORDER BY distance, path
Если функция CONCAT
не работает, то все использовали конкатенацию с +
CONCAT('[',from_city,']') => '['+from_city+']'