oracle菜鸟日记4

1. 对于case when语句会出现null值，对于一列，要使用多个case when进行类似于日历这样的，则每一列都有null，那么我们可以通过
先进行分组的形式，在进行取最大值，分组是为了使得在一组中的值能够在同一列，而max可以消除每列的null值，且这里值得注意的是
如果每组的值不在一列，那么取max对于一组的值，这个max就应该对每一列取最大值，而不是对这组中的所有值取最大值
例如：SQL> select * from test;


        ID        BAT
---------- ----------
         1          4
         1          5
         1          6
         2          4
         2          5
         2          7
         3          4
         3          5
         3          8


已选择9行。


已用时间:  00: 00: 00.00
SQL> select max(case when bat=4 or bat=6 then bat end) as d4,
  2          max(case when bat=5 then bat end) as d5,
  3             max(case when bat=7 or bat=8 then bat end) as d6
  4             from test
  5             group by id;


        D4         D5         D6
---------- ---------- ----------
         6          5
         4          5          7
         4          5          8


已用时间:  00: 00: 00.01


2. 如果是为了取得下一行的值，那么可以先考虑lead over函数，在考虑自连接


3. 任何值与null进行加减乘除都是null，有null可以想到decode，nvl，case when is null。。。。。。。。。


4. 对于nvl函数，里面的参数必须是字符型，所以当我们所求的是数字类型时，又要用到nvl时，就需要进行nvl（to_char（列或表达式），‘A/N’）


5. lead over函数，遇到重复值时的解决办法，利用lead的第二个参数，使用cnt-cn+1来解决
例如：SQL> select deptno,ename,sal,hiredate,lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
  2  from(
  3     select deptno,ename,sal,hiredate,
  4             lead(sal,cnt-rn+1) over(partition by deptno order by hiredate) next_s
  5     from (
  6     select deptno,ename,sal,hiredate,
  7     count(*) over (partition by deptno,hiredate) cnt,
  8     row_number() over (partition by deptno,hiredate order by sal) rn
  9       from emp
 10     )
 11   )
 12   /


    DEPTNO ENAME             SAL HIREDATE       DIFF
---------- ---------- ---------- -------------- --------------------
        10 CLARK            2450 09-6月 -81          -2550
        10 KING             5000 17-11月-81           3700
        10 MILLER           1300 23-1月 -82            N/A
        20 SMITH             800 17-12月-80          -2175
        20 JONES            2975 02-4月 -81            -25
        20 FORD             3000 03-12月-81              0
        20 SCOTT            3000 19-4月 -87           1900
        20 ADAMS            1100 23-5月 -87            N/A
        30 ALLEN            1600 20-2月 -81            350
        30 WARD             1250 22-2月 -81          -1600
        30 BLAKE            2850 01-5月 -81           1350
        30 TURNER           1500 08-9月 -81            250
        30 MARTIN           1250 28-9月 -81            300
        30 JAMES             950 03-12月-81            N/A


已选择14行。


已用时间:  00: 00: 00.01
其中的lpad是用来使得输出的值右对齐


6. 对于如何新增一列进行分组，
  例如： 1
        0   
         0
        0
         1
         1
         0
         0
         0
         0
         1
我们可以把这列进行累加来进行sum over 则结果是
       1
       1
       1 
       2
       3
       3  
       3
       3
       3
       4
可以利用这个来进行分组


7. dense_rank()函数是个伪列函数