SQL之WITH语句进阶

程序员文章站 2024-02-20 11:21:41

...

WITH语句其实是SQL ANSI标准语句之一。

我们在Oracle里面使用CONNECT BY语句进行递归查询。实际上，CONNECT BY仅仅属于Oracle自身的递归查询实现。

在标准的数据库中，如DB2，SQL SERVER，PostgreSQL都是支持WITH语句进行递归查询。

通过使用WITH语句进行递归查询，我们完全可以手动实现Oracle中的SYS_CONNECT_BY_PATH等内置函数。

以下是我从ITPUB摘录的一些WITH语句的递归使用案例。

首先，作为递归函数，可以实现我们常见的斐波那契数列。

--斐波那契 數列
WITH t(r,a,b) AS (
SELECT 1,0,1 FROM DUAL
UNION ALL
SELECT r+1
      ,b
      ,a+b
  FROM t
WHERE r<10
)
cycle a,b set iscycle to 'y' default 'n'
select r,b from t

另外就是阶乘。

--阶乘
with targ as 
(
  select 10 d from dual
)
, prod(lastnum, lastprod) as 
(select 1, 1  from dual 
union all
select lastnum+1, (lastnum+1)*lastprod 
from prod,targ
where lastnum < d
)
select * from prod,targ

然后就是Oracle里面，level关键字，和SYS_CONNECT_BY_PATH的WITH实现：

with base (id,father_id,name)
as(
 select 1, 0, 'A' from dual union all
 select 2, 1, 'BC' from dual union all
 select 3, 1, 'DE' from dual union all
 select 4, 1, 'FG' from dual union all
 select 5, 2, 'HIJ' from dual union all
 select 6, 4, 'KLM' from dual union all
 select 7, 6, 'NOPQ' from dual union all
 select 8, 5, 'RSTU' from dual 
),
T(id,father_id,name,the_level,path,root)  --注意声明必须写出结构
as(
 select id,
        father_id,
        name,
        1 as the_level,                   --表示递归的第一层  相当于LEVEL
        '\' || name as path,              --路径的第一层
        name as root                      --相当于根节点
   from base
  where id = 1                           --这里相当于 START WITH 条件
 union all                               --下面是递归
 select b.id,
        b.father_id,
        b.name,
        1 + t.the_level,
        t.path || '\' || b.name,
        t.root
   from t, base b
  where t.id = b.father_id                --这里是CONNECT BY条件
)
SEARCH DEPTH FIRST BY NAME SET NAME_ORDER  --树的深度
--SEARCH BREADTH FIRST BY NAME SET NAME_ORDER2 --树的广度
select * from t

这里使用到了SEARCH关键字，是属于可选的。在我之前的博文里面也有写到，WITH语句有个NOCYCLE的关键字，和CYCLE关键字。也是属于可选的。

这里顺便讲一下SEARCH关键字的用法。

SEARCH
①   { DEPTH FIRST BY c_alias [, c_alias]...
        [ ASC | DESC ]
        [ NULLS FIRST | NULLS LAST ]
②   | BREADTH FIRST BY c_alias [, c_alias]...
        [ ASC | DESC ]
        [ NULLS FIRST | NULLS LAST ]
     }
③    SET ordering_column

①DEPTH FIRST表示按深度优先的顺序来输出。BY后面的列名及其升降序、空值放置顺序指明了在深度优先的前提下，同一层次的数据的排序情况。这和原来CONNECT BY查询中的ORDER SIBLING BY子句是一样的。
②BREADTH FIRST表示按广度优先的顺序来输出。BY后面的列名及其升降序、空值放置顺序指明了在广度优先的前提下，同一层次的数据的排序情况。
③列名ordering_column用于存放排序后的序号，是一个从1开始的连续正整数。后续的查询中可以利用这个列得知某行数据在整个结果集中的位置。

可以通过上面的SQL，分别使用广度优先很深度优先进行搜索。看看有什么不同。

最后就是一个路径求和，求积的问题。

比如ERP系统中的BOM表，怎么去计算这个零件所有的零件使用的总价格呢？

或者是，飞机路线选择。

--沿路求值问题。如BOM求积问题等。
WITH fares(depart,arrive, price)
as(
select 'BJ','SH',500 from dual union all
select 'SH','GZ',1500 from dual union all
select 'BJ','GZ',1800 from dual union all
select 'GZ','BJ',1600 from dual union all
select 'GZ','SH',1300 from dual union all
select 'BJ','SZ',100 from dual union all
select 'SZ','GZ',110 from dual 
)
, T (depart,arrive,path,cost,lvl) AS (
SELECT  depart  ---- 构造第一层数据：从起点城市出发
       ,arrive 
       ,'/'||depart AS PATH
       ,price
       ,1
   FROM fares 
  WHERE depart = 'BJ'  ---- 起点是北京
UNION ALL  ------- 递归部分：把衔接的新一段路程拼接进去
SELECT f.depart 
      ,f.arrive
      ,t.path||'/'||f.depart    ----- 把新的路段的起点机场拼接上去
      ,t.cost + f.price         ----- 把新的路段的票价累加到总成本中。这是递归WITH最强大的地方。
      ,t.lvl+1                  ----- 层数递增
  FROM t,fares f
WHERE f.depart=t.arrive        ----- 递归条件：起飞机场是上一段的到达机场
       AND 'BJ'<>f.arrive       ----- 目的地不能是北京，否则就绕回去了
       AND t.arrive<>'SH'       ----- 递归终止条件：如果上一段终点已经是上海，没必要继续遍历了
       AND t.cost + f.price <5000 ------- 控制总成本在5000以内，否则停止遍历。这个剪枝功能是CONNECT BY做不到的。
       AND lvl<=10   -------- 控制转机次数，转机不超过10次
       AND INSTR(t.path,'/'||f.depart)=0  ------ 新一段路程的出发机场在路径中未出现过。相当于CONNECT BY的NOCYCLE功能，或是递归WITH中的CYCLE子句。
)
SELECT t.path||'/'||t.arrive path  ---- 在右边拼上最后一段旅程的到达机场，构成完整的路径。
      ,t.cost
FROM T WHERE arrive='SH';

希望大家都能了解WITH语句。

适当地使用可以降低数据库IO，提高查询效率。

在我本人的实践中，WITH的递归效率会比CONNECT BY要高效一点。

SQL之WITH语句进阶