2016-0905 Oracle-SQL小结

Oracle的SQL小结

尝试用MarkDown写下项目中涉及到的一些sql用法

查询

递归查询

常见的查询语法是connect by prior,应用示例有
1. 递归查询表

例：rba_res是项目中涉及的资源表，字段格式如下：

id	parent_id	name
0		父结点
1	0	子结点

可采用下述sql查询该资源表的层次结构。

select r.* ,level, SUBSTR(SYS_CONNECT_BY_PATH(lpad(r.id,3,'0'),'/'),0) as path
from rbac_res r
start with r.parent_id = 0
connect  by prior r.id = r.parent_id

2. 依序生成临时表数据
在项目中，会遇到如下场景，需要根据起止时间生成多条时间数据，也可应用到prior来生成：
生成201601到201609的月份数据

 select add_months(
 trunc(add_months(to_date('20160101','yyyymmdd'),-1),'mm'), rownum) my_month
 from dual
 connect by rownum <= 
 (select months_between(
 trunc(to_date('20160901', 'yyyymmdd'),'mm'),                                           trunc(to_date('20160101', 'yyyymmdd'),'mm')) AS monthnum
 from dual) + 1

生成0901到0931的时间数据

select to_date('20160901', 'yyyymmdd') -1+rownum
from dual connect by rownum <= 
to_date('20160931', 'yyyymmdd') - 
to_date('20160901', 'yyyymmdd')+1
order by newdate asc

统计查询

在group by时会涉及总计、小计的聚合计算，可以结合rollup来配合计算。order_refer是一张来源表,cfrom表示来源标识（baidu，google）,media是具体的小分类（wap，web,app），需要按大类、小类来依次统计来源数量，可使用下述语句。

select t2.cfrom||'-'||t2.media||'-'||
grouping(t2.cfrom)||grouping(t2.media) as title2,             
 sum(id) clueCount
from order_refer refer 
group by rollup(t2.cfrom,t2.media)

行转列查询

使用Oracle的pivot实现，订单表Order有（org_id,order_count,area），需查询出某机构的在各地域的订单分布数：

select *  
from order pivot
(
max(order_count) for area in ('江苏','上海')
)

列转行查询

使用unpivot，和pivot相反。

更新

更新或插入操作

 merge into order using (
select *
from order_tmp
) 
on ( order_id = order_id2)
when matched then 
 update set order_name = order_name
when not matched then
 insert (order_id,order_name) values (order_id2,order_name)

---

[1]: http://blog.sina.com.cn/s/blog_5ef755720100cyo3.html pivot和unpivot
[2]: