Oracle的SQL高级技巧

　　

/***********本人原创，欢迎转载，转载请保留本人信息*************/
作者：wallimn
电邮：[e-mail]
博客：http://wallimn.bokee.com
　　　http://blog.csdn.net/wallimn
时间：2008-08-17
/***********本人原创，欢迎转载，转载请保留本人信息*************/

记录一些在开发过程中，费了些事或者走了些弯路才解决的问题。

希望能给网友带来些帮助。

 

1.关于层次查询

select * from employees start with employee_id =100 connected by proior employee_id=manager_id;

这种层次查询，相信大部分人都知道。但如何保持层次关系并进行排序呢？

Oracle有个关键字：SIBLINGS专门用来解决这个问题。

写法如下：order siblings by employee_id

 

这种排序，在层次查询与树形控件结合使用的时候尤其有用。

 

2.横向显示多个依据不同条件的进行统计的count()结果

如，统计每个部门的总人数、及男性数量及女性数量

我使用的原始方法是：

select count(*) ,(select count(*) from employees where sex='1') as male,(select count(*) from employees where sex='0') as female from employees group by dept_id

可以解决问题，但速度很慢。今天在网上看到一个好方法，记录如下：

select count(*) ,count(case when sex='1' then 1 else null end) as male,count(case when sex='0' then 1 else null end) as female from employees group by dept_id

很巧妙的使用了count的特性及case，很不错。

 

3.今天发现Oracle很特别

这个语句不能用：insert into mytab (select * from mytab_tmp order by id)

注：mytab表的写了个触发器，要求按顺序处理插入的数据。

害得我只好这样写：insert into mytab (select * from (select * from mytab_tmp order by id))

 

4.ROWNUM用法

select * from (select rownum row_id ,month,sell  from (select month,sell from sale group by month,sell)) 
where row_id between 5 and 9

 

5.Oracle计算时间差的方法

总结了一下:
天：
ROUND(TO_NUMBER(END_DATE - START_DATE))
小时：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)
分钟：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)
秒：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)
毫秒：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 60)