Oracle用法、函数备忘记要
Listagg
select * from emp
select LISTAGG(ename,'-') within group (order by deptno desc) from emp;
可以看到功能类似wm_concat，可以自定义连接符，区别：
LISTAGG : 11g2才提供的函数，不支持distinct,拼接长度不能大于4000,函数返回为varchar2类型，最大长度为4000.
和wm_concat相比，listagg可以执行排序。例如
select deptno, listagg(ename,';')
within
group(order
by ename) enames from emp group
by deptno;
with table as
SQL Code
with temp as(
select 500 population, 'China' nation ,'Guangzhou' city from dual union all
select 1500 population, 'China' nation ,'Shanghai' city from dual union all
select 500 population, 'China' nation ,'Beijing' city from dual union all
select 1000 population, 'USA' nation ,'New York' city from dual union all
select 500 population, 'USA' nation ,'Bostom' city from dual union all
select 500 population, 'Japan' nation ,'Tokyo' city from dual
)
select population,
nation,
city,
listagg(city,',') within GROUP (order by city) over (partition by nation) rank
from temp
With table as 类似创建一个临时表，只可以查询一次，之后就被销毁，同时可以创建多个临时table，比如：
with sql1 as
(select to_char(a) s_name from test_tempa),
sql2 as
(select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum = 1))
select * from sql1 union all select * from sql2
pivot unpivot
行列转换，见
Minus
SQL中有一个MINUS关键字，它运用在两个SQL语句上，它先找出第一条SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中。如果有的话，那这一笔记录就被去除，而不会在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这笔资料就被抛弃，其语法如下：
[SQL Segment 1]
MINUS
[SQL Segment 2]
--------------------------------------------
--创建表1
create table test1
(
name varchar(10),
sex varchar(10),
);
insert into test1 values('test','female');
insert into test1 values('test1','female');
insert into test1 values('test1','female');
insert into test1 values('test11','female');
insert into test1 values('test111','female');
--创建表2
create table test2
(
name varchar(10),
sex varchar(10),
);
insert into test1 values('test','female');
insert into test1 values('test2','female');
insert into test1 values('test2','female');
insert into test1 values('test22','female');
insert into test1 values('test222','female');
-------------------------------------------
select * from test1 minus select * from test2;
结果：
NAME SEX
---------- ----------
test1 female
test11 female
test111 female
-----------------------------------------------------------
select * from test2 minus select * from test1;
结果:
NAME SEX
---------- ----------
test2 female
test22 female
test222 female
结论：Minus返回的总是左边表中的数据，它返回的是差集。注意：minus有剃重作用
==========================================================
下面是我做的实验，很明显能够看出MINUS的效率，made_order共23万笔记录，charge_detail共17万笔记录
性能比较:
SELECT order_id FROM made_order
MINUS
SELECT order_id FROM charge_detail
1.14 sec
select a.order_id
from made_order a
where not exists
(select order_id from charge_detail where order_id = a.order_id)
18.19 sec
select order_id
from made_order
where order_id not in (select order_id from charge_detail)
20.05 sec
nulls last(first)
排序，遇空排在前（后）
select * from emp order by comm desc nulls last
Over
select ename,max(sal) over() from emp;
rownum和row_number()
row_number() over (partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。 与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序，而row_number()在包含排序从句后是先排序再计算行号码。
select row_number() over (order by ename) rn,ename from emp ;
select rownum,ename from emp order by ename;
Partition by
select row_number() over(partition by job order by sal) row_number,
rank() over(partition by job order by sal) rank,
dense_rank() over(partition by job order by sal) dense_rank,
count(1) over(partition by job order by sal) count,
max(empno) over(partition by job order by sal) max,
sum(sal) over(partition by job order by sal) sum,
lag(ename) over(partition by job order by sal) lag,
lead(ename) over(partition by job order by sal) lead,
sal,
mgr,
job,
empno,
ename
from emp
注意： rank、dense_rank的区别，count的变化。
- Re: mellowsmile
- @我爱博客园45wq，好啊，一起交流，共同学习。