SQL Cookbook

概述1. 利用LAG OVER抑制结果集中的重复值原始结果如下：SQL> select deptno,ename from emp order by deptno;DEPTNO ENAME-----

1.  利用LAG OVER抑制结果集中的重复值

原始结果如下：

sql> select deptno,ename from emp order by deptno;DEPTNO Ename------ ----------    10 CLARK     KING     MILLER    20 JOnes     FORD     AdamS     SMITH     SCott    30 WARD     TURNER     ALLEN     JAMES     BLAKE     MARTIN14 rows selected.

使用窗口函数LAG OVER，为每行返回它的前一个DEPTNO

sqlselect lag(deptno)over(by deptno) lag_deptno,deptno,1)">from emp;LAG_DEPTNO DEPTNO Ename-------- ------ ----------           10     20     30     14 rows selected.

观察上面的结果集，对于DEPTNO与LAG_DEPTNO相匹配的行，需要把DEPTNO设置为NulL。使用DECODE可完成此功能（使用TO_NUMBER是为了把DEPTNO转换为数值）：

sqlselect to_number(decode(lag(deptno)by deptno),null,deptno))deptno,2  ename  emp;DEPTNO Ename CLARK       KING       MILLER     JOnes       FORD       AdamS       SMITH       SCott     WARD       TURNER       ALLEN       JAMES       BLAKE       MARTIN14 rows selected.

2. 从表中随机返回n条记录--如果直接用 where rownum<=n的话，每次返回的记录都一样 

sqlselect *  (  2     select ename,job  dbms_random.value()  3  ) where rownum<=5;

3. 将空值转换为实际值

sqlselect coalesce(comm,0) from emp;

    coalesce函数有1个或多个参数，该函数返回列表中的第一个非空值。在本例中，只要comm不为空，就返回comm的值，否则返回0

4. 如何对字母数据混合的数据分别截取字母或者数字

    在这里，主要用到translate函数和replace函数

    首先，创建视图构建数据    

sqlcreate vIEw v as select ename ||' '||deptno as data from emp;

    查询一下数据

sql v;DATA-------------------------------------------------SMITH ALLEN WARD JOnes 

   如何从该列中截取字母呢？在这里我们需要用到translate函数和replace函数

   translate函数将数字转换为#

sqlselect translate(data,'0123456789',1)">#')  v;TRANSLATE(DATA,')-------------------------------------------------SMITH #ALLEN #WARD #JOnes #

  replace函数将上述结果中#转换为空字符

sqlreplace(translate(data,1)">'),1)">'')  v;REPLACE(TRANSLATE(DATA,1)">''SMITHALLENWARDJOnes

  最后，利用replace函数和上述结果截取数字

sqlreplace(data,1)">''),1)">REPLACE(DATA,1)">#---------------------------------------------------20303020

 5. 如何输出整点值

sqlselect to_char(Trunc(sysdate)+(rownum-1)/24,1)">hh24:mifrom dual connect by rownum24;TO_CH---00:0001:02:03:04:05:06:07:08:09:10:00TO_CH11:12:13:14:15:16:17:18:19:20:21:22:23:0024 rows selected.

6. 显示各部门员工的工资，并附带显示该部分的最高工资 

SELECT E.DEPTNO,E.EMPNO,E.Ename,E.SAL,LAST_VALUE(E.SAL)        OVER(PARTITION BY E.DEPTNO             ORDER  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        7782 CLARK            2450      7839 KING             5000      20       7369 SMITH             800      3000        7876 AdamS            1100      7566 JOnes            2975      7788 SCott            3000      7902 FORD             30       7900 JAMES             950      2850        7654 MARTIN           1250      7521 WARD             2850    DEPTNO      EMPNO Ename             SAL    MAX_SAL7844 TURNER           1500      7499 ALLEN            1600      7698 BLAKE            2850      2850

7. 请根据以下emp表信息，写出sql，删除除了ID不同，其它都相同的雇员的冗余信息

sql emp;        ID USERCOD USERname   SE ADDRESS    MOBILE-------- ------- ---------- -- ---------- -----------         1 9900001 张三       01 guangzhou  13800138000         2 9900002 李四       02 shanghai   13900139000         3 4 02 guangzhou  5 13900139000

sql如下：

sqldelete where emp.ID not in ( min(ID) group by usercode,username,sex,address,mobile);

 

总结

以上是内存溢出为你收集整理的SQL Cookbook全部内容，希望文章能够帮你解决SQL Cookbook所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。