- 浏览: 1256204 次
- 性别:
- 来自: 广州
文章分类
最新评论
-
jackhong1108:
你可能只知道Oracle有like,不知道Oracle有其他的 ...
Hive使用regexp,RLIKE需要使用转义字符 -
pktangshao:
a_bun 写道iijjll 写道使用wmsys.wm_con ...
Oracle 列转行函数 Listagg() -
nayouzhenai:
Spring读取properties文件作为环境变量 -
a_bun:
iijjll 写道使用wmsys.wm_concat()函数也 ...
Oracle 列转行函数 Listagg() -
Horse_Chasing:
鼓励鼓励鼓励鼓励鼓励鼓励鼓励鼓励鼓励鼓励
广州面试小结
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吗?关于SQL的join和on之间的区别
2014-12-05 22:11 2565写了那么多年的SQL,居然发现自己写的是错的,实在是惭愧不 ... -
慎用堆表
2014-11-18 11:14 1457有时候我们常常为了使用方便,省略建主键的建表方式。 因为很 ... -
Oracle谓词推入
2014-04-08 09:20 7011在Oracle中,谓词一般就是指where后面的那些过滤条 ... -
Oracle 执行计划
2013-11-14 10:40 0explain plan for select a.produ ... -
Oracle Share Pool 与ORA-04031
2013-07-16 11:25 1780oracle share pool详解:http://blo ... -
Oracle 分析函数Lead(),Lag()
2013-04-22 21:18 12764其实这2个函数的作用非常好理解,Lead()就是取当前顺序的 ... -
总结Oracle数组和BULK COLLECT INTO
2013-04-19 23:29 1754Oracle中使用数组,就像JAVA使用对象List< ... -
[转]Oracle表连接方式学习
2013-04-10 20:40 1463出自:http://www.itpub.net/threa ... -
Oracle SQL 嵌套CASE WHEN
2013-04-09 20:11 13146尝试了一下,Oracle CASE WHEN 是可以支持嵌套 ... -
Oracle FUNCTION与PROCEDURE的最大区别
2013-04-03 15:12 1839Oracle function里面是可以允许有DML语句的, ... -
Oracle Merge into语句
2013-03-29 15:35 2819研究这个语句完全是为了偷懒而使用的。 因为在Java写后 ... -
Oracle填坑指南之长度为0的字符串
2013-03-18 16:20 1372用一下语句查询,你认为结果是什么?? select cas ... -
Oracle数据库设计中的不更新原则
2013-02-04 16:39 1106在新增数据的时候,如果新的逻辑涉及到原有的信息,那么尽量是能 ... -
Oracle SQL判断字符串是否在目标字符串中的函数
2013-01-18 15:33 5579根据需求,写了一段方法。 用于识别以下的情况: 判断 字 ... -
[转]Oracle中使用正则表达式
2012-11-13 14:05 1406Oracle10开始支持正则表达式。 介绍内容如下: OR ... -
Oracle中使用Java存储过程
2012-11-13 13:51 1575从Oracle8i开始,就有了对Java存储过程的支持。 O ... -
Oracle索引的新认识
2012-11-08 16:16 1292首先,Oracle的索引有。 普通索引B*树,BitMap索 ... -
Oracle和Cognos Framework Manager中decode妙用,CASE WHEN也可以!
2012-11-05 17:44 2752我们都知道Decode函数可以用来做什么作用,如果这个不知道的 ... -
Oracle 数组类型
2012-10-16 11:07 4840Oracle的数组类型,范例 ... -
Oracle 列转行函数 Listagg()
2012-10-16 09:58 174761这是一个Oracle的列转行函数:LISTAGG() ...
相关推荐
数据库SQL语句入门到进阶,包含最常用的数据库语句。数据库SQL语句入门到进阶,包含最常用的数据库语句。
无论你是一位SQL新手,或是一位只需对SQL复习一下的资料仓储业界的老将,这里将给您提供最完整...进阶 SQL : 介绍SQL 进阶概念,以及如何 用SQL 来执行一些复杂的运算. SQL 语法 : 这里列出所有在教材中被提到的SQL 语法.
sql高级进阶sql高级进阶sql高级进阶sql高级进阶sql高级进阶
SQL语句学习入门进阶(测试题含答案).rar
资源名称:SQL语句学习入门进阶资源截图: 资源太大,传百度网盘了,链接在附件中,有需要的同学自取。
SQL进阶教程.pdf
<SQL Server 2000菜鸟进阶>,一本关于SQL的入门资料!
SQL从基础到进阶,从刷题到面试,本课程目标人群为对数据科学相关感兴趣在校学生、想转行入职数据科学相关岗位的人群(包括数据分析助理、数据(商业)分析师、机器和深度学习工程师、数据科学家等)、刚入职数据...
sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句sql注入语句...
sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明...
SQL语句进阶 1.查询字段: ————查询所有字段 select * from 表名; ————查询指定字段 select 字段名,字段名… from 表名; ————多数据表连接查询时 select 表名.字段名,表名.字段名 … from ...
sql之select语句,sql之select语句,sql之select语句,sql之select语句
进阶sql精华,常用进阶sql技巧汇总集合
全面的sql语句sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全
分享一套视频教程,名字叫SQL从基础到进阶,从刷题到面试,,,大伟老师主讲,非常棒的一套sql视频教程,完整版85讲,附带源码课件,喜欢的同学可以下载学习一下,希望对你们学习sql有所帮助
SQL注入基础和进阶
SQL 执行超长语句, SQL 执行超长语句, SQL 执行超长语句, SQL 执行超长语句