SQL研究 - CTE深入

本文深入研究如何使用CTE完成图的深度遍历，并查找最短路径。本文假设你在为一家航空公司做一个导游的软件，其中的一个基本需求是查询从一个城市到另一个城市的飞行路线。并假设除了软件所已知的航班之外，并不存在其他的直达航班。首先你让用户看到这样的图片：

 

在后台你需要创建的表结构可以简化如下：

CREATE TABLE dbo.Flights
(
  city1       NVARCHAR(256) NOT NULL ,
  city2       NVARCHAR(256) NOT NULL ,
  distance   INT NOT NULL,
  PRIMARY KEY(city1, city2),
  CHECK(city1 < city2),
  CHECK(distance > 0)
);

表中存放如下内容：

BeiJingTaiYuan10BeijingZhenZhou15TaiYuanXiAn12TaiYuanYingChuan14TaiYuanZhenZhou8WuHanXiAn13WuHanZhenZhou10XiAnYingChuan13XiAnZhenZhou8

 

那么你现在希望找到各个城市间所有的飞行途径以及相应的路程。为此我们需要增加1个新的列：

- Path nvarchar（1024） 存放所有经过的城市；

 

我们不再需要增加Distance列，因为新增加的行自动包含原有的Distance列。

好吧，让我来看SQL吧。

 with AllFlights as
(
select city1,city2,distance from flights
union
select city2,city1,distance from flights
),
CompleteFlights as
(
select city1,city2,distance,cast(city1+'.' as nvarchar(1024)) as [path] from AllFlights

union all

select

   a.city1,

   b.city2,

   a.distance+b.distance,

   cast([path]+a.city2+'.' as nvarchar(1024))
from  CompleteFlights a join AllFlights b
 on a.city2 = b.city1 and charindex(b.city1,[path])=0 and charindex(b.city2,[path])=0
)
select * from CompleteFlights

 

直接打印出所有结果是不明智，它不能帮助我们理解发生的一切。让我们还是从SQL本身入手来理解它。

首先，我们使用了两个CTE。第一个CTE是源表的扩张，既然你可以从西安到银川，自然也能从银川到西安。可是源表为了减少冗余，并没有银川到西安的路径。

 

接下来是正宗的CTE了。请回忆我的上一篇文章讲的CTE的结构： 锚点，连接词，递归生成语句。

现在 锚点 就是第一步产生的CTE，它包含所有可以一步到达的航班信息。

连接词是UnionAll，因为我们要吧所有的路径组合起来。

递归语句是我们这个解法的核心。

首先，仍然是 i 轮结果产生 i+1 轮的结果。i 轮的结果，自然是 源 和 目的 城市间转机次数为 i 的记录。 特殊的，锚点的结果是转机次数为0的路径记录。

请注意到当转机次数增加1时，只是从原始路径AllFlights中选择，并且有两个额外的限制条件： charindex(b.city2,[path])=0

对新加入的路径如果终点出现在路径中，加入该边后必然构成一个环，那不是一个正常的结果。 我们可以推断，B.City1必然不在路径中。

 

当新的路径形成后，我们相应的修改Distance和Path变量。

 

好吧，让我们看看结果吧：为方便，我们把之前的SQL作为一个View， FlightView

 

请找出北京到太原的所有路径：select * from FlightView where city1='BeiJing' and city2='TaiYuan'

下面是结果：

BeiJingTaiYuan10BeiJing.BeijingTaiYuan23Beijing.ZhenZhou.BeijingTaiYuan35Beijing.ZhenZhou.XiAn.BeijingTaiYuan50Beijing.ZhenZhou.XiAn.YingChuan.BeijingTaiYuan50Beijing.ZhenZhou.WuHan.XiAn.BeijingTaiYuan65Beijing.ZhenZhou.WuHan.XiAn.YingChuan.

 

请找出北京到西安的最短路径：

select city1,city2,distance,[path]

from CompleteFlights
where city1='BeiJing' and city2='xian' and
         distance = (select min(distance) from CompleteFlights where city1='BeiJing' and city2='xian')

结果如下：

BeiJingXiAn22BeiJing.TaiYuan.

 从图上看，这显然是正确的结果。