文档章节

SQL之WITH语句进阶

吐槽的达达仔
 吐槽的达达仔
发布于 2014/12/05 22:18
字数 1248
阅读 38
收藏 1

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要高效一点。

 

 

 

 

 

 

 

本文转载自:http://dacoolbaby.iteye.com/blog/1661896

吐槽的达达仔
粉丝 28
博文 108
码字总数 8126
作品 0
广州
程序员
私信 提问
【持久化框架】Mybatis与Hibernate的详细对比

前言 这篇博文我们重点分析一下Mybatis与Hibernate的区别,当然在前面的博文中我们已经深入的研究了Mybatis和Hibernate的原理。 Mybatis 【持久化框架】Mybatis简介与原理 【持久化框架】Spr...

sietai
2018/05/18
162
0
怎么快速插入 100 条数据,用时最短

     作者:孤独键客   https://juejin.im/post/5d255ab9e51d454f73356dcd   最近群里的学习氛围又浓厚了起来。 群友又抛出了一道面试题,有人给出了答案,看完后,是否还有改进的地...

java进阶架构师
11/16
0
0
数据可视化技能的进阶路线:SQL+BI+Echarts

“数据可视化” 是一种使用技术手段表达数据结果的一种方式,但是大部分数据可视化=各种图表,这是十分外行的想法。 数据可视化是一个需要理解业务需求、数据结构、数据交互原理、数据表达逻...

帆软
01/17
0
0
Python进阶(五十二)-Flask使用pymysql连接MySQL数据库

Python进阶(五十二)-Flask使用pymysql连接MySQL数据库 IDE说明 Python:3.5 Flask:0.12.1 Pymysql:0.7.10 MySQL:5.5 前言   之前在做Python Web开发时,选择的是Django框架,后台的自动化实...

sunhuaqiang1
2017/05/31
0
0
Hibernate Criteria Query查询

Hibernate这节官网连接:http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querycriteria.html 1.创建标准查询 org.hibernate.Criteria接口表示特定持久类的一个查询。 Session是......

郏高阳
2013/08/26
2.9K
0

没有更多内容

加载失败,请刷新页面

加载更多

面试题必备-web页面基础

html标签是由<>包围的关键词 html标签是成对出现的 有部分标签是没有结束标签的,叫单标签, 页面中所有的内容,都是要放在HTML标签中的 HTML标签分三部分: 标签名称 标签内容 标签属性 HT...

达达前端小酒馆
43分钟前
10
0
OSChina 周二乱弹 —— 女装大佬被拆穿是妹子假扮

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 小小编辑推荐:《All of the Stars》- Ed Sheeran 《All of the Stars》- Ed Sheeran 手机党少年们想听歌,请使劲儿戳(这里) @Leon_swool ...

小小编辑
52分钟前
253
7
3. 彤哥说netty系列之Java BIO NIO AIO进化史

你好,我是彤哥,本篇是netty系列的第三篇。 欢迎来我的公从号彤哥读源码系统地学习源码&架构的知识。 简介 上一章我们介绍了IO的五种模型,实际上Java只支持其中的三种,即BIO/NIO/AIO。 本...

彤哥读源码
55分钟前
21
0
02.日志系统:一条SQL更新语句是如何执行的?

我们还是从一个表的一条更新语句说起,我们创建下面一张表: create table T(ID int primary key, c int); 如果要将ID=2这一行c的值加1,SQL可以这么写: update T set c=c+1 where ID=2; 前...

scgaopan
今天
10
0
【五分钟系列】掌握vscode调试技巧

调试前端js 准备一个前端项目 index.html <!DOCTYPE html><html lang="en"><head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1......

aoping
今天
11
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部