公用表达式递归求最短路径详解

需求：求从A出发到D的最短路径，如图：

WITH T AS(
SELECT ‘A’ S,’B’ E,20 DIST FROM DUAL UNION
SELECT ‘A’ S,’C’ E,10 DIST FROM DUAL UNION
SELECT ‘B’ S,’C’ E,40 DIST FROM DUAL UNION
SELECT ‘B’ S,’D’ E,10 DIST FROM DUAL UNION
SELECT ‘C’ S,’D’ E,30 DIST FROM DUAL
)
SELECT S 起点,E 终点,DIST 距离 FROM T;

STEP1：设置起始点
SELECT ‘A’,’A’,0 FROM DUAL；–即A到A的距离为0

STEP2：STEP1 join T
WITH T AS(
SELECT ‘A’ S,’B’ E,20 DIST FROM DUAL UNION
SELECT ‘A’ S,’C’ E,10 DIST FROM DUAL UNION
SELECT ‘B’ S,’C’ E,40 DIST FROM DUAL UNION
SELECT ‘B’ S,’D’ E,10 DIST FROM DUAL UNION
SELECT ‘C’ S,’D’ E,30 DIST FROM DUAL
),
TMP (P_S,P_E,DIST) AS (SELECT ‘A’ ,’A’ ,0 FROM DUAL)
SELECT T.E P_S,TMP.P_E||’-‘||T.E P_E,TMP.DIST+T.DIST DIST FROM T
JOIN TMP
ON TMP.P_S=T.S;

从A出发，下一站有B、C两种可能，
A到B，下一站的起点是B，
A到C，下一站的起点是C

STEP3：STEP2 join T
WITH T AS(
SELECT ‘A’ S,’B’ E,20 DIST FROM DUAL UNION
SELECT ‘A’ S,’C’ E,10 DIST FROM DUAL UNION
SELECT ‘B’ S,’C’ E,40 DIST FROM DUAL UNION
SELECT ‘B’ S,’D’ E,10 DIST FROM DUAL UNION
SELECT ‘C’ S,’D’ E,30 DIST FROM DUAL
),
TMP (P_S,P_E,DIST) AS (
SELECT ‘B’ ,’A-B’ ,20 FROM DUAL UNION ALL
SELECT ‘C’ ,’A-C’ ,10 FROM DUAL
)
SELECT T.E P_S,TMP.P_E||’-‘||T.E P_E,TMP.DIST+T.DIST DIST FROM T
JOIN TMP
ON TMP.P_S=T.S;

从B出发，下一站有C、D两种可能
从C出发，下一站有D一种可能

STEP4：STEP3 join T
WITH T AS(
SELECT ‘A’ S,’B’ E,20 DIST FROM DUAL UNION
SELECT ‘A’ S,’C’ E,10 DIST FROM DUAL UNION
SELECT ‘B’ S,’C’ E,40 DIST FROM DUAL UNION
SELECT ‘B’ S,’D’ E,10 DIST FROM DUAL UNION
SELECT ‘C’ S,’D’ E,30 DIST FROM DUAL
),
TMP (P_S,P_E,DIST) AS (
SELECT ‘C’ ,’A-B-C’ ,20 FROM DUAL UNION ALL
SELECT ‘D’ ,’A-B-D’ ,20 FROM DUAL UNION ALL
SELECT ‘D’ ,’A-C-D’ ,10 FROM DUAL
)
SELECT T.E P_S,TMP.P_E||’-‘||T.E P_E,TMP.DIST+T.DIST DIST FROM T
JOIN TMP
ON TMP.P_S=T.S;

从C出发，下一站只有D

综上：
WITH T AS(
SELECT ‘A’ S,’B’ E,20 DIST FROM DUAL UNION
SELECT ‘A’ S,’C’ E,10 DIST FROM DUAL UNION
SELECT ‘B’ S,’C’ E,40 DIST FROM DUAL UNION
SELECT ‘B’ S,’D’ E,10 DIST FROM DUAL UNION
SELECT ‘C’ S,’D’ E,30 DIST FROM DUAL
),
TMP (P_S,P_E,DIST) AS(
SELECT ‘A’,’A’,0 FROM DUAL
UNION ALL
SELECT T.E,TMP.P_E||’-‘||T.E,TMP.DIST+T.DIST FROM T
JOIN TMP
ON TMP.P_S=T.S
)
SELECT P_E,DIST FROM TMP
WHERE p_E LIKE ‘%D’;

当然用Oracle的connect by prior能更简单地实现这个需求，但公用表达式可用于DB2、SQL Server等各种不同的数据库，个人比较喜欢这种写法。

By:xsh@live.com QQ:122121221