SQL Cookbook
1. 利用LAG OVER抑制结果集中的重复值
原始结果如下：
SQL> select deptno,ename from emp order by deptno; DEPTNO ENAME ------ ---------- 10 CLARK 10 KING 10 MILLER 20 JONES 20 FORD 20 ADAMS 20 SMITH 20 SCOTT 30 WARD 30 TURNER 30 ALLEN 30 JAMES 30 BLAKE 30 MARTIN 14 rows selected.
使用窗口函数LAG OVER，为每行返回它的前一个DEPTNO
SQL> select lag(deptno)over(order by deptno) lag_deptno,deptno,ename from emp; LAG_DEPTNO DEPTNO ENAME ---------- ------ ---------- 10 CLARK 10 10 KING 10 10 MILLER 10 20 JONES 20 20 FORD 20 20 ADAMS 20 20 SMITH 20 20 SCOTT 20 30 WARD 30 30 TURNER 30 30 ALLEN 30 30 JAMES 30 30 BLAKE 30 30 MARTIN 14 rows selected.
观察上面的结果集，对于DEPTNO与LAG_DEPTNO相匹配的行，需要把DEPTNO设置为NULL。使用DECODE可完成此功能（使用TO_NUMBER是为了把DEPTNO转换为数值）：
SQL> select to_number(decode(lag(deptno)over(order by deptno),deptno,null,deptno))deptno, 2 ename from emp; DEPTNO ENAME ------ ---------- 10 CLARK KING MILLER 20 JONES FORD ADAMS SMITH SCOTT 30 WARD TURNER ALLEN JAMES BLAKE MARTIN 14 rows selected.
2. 从表中随机返回n条记录--如果直接用 where rownum<=n的话，每次返回的记录都一样
SQL> select * from ( 2 select ename,job from emp order by dbms_random.value() 3 ) where rownum<=5;
3. 将空值转换为实际值
SQL> select coalesce(comm,0) from emp;
coalesce函数有1个或多个参数，该函数返回列表中的第一个非空值。在本例中，只要comm不为空，就返回comm的值，否则返回0
4. 如何对字母数据混合的数据分别截取字母或者数字
在这里，主要用到translate函数和replace函数
首先，创建视图构建数据
SQL> create view v as select ename ||' '||deptno as data from emp;
查询一下数据
SQL> select * from v; DATA --------------------------------------------------- SMITH 20 ALLEN 30 WARD 30 JONES 20
如何从该列中截取字母呢？在这里我们需要用到translate函数和replace函数
translate函数将数字转换为#
SQL> select translate(data,'0123456789','#') from v; TRANSLATE(DATA,'0123456789','#') --------------------------------------------------- SMITH # ALLEN # WARD # JONES #
replace函数将上述结果中#转换为空字符
SQL> select replace(translate(data,'0123456789','#'),'#','') from v; REPLACE(TRANSLATE(DATA,'0123456789','#'),'#','') --------------------------------------------------- SMITH ALLEN WARD JONES
最后，利用replace函数和上述结果截取数字
SQL> select replace(data,replace(translate(data,'0123456789','#'),'#',''),'') from v; REPLACE(DATA,REPLACE(TRANSLATE(DATA,'0123456789','# --------------------------------------------------- 20 30 30 20
5. 如何输出整点值
SQL> select to_char(TRUNC(sysdate)+(rownum-1)/24,'hh24:mi') from dual connect by rownum<=24; TO_CH ----- 00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 TO_CH ----- 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 TO_CH ----- 22:00 23:00 24 rows selected.
6. 显示各部门员工的工资，并附带显示该部分的最高工资
SELECT E.DEPTNO, E.EMPNO, E.ENAME, E.SAL, LAST_VALUE(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL ROWS --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录 --unbounded：不受控制的，无限的 --preceding：在...之前 --following：在...之后 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL FROM EMP E;
输出结果如下：
DEPTNO EMPNO ENAME SAL MAX_SAL ---------- ---------- ---------- ---------- ---------- 10 7934 MILLER 1300 5000 10 7782 CLARK 2450 5000 10 7839 KING 5000 5000 20 7369 SMITH 800 3000 20 7876 ADAMS 1100 3000 20 7566 JONES 2975 3000 20 7788 SCOTT 3000 3000 20 7902 FORD 3000 3000 30 7900 JAMES 950 2850 30 7654 MARTIN 1250 2850 30 7521 WARD 1250 2850 DEPTNO EMPNO ENAME SAL MAX_SAL ---------- ---------- ---------- ---------- ---------- 30 7844 TURNER 1500 2850 30 7499 ALLEN 1600 2850 30 7698 BLAKE 2850 2850
7. 请根据以下emp表信息，写出sql，删除除了ID不同，其它都相同的雇员的冗余信息
SQL> select * from emp; ID USERCOD USERNAME SE ADDRESS MOBILE ---------- ------- ---------- -- ---------- ----------- 1 9900001 张三 01 guangzhou 13800138000 2 9900002 李四 02 shanghai 13900139000 3 9900001 张三 01 guangzhou 13800138000 4 9900001 张三 02 guangzhou 13800138000 5 9900002 李四 02 shanghai 13900139000
SQL如下：
SQL> delete from emp where emp.id not in ( select min(id) from emp group by usercode,username,sex,address,mobile);
