Oracle排序改写

以指定查询列排序查询

--ASC升序，默认可省略
--DESC倒序
--可以在order by子句后用查询列号代替列名进行排序查询

--指定字段排序
select empno, ename, hiredate from emp where deptno = 10 order by hiredate;
--or
select empno, ename, hiredate from emp where deptno = 10 order by 3;

--多个字段排序
select empno, deptno, sal, ename, job from emp where deptno = 10 order by 2, 3 DESC;
--or
select empno, deptno, sal, ename, job from emp where deptno = 10 order by deptno, sal DESC;

--按查询字段子串排序
select ename, substr(empno,4,1), sal,job from emp order by 2 DESC;

按随机列排序并返回指定条记录

select empno, ename
from(select empno, ename from emp order by dbms_random.value())
where rownum <= 3;--先随机排序再取数据

处理空值排序

--可以使用NVL将空值转换后再排序，也可以使用NULLS FIRST,NULLS LAST
--默认排序中空值最大
select ename, sal, nvl(comm, -1) from emp  order by 3;
select ename, sal, comm from emp  order by 3;--升序默认空值在后NULLS LAST
select ename, sal, comm from emp  order by 3 DESC;--降序默认空值在前NULLS FIRST
select ename, sal, comm from emp  order by 3 NULLS FIRST;
select ename, sal, comm from emp  order by 3 DESC NULLS LAST;

按列值自定义列再排序

--使用case语句判断生成列再排序
select empno, ename, sal,
        case when sal >= 1000 and sal <=1999 then 1
        else 2 end as jibie
from emp where deptno = 30 order by 4, 3;

---

CREATE TABLE EMP
(
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2)
);

COMMENT ON TABLE EMP IS '员工表';
COMMENT ON COLUMN EMP.EMPNO IS '编码';
COMMENT ON COLUMN EMP.ENAME IS '名称';
COMMENT ON COLUMN EMP.JOB IS '工作';
COMMENT ON COLUMN EMP.MGR IS '主管';
COMMENT ON COLUMN EMP.HIREDATE IS '聘用日期';
COMMENT ON COLUMN EMP.SAL IS '工资';
COMMENT ON COLUMN EMP.COMM IS '提成';
COMMENT ON COLUMN EMP.DEPTNO IS '部门编码';

INSERT INTO EMP
SELECT 7369 AS EMPNO, 'SMITH' AS ENAME, 'CLERK' AS JOB, 7902 AS MGR, TO_DATE('19801217','yyyy-MM-dd') AS HIREDATE, 800 AS SAL, NULL AS COMM, 20 AS DEPTNO FROM DUAL UNION ALL
SELECT 7499 AS EMPNO, 'ALLEN' AS ENAME, 'SALESMAN' AS JOB, 7698 AS MGR, TO_DATE('19810220','yyyy-MM-dd') AS HIREDATE, 1600 AS SAL, 300 AS COMM, 30 AS DEPTNO FROM DUAL UNION ALL
SELECT 7521 AS EMPNO, 'WARD' AS ENAME, 'SALESMAN' AS JOB, 7698 AS MGR, TO_DATE('19810222','yyyy-MM-dd') AS HIREDATE, 1250 AS SAL, 500 AS COMM, 30 AS DEPTNO FROM DUAL UNION ALL
SELECT 7566 AS EMPNO, 'JONES' AS ENAME, 'MANAGER' AS JOB, 7839 AS MGR, TO_DATE('19810402','yyyy-MM-dd') AS HIREDATE, 2975 AS SAL, NULL AS COMM, 20 AS DEPTNO FROM DUAL UNION ALL
SELECT 7654 AS EMPNO, 'MARTIN' AS ENAME, 'SALESMAN' AS JOB, 7698 AS MGR, TO_DATE('19810928','yyyy-MM-dd') AS HIREDATE, 1250 AS SAL, 1400 AS COMM, 30 AS DEPTNO FROM DUAL UNION ALL
SELECT 7698 AS EMPNO, 'BLAKE' AS ENAME, 'MANAGER' AS JOB, 7839 AS MGR, TO_DATE('19810501','yyyy-MM-dd') AS HIREDATE, 2850 AS SAL, NULL AS COMM, 30 AS DEPTNO FROM DUAL UNION ALL
SELECT 7782 AS EMPNO, 'CLARK' AS ENAME, 'MANAGER' AS JOB, 7839 AS MGR, TO_DATE('19810609','yyyy-MM-dd') AS HIREDATE, 2450 AS SAL, NULL AS COMM, 10 AS DEPTNO FROM DUAL UNION ALL
SELECT 7788 AS EMPNO, 'SCOTT' AS ENAME, 'ANALYST' AS JOB, 7566 AS MGR, TO_DATE('19821209','yyyy-MM-dd') AS HIREDATE, 3000 AS SAL, NULL AS COMM, 20 AS DEPTNO FROM DUAL UNION ALL
SELECT 7839 AS EMPNO, 'KING' AS ENAME, 'PRESIDENT' AS JOB, NULL AS MGR, TO_DATE('19811117','yyyy-MM-dd') AS HIREDATE, 5000 AS SAL, NULL AS COMM, 10 AS DEPTNO FROM DUAL UNION ALL
SELECT 7844 AS EMPNO, 'TURNER' AS ENAME, 'SALESMAN' AS JOB, 7698 AS MGR, TO_DATE('19810908','yyyy-MM-dd') AS HIREDATE, 1500 AS SAL, 0 AS COMM, 30 AS DEPTNO FROM DUAL UNION ALL
SELECT 7876 AS EMPNO, 'ADAMS' AS ENAME, 'CLERK' AS JOB, 7788 AS MGR, TO_DATE('19830112','yyyy-MM-dd') AS HIREDATE, 1100 AS SAL, NULL AS COMM, 20 AS DEPTNO FROM DUAL UNION ALL
SELECT 7900 AS EMPNO, 'JAMES' AS ENAME, 'CLERK' AS JOB, 7698 AS MGR, TO_DATE('19811203','yyyy-MM-dd') AS HIREDATE, 950 AS SAL, NULL AS COMM, 30 AS DEPTNO FROM DUAL UNION ALL
SELECT 7902 AS EMPNO, 'FORO' AS ENAME, 'ANALYST' AS JOB, 7566 AS MGR, TO_DATE('19811203','yyyy-MM-dd') AS HIREDATE, 3000 AS SAL, NULL AS COMM, 20 AS DEPTNO FROM DUAL UNION ALL
SELECT 7934 AS EMPNO, 'MILLER' AS ENAME, 'CLERK' AS JOB, 7782 AS MGR, TO_DATE('19820123','yyyy-MM-dd') AS HIREDATE, 1300 AS SAL, NULL AS COMM, 10 AS DEPTNO FROM DUAL;
COMMIT;