oracle 高级函数
阅读数：1731
ORACLE常用SQL
工作两年了，一直使用的oracle，最近经常使用不常用的sql语句，索性就自己整理一下，发出来 供大家参考,后续会不断添加、整理,本文章不做详细的功能解释，主要集合描述目前接触过的一些函数
复制表结构和数据
CREATE TABLE table_name AS SELECT * FROM old_table_name;
只复制表结构
CREATE TABLE table_name AS SELECT * FROM old_table_name
WHERE 1=2
只复制表数据
两个表结构一毛一样：
INSERT INTO table_name select * from old_table_name;
结构不完全一致：
Insert into table_name(column1,column2...) select column1,column2 from old_table_name;
递归查询
从根节点开始查询：
Select t.*,level from table_name t start with t.id=0 connect by prior t.id=t.pid order by level
从子节点开始查询：
把上面的开始节点换成开始的子节点，并且prior
放在子节点前面
Select t.*,level from table_name t start with t.id=10
connect by t.id= prior t.pid order by level
合并字符串（简单的行专列）
Select to_char(wm_concat(column1 || ‘-’ || column2)) from table group by column1
拆分字符串
select REGEXP_SUBSTR('01#02#03#04', '[^#]+', 1, rownum) as newport from dual connect by rownum <= REGEXP_COUNT('01#02#03#04', '[^#]+');
With as 临时表
With t as (select 1 id,2 pid from dual),
t2 as (select 1 id,22 pid from dual)
Select t2.* from t,t2 where t.id=t2.id
行专列查询
with t as (
Select ‘桃子’ name, 300 nums ,1 jidu from dual
Union
Select ‘苹果’ name, 200 nums ,1 jidu from dual
Union
Select ‘西瓜’ name, 100 nums ,1 jidu from dual
Union
Select ‘桃子’ name, 111 nums ,2 jidu from dual
Union
Select ‘西瓜’ name, 222 nums ,2 jidu from dual
Union
Select ‘苹果’ name, 333 nums ,2 jidu from dual
)
Select * from t pivot(sum(nums) for name in (‘桃子’,’西瓜’,’苹果’))
order by jidu
列转行查询
With t as (
Select 1 id,’桃子’ name,100 q1,200 q2,300 q3,400 q4 from dual
Union
Select 2 id,’苹果’ name,111 q1,222 q2,333 q3 444 q4 from dual
Union
Select 3 id,’西瓜’ name,123 q1,234 q2,345 q3,456 q4 from dual
)
Select * from t unpivot(nums for jidu in (q1,q2,q3,q4)) order by id
排序函数
这里复制的别人的
原文链接：http://www.cnblogs.com/*sky/archive/2010/02/24/oracle_rank.html
1 select region_id, customer_id, sum(customer_sales) total,
2 rank() over(order by sum(customer_sales) desc) rank,
3 dense_rank() over(order by sum(customer_sales) desc) dense_rank,
4 row_number() over(order by sum(customer_sales) desc) row_number
5 from user_order
6 group by region_id, customer_id;
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
8 18 1253840 11 11 11
5 2 1224992 12 12 12
9 23 1224992 12 12 13
9 24 1224992 12 12 14
10 30 1216858 15 13 15
请注意上面的绿色高亮部分，这里生动的演示了3种不同的排名策略：
①对于第一条相同的记录，3种函数的排名都是一样的：12
②当出现第二条相同的记录时，Rank和Dense_rank依然给出同样的排名12；而row_number则顺延递增为13，依次类推至第三条相同的记录
③当排名进行到下一条不同的记录时，可以看到Rank函数在12和15之间空出了13,14的排名，因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增
使用分析函数为记录进行分组排名：
上面的排名是按订单总额来进行排列的，现在跟进一步：假如是为各个地区的订单总额进行排名呢？这意味着又多了一次分组操作：对记录按地区分组然后进行排名。幸亏Oracle也提供了这样的支持，我们所要做的仅仅是在over函数中order by的前面增加一个分组子句：partition
by region_id。
select region_id, customer_id, sum(customer_sales) total,
rank() over(partition by region_id
order by sum(customer_sales) desc) rank,
dense_rank() over(partition by region_id
order by sum(customer_sales) desc) dense_rank,
row_number() over(partition by region_id
order by sum(customer_sales) desc) row_number
from user_order
group by region_id, customer_id;
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
现在我们看到的排名将是基于各个地区的，而非所有区域的了！Partition by 子句在排列函数中的作用是将一个结果集划分成几个部分，这样排列函数就能够应用于这各个子集。
存在则修改，不存在则新增
判断表里面是否有满足条件的数据，存在则修改，不存在则新增
MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2ON ( T1.a=T2.a)
WHEN MATCHED THEN
UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES(T2.a,T2.b);
全连接
select * from dept full join employee on dept.deptid=employee.deptid
Nulls last/first
空值排最前面或者最后面
Select * from table_name order by id nulls first/last
取top N条数据
SELECT NO FROM (
SELECT ROW_NUMBER() OVER (ORDER BY NO) RNO, NO FROM ROWNUM_TEST
)WHERE RNO <= 5 ORDER BY NO ;
分组函数（rollup,cube,grouping sets）
rollup
假设有一个表test，有A、B、C、D、E5列。
如果使用group by rollup(A,B,C)，首先会对(A、B、C)进行GROUP
BY，然后对(A、B)进行GROUP
BY，然后是(A)进行GROUP BY，最后对全表进行GROUP
BY操作。roll up的意思是“卷起”，这也可以帮助我们理解group
by rollup个sql的结果集是一样的：
Select A,B,C,sum(E) from test group by rollup(A,B,C)
与
Select A,B,C,sum(E) from test group by A,B,C
union all
Select A,B,null,sum(E) from test group by A,B
union all
Select A,null,null,sum(E) from test group by A
union all
Select null,null,null,sum(E) from test
cube
cube的意思是立方，对cube的每个参数，都可以理解为取值为参与grouping和不参与grouping两个值的一个维度，然后所有维度取值组合的集合就是grouping的集合，对于n个参数的cube，有2^n次的grouping。如果使用group
by cube(A,B,C),，则首先会对(A、B、C)进行GROUP
BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP
BY操作，一共是2^3=8次grouping。同rollup一样，也可以用基本的group
by加上结果集的union all写出一个与group
by cube结果集相同的sql：
Select A,B,C,sum(E) from test group by cube(A,B,C);
与
Select A,B,C,sum(E) from test group by A,B,C
union all
Select A,B,null,sum(E) from test group by A,B
union all
Select A,null,C,sum(E) from test group by A,C
union all
Select A,null,null,sum(E) from test group by A
union all
Select null,B,C,sum(E) from test group by B,C
union all
Select null,B,null,sum(E) from test group by B
union all
Select null,null,C,sum(E) from test group by C
union all
Select null,null,null,sum(E) from test;
grouping sets
grouping sets就是对参数中的每个参数做grouping，也就是有几个参数做几次grouping,例如使用group
by grouping sets(A,B,C)，则对(A),(B),(C)进行group
by，如果使用group by grouping sets((A,B),C),则对(A,B),(C)进行group
by次group
by,grouping sets的参数允许重复
Keep
ID MC SL
rows selected
SQL>
SQL ;
ID MC SL
ratio_to_report 占比函数
SELECT
empno,ename,ename,hiredate,sal,deptno,
ratio_to_report(sal) over () as pct1,
ratio_to_report(sal) over (partition by deptno) as pct2
FROM emp;
Pct1 是求sal在所有sal中的比率
Pct2是求sql在按deptno分组后 所占的比率
NVL2
Select nvl2(1,2,3) FROM DUAL 相当于三目运算符
如果第一个参数为null
返回3，否则返回2
COALESC
从左到右返回第1个非null值，若所有的列表元素都为null,则返回null.它有短路计算功能，
Select COALESC(null,null,null,2null) FROM DUAL
translate函数
translate（x, from_string, to_string）函数在x中查找from_string中的字符，并将其转换成to_string中对应的字符。
from_string和to_string中的字符是一一对应的，然后根据这种对应关系，将原字符串进行转换；
select translate('ab123', 'abcdefg123', '3456789abc') from dual;
结果是: 3b5abc