- 浏览: 1267329 次
- 性别:
- 来自: 广州
文章分类
最新评论
-
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 2634写了那么多年的SQL,居然发现自己写的是错的,实在是惭愧不 ... -
慎用堆表
2014-11-18 11:14 1497有时候我们常常为了使用方便,省略建主键的建表方式。 因为很 ... -
Oracle谓词推入
2014-04-08 09:20 7124在Oracle中,谓词一般就是指where后面的那些过滤条 ... -
Oracle 执行计划
2013-11-14 10:40 0explain plan for select a.produ ... -
Oracle Share Pool 与ORA-04031
2013-07-16 11:25 1826oracle share pool详解:http://blo ... -
Oracle 分析函数Lead(),Lag()
2013-04-22 21:18 12863其实这2个函数的作用非常好理解,Lead()就是取当前顺序的 ... -
总结Oracle数组和BULK COLLECT INTO
2013-04-19 23:29 1807Oracle中使用数组,就像JAVA使用对象List< ... -
[转]Oracle表连接方式学习
2013-04-10 20:40 1502出自:http://www.itpub.net/threa ... -
Oracle SQL 嵌套CASE WHEN
2013-04-09 20:11 13238尝试了一下,Oracle CASE WHEN 是可以支持嵌套 ... -
Oracle FUNCTION与PROCEDURE的最大区别
2013-04-03 15:12 1881Oracle function里面是可以允许有DML语句的, ... -
Oracle Merge into语句
2013-03-29 15:35 2889研究这个语句完全是为了偷懒而使用的。 因为在Java写后 ... -
Oracle填坑指南之长度为0的字符串
2013-03-18 16:20 1412用一下语句查询,你认为结果是什么?? select cas ... -
Oracle数据库设计中的不更新原则
2013-02-04 16:39 1157在新增数据的时候,如果新的逻辑涉及到原有的信息,那么尽量是能 ... -
Oracle SQL判断字符串是否在目标字符串中的函数
2013-01-18 15:33 5648根据需求,写了一段方法。 用于识别以下的情况: 判断 字 ... -
[转]Oracle中使用正则表达式
2012-11-13 14:05 1444Oracle10开始支持正则表达式。 介绍内容如下: OR ... -
Oracle中使用Java存储过程
2012-11-13 13:51 1619从Oracle8i开始,就有了对Java存储过程的支持。 O ... -
Oracle索引的新认识
2012-11-08 16:16 1329首先,Oracle的索引有。 普通索引B*树,BitMap索 ... -
Oracle和Cognos Framework Manager中decode妙用,CASE WHEN也可以!
2012-11-05 17:44 2807我们都知道Decode函数可以用来做什么作用,如果这个不知道的 ... -
Oracle 数组类型
2012-10-16 11:07 4898Oracle的数组类型,范例 ... -
Oracle 列转行函数 Listagg()
2012-10-16 09:58 175032这是一个Oracle的列转行函数:LISTAGG() ...
相关推荐
标题所指的知识点为“SQL高级进阶”,描述重复强调了标题,因此不再赘述。标签为“SQL”,意味着所有知识点围绕SQL语言的高级应用和技巧。 在【部分内容】中,提供了一系列具体的SQL高级应用主题,以下是基于这些...
本文将深入探讨MySQL中的常用SQL语句,包括查询、条件查询、排序、连接查询、子查询等关键操作技巧。 1. 查询字段: - `SELECT * FROM 表名;` 查询所有字段。 - `SELECT 字段名,字段名… FROM 表名;` 查询指定...
在深入探讨《SQL Server 2000_语法进阶》这一主题时,我们首先要理解其核心在于数据库的创建、管理以及数据的操作。这不仅包括基础的SQL语法,还涉及了高级特性如视图、存储过程、触发器、游标等的使用。接下来,...
### SQL语句教程大全 #### 一、SQL基础指令概览 SQL(Structured Query Language),即结构化查询语言,是一种用于管理关系型数据库的标准语言。它不仅被用于数据的存储和检索,还广泛应用于数据的更新、删除以及...
"50个常用sql语句.rar"这个压缩包文件显然是一个学习资源,包含了50个基础到进阶的SQL语句,旨在帮助用户掌握SQL的基本操作。下面我们将详细探讨这些关键的SQL知识点。 1. **SELECT语句**:这是SQL中最常用的语句,...
根据给定的文件信息,以下是对“经典SQL...以上涵盖了SQL的基础到进阶操作,对于学习SQL语句的人来说,这些知识点是必不可少的。通过掌握这些语句,你可以有效地管理数据库,进行数据的增删改查以及复杂的分析工作。
3. **嵌套与引用**:WITH子句中定义的查询可以在同一WITH语句中的其他查询中被引用,但一个WITH子句内部不允许再嵌套另一个WITH子句。 4. **命名冲突**:如果WITH子句中定义的查询名与现有表名或其他对象名冲突,...
以上就是"SQL基础语句.pdf"中涉及的主要内容,这些知识点构成了SQL的基础操作和进阶技巧,对于数据库管理和数据分析工作至关重要。理解并熟练运用这些语句,能帮助用户更有效地管理数据,执行复杂的查询,并进行...
2. **SQL进阶** - **子查询**: 在SQL语句中嵌入查询,如:`SELECT column FROM table WHERE column IN (SELECT column FROM another_table);` - **联接操作优化**: 使用`JOIN`和`WHERE`子句的组合,理解`ON`和`...
本大全旨在提供一个全面的SQL语句参考,帮助数据库学习者掌握从基础到进阶的各种操作。 ### 基础 SQL的基础包括数据类型、数据操作和数据定义。数据类型包括数值型(如INT、FLOAT)、字符串型(如VARCHAR、CHAR)...
- **背景**:SELECT是最常用的SQL语句之一,用于从数据库中检索特定的数据行或列。 - **一般的语法规则**:SELECT语句的基本格式是“SELECT column_name(s) FROM table_name WHERE condition;”,其中WHERE子句用于...
通过以上内容的学习,我们可以看到SQL语言的强大之处,它不仅能够帮助我们管理数据库,还能让我们灵活地处理数据,进行高效的数据查询与操作。无论是对于初学者还是高级用户来说,掌握这些基本的SQL命令都是非常重要...
执行上述SQL语句后,我们将得到如下结果: | ORG_ID | ORG_NAME | LEVEL | |--------|------------|-------| | 1 | 总部 | 1 | | 2 | 销售部 | 2 | | 3 | 研发部 | 2 | | 4 | 技术支持部 | 3 | | 5 | 售后服务部 | ...
本文将深入探讨如何使用SQL语句在PostgreSQL中高效地插入客户记录和订单记录,以及相关的最佳实践。 首先,让我们理解基本的SQL插入语句结构。在PostgreSQL中,`INSERT INTO`语句用于向表中添加新行。假设我们有两...
根据给定的文件标题“sql语句大全”和描述“所有的mysql查询语句,这里都有,希望可能帮助大家”,以下将详细阐述与SQL语言相关的多个重要知识点,涵盖数据库的创建、删除、表的创建与修改、数据操作以及复杂的查询...
本教程《SQL21日自学通》旨在通过21天的学习周期,帮助读者迅速掌握SQL的基础与进阶知识。由翻译者笨猪倾力奉献,邮箱:wyhsillypig@163.com。以下是对该教程中涉及的重要知识点的详细解析: #### 第一天:SQL简介...
**SELECT** 语句是SQL中最常用也是最重要的命令之一,用于从数据库表中选择数据。根据所提供的内容,我们可以看到多种SELECT语句的应用。 ##### 1. 基础SELECT ```sql SELECT * FROM lsgscs2011; ``` 这条语句会...