Oracle基础学习之空值排序

新建表：

 1 -- Create table
 2 create table EMP
 3 (
 4   empno    NUMBER(4),
 5   ename    VARCHAR2(10),
 6   job      VARCHAR2(9),
 7   mgr      NUMBER(4),
 8   hiredate DATE,
 9   sal      NUMBER(7,2),
10   comm     NUMBER(7,2),
11   deptno   NUMBER(2)
12 )
13 tablespace USERS
14   pctfree 10
15   initrans 1
16   maxtrans 255
17   storage
18   (
19     initial 64K
20     next 1M
21     minextents 1
22     maxextents unlimited
23   );
24 -- Add comments to the columns 
25 comment on column EMP.empno
26   is '编码';
27 comment on column EMP.ename
28   is '名称';
29 comment on column EMP.job
30   is '工作';
31 comment on column EMP.mgr
32   is '主管';
33 comment on column EMP.hiredate
34   is '聘用时间';
35 comment on column EMP.sal
36   is '工资';
37 comment on column EMP.comm
38   is '提成';
39 comment on column EMP.deptno
40   is '部门编码';

 

初始化数据：

 1 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
 2 values (7369, 'SMITH', 'CLERK', 7902, to_date('27-12-1980', 'dd-mm-yyyy'), 800.00, null, 20, 7369, 'SMITH');
 3 
 4 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
 5 values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600.00, 300.00, 30, 7499, 'SALESMAN');
 6 
 7 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
 8 values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250.00, 500.00, 30, 7521, 'WARD');
 9 
10 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
11 values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975.00, null, 20, 7566, 'MANAGER');
12 
13 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
14 values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250.00, 1400.00, 30, 7654, 'SALESMAN');
15 
16 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
17 values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850.00, null, 30, 7698, 'MANAGER');
18 
19 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
20 values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450.00, null, 10, 7782, 'MANAGER');
21 
22 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
23 values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000.00, null, 20, 7788, 'SCOTT');
24 
25 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
26 values (7839, 'KING', 'PERSIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000.00, null, 10, 7839, 'PERSIDENT');
27 
28 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
29 values (7844, 'TURNER', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1500.00, 0.00, 30, 7844, 'TURNER');
30 
31 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
32 values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100.00, null, 20, 7876, 'CLERK');
33 
34 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
35 values (7900, 'JAMES', 'CLERK', 6798, to_date('03-12-1981', 'dd-mm-yyyy'), 950.00, null, 30, 7900, 'JAMES');
36 
37 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
38 values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000.00, null, 20, 7902, 'FORD');
39 
40 insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GREATEST(T.SAL,T.EMPNO), GREATEST(T.ENAME,T.JOB))
41 values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300.00, null, 10, 7934, 'MILLER');

 

示例一：

1 select t.empno, t.sal, comm, nvl(t.comm, -1) from emp t order by 4;

结果：

 

示例二：

1 select t.empno, t.sal, comm, nvl(t.comm, -1) from emp t order by 3 nulls first

结果：

 

结论：

　　order by nulls first 表示空值在前

　　order by nulls last 表示空值在后

 

posted @ 2018-11-02 11:38  ssssxy  阅读(286)  评论(0)    收藏  举报