SQL Cookbook 笔记（Oracle）
（1）条件选择 case when
（2）随机排序 order by dbms_random.value()
（3）空值替换 coalesce、nvl
（6）null值排序处理
select * from emp t order by t.comm desc 【nulls last | nulls first】
（7）条件排序
select * from emp t order by t.job, case when t.job = 'SALESMAN' then comm else sal end desc, t.empno
（8）集合操作
intersect、minus
（9）标量子查询：放置在 select列表中的子查询
（10）in、update 均可多行
（11）update可使用内联视图
//=============使用字符串===============================
（1）cast 类型转换
select cast(e.sal as char(9)) sal from emp e;
（2）lower、upper 大小写
select lower('AbcD') from dual; --abcd
select upper('AbcD') from dual; --ABCD
（4）translate 转码
select translate('abc', 'ab', '12') from dual; -- '12c'
（5）replace 串替换
（6）rpad，lpad 字符截取、用特定字符填充
select rpad('abc', 2, 'X') from dual; --ab
select rpad('abc', 4, 'X') from dual; --abcX
select lpad('abc', 2, 'X') from dual; --ab
select lpad('abc', 4, 'X') from dual; --Xabc
（7）substr 截子串
（8）递归查询，汇总
select
deptno,
ltrim(sys_connect_by_path(ename, ','), ',') emps
from (
select
deptno,
ename,
row_number() over (partition by deptno order by ename) rn,
count(*) over (partition by deptno) cnt
from emp
where deptno is not null
)
where level = cnt
start with rn = 1 connect by prior deptno = deptno and prior rn = rn - 1
--output
DEPTNO EMPS
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
（9）
--列出每个部门最高/最低工资
--1
select
deptno,
min_sal,
max_sal
from (
select
deptno,
ename,
sal,
row_number() over (partition by deptno order by sal) rn,
count(*) over (partition by deptno) cnt,
min(sal) over (partition by deptno) min_sal,
max(sal) over (partition by deptno) max_sal
from emp
)
where rn = cnt
--2
select
deptno,
min_sal,
max_sal
from (
select
deptno,
min(sal) over (partition by deptno) min_sal,
max(sal) over (partition by deptno) max_sal,
row_number() over (partition by deptno order by empno) rn
from emp
)
where rn = 1
