【PDF已传上】准备写一个oracle高级sql的参考资料

newkid

原帖由 dingjun123 于 2010-1-22 09:21 发表
对于MINUS运算，在WHERE子句中所有的列都必须在SELECT子句中。(10个可以) 这个是错了，没有这个限制。
缺省排序好像除了union all,其它都是按第1列升序排列的，这应该没有问题，oracle官方文档上也是这样说的。

文档在哪里？我没有找到。

11G的例子里面还是用了ORDER BY:
http://download.oracle.com/docs/ ... 8286/queries004.htm

INTERSECT Example
The following statement combines the results with the INTERSECT operator, which returns only those unique rows returned by both queries:
SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items
ORDER BY product_id;

MINUS Example
The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second:
SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items
ORDER BY product_id;

它还说未来版本中INTERSECT的优先级会更高：
Note:
To comply with emerging SQL standards, a future release of Oracle will give the INTERSECT operator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the INTERSECT operator with other set operators.

wiki上也说:
http://en.wikipedia.org/wiki/Set_operations_(SQL)

Note that UNION does not guarantee the order of rows. Rows from the second operand may appear before, after, or mixed with rows from the first operand. In situations where a specific order is desired, ORDER BY must be used.

当然这里讲的不是针对哪个数据库。

dingjun123

oracle9i:Advanced sql上是这样说的
P23：
By default:the output is stored in ascending order of the first column of SELECT cluase.
后面一节是Controlling the Order of Rows
是这样说的：
By default，the output is stored in ascending order on  the first column .you can use the Order by clause to change this.

feng_no_1

友情支持一下

newkid

原帖由 dingjun123 于 2010-1-22 23:40 发表
oracle9i:Advanced sql上是这样说的
P23：
By default:the output is stored in ascending order of the first column of SELECT cluase.
后面一节是Controlling the Order of Rows
是这样说的：
By default，the output is stored in ascending order on  the first column .you can use the Order by clause to change this.

你这个我google了半天没找到。还是来看一下TOM的说法（在他的博客一篇回复）：
http://tkyte.blogspot.com/2005/08/order-in-court.html

Does UNION do a sort or is it the same as GROUP BY?

Union is a distinct operation and may or may not sort in order to distinct. In any case, the sort it does would be a binary sort, not a character set sort (eg: does not sort the same as order by would necessarily). Throw in parallel operations, partitioning and all bets are off.

另外他在asktom上重复了很多次了，比如这个：
http://asktom.oracle.com/pls/ask ... N_ID:75397449124988

newkid

又看了一些，再提一些意见或补充：

5）找出每个部门的经理
这个需求，我们可以从树中查找，也就是对于每个部门选最高等级节点。可以使用connect
by后加条件过滤branches 的方法。
select level,a.* from
s_emp a
start with manager_id is null
connect by prior id=manager_id and dept_id !=prior dept_id;--当前行的dept_id不等于前一行的
dept_id，即每个子树中选最高等级节点


这个SQL和需求“找出每个部门的经理”没什么关系。找出来的可能有很多非经理。


6）查询一个组织中最高的几个等级
用where level条件过滤
select level,a.* from
s_emp a
where level <=2 –查找前两个等级
start with manager_id is null
connect by prior id=manager_id and dept_id !=prior dept_id;


这个需求把LEVEL<=2放到CONNECT BY中会更高效，因为尽早截断遍历路径。



8）找出指定层次中的叶子节点
Leaf(叶子)就是没有子孙的孤立节点。Oracle 10g提供了一个简单的connect_by_isleaf=1,
0 表示非叶子节点
select level,id,manager_id,last_name, title from s_emp
where connect_by_isleaf=1 –表示查询叶子节点
start with manager_id=2
connect by prior id=manager_id;
Oracle advanced sql by dj mailto:dingjunlove@163.com
第 27 页 共 90 页
也可以通过连接查询获得，方法有多种，叶子节点肯定是level最大的节点。
select a.lev,b.id from (
select max(level) lev from s_emp --查询最大的level
start with manager_id=2
connect by prior id=manager_id) a,
(select level lev,id
from s_emp
start with manager_id=2
connect by prior id=manager_id) b
where a.lev=b.lev;


这方法有问题，因为子树可能不是等高的。矮树的叶子LEVEL较小。



5.3.4 CONNECT_BY_ROOT

9i办法：用SYS_CONNECT_BY_PATH, 然后用INSTR, SUBSTR把第一截解析出来。


5.3.5 使用SIBLINGS关键字排序


9i 办法：

SELECT ... FROM
(SELECT ...
      ,SYS_CONNECT_BY_PATH(LPAD(PAPRENT_ID,20)||LPAD(ORDER_BY_表达式,20),'\') AS sorting  ----- LPAD用于对齐，如果LPADORDER_BY_表达式是字符串，则第二个LPAD不用；如果是日期、时间先TO_CHAR转换为 YYYYMMDDHH24MISS
  FROM ...
START WITH ....
CONNECT BY ....
)
ORDER BY sorting



1、 固定列数的行列转换
如
name subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
按照name分组得到的结果，所以decode外面要套个sum，不然group by出现语义错误，
结果为
select name,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from student
group by name


最好用MAX或MIN不要用SUM, 这样也适用于非NUMBER型的行转列。

dingjun123

谢谢newkid指教，我试试看！

臧圩人

好东西，多谢楼主分享。

zinedine

这个帖子不顶不行

wfjt

12.ROWID的使用        71
13. ORACLE10G正则表达式        71
14.使用HINT        71
15.PARITION分区        71
16.并行操作        71

这几个没写啊

wfjt

好东西，多谢楼主分享