ORACLE ORDER BY的多种排序

快来打我* 2023-10-07 17:52 17阅读 0赞

## 1. 按单个字段排序 ##

升序：ORDER BY ASC(默认)

降序：ORDER BY DESC

以指定的次序返回查询结果实际提取数据或生成报表时，一般都要根据一定的顺序查看，比如，想查看单位所雇员工的信息。

SELECT empno, ename, hiredate FROM emp WHERE deptno = 10 ORDER BY hiredate ASC;

![b384a6abf6db4be3ad76e62fc695e6fb.png][]

给查询结果排序这种语句很多人都会写，但除了“ORDER BY hiredate ASC”这种写法外，还可以写成“ ORDER BY 3 ASC”，意思是按第三列排序。

SELECT empno, ename, hiredate FROM emp WHERE deptno = 10 ORDER BY 3 ASC;

当取值不定时，用这种方法就很方便，需要注意的是，

用数据来代替列位置只能用于 order by子句中，其他地方都不能用

## 2. 按多个字段排序 ##

如果按多列排序且有升有降怎么办？如：按部门编号升序，并按工资降序排列排序时有两个关键字：ASC表示升序、DESC表示降序所以我们在 order by后加两列，并分别标明ASC、DESC。

SELECT empno, deptno, sal, ename, job FROM emp ORDER BY 2 ASC, 3 DESC;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_10_color_FFFFFF_t_70_g_se_x_16][]

下面用图的形式进行介绍，如下图所示，多列排序时，若前面的列有重复值（如deptno = 10有3行数据），后面的排序才有用。相当于是通过前面的列把数据分成了几组，然后每组的数据再按后面的列进行排序。

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_16_color_FFFFFF_t_70_g_se_x_16][]

## 3. 按子串排序 ##

按子串排序有一种速査法就是按顾客电话号码尾号的顺序记录，这样在查找的时候就可以快速缩小査询范围，增强顾客的认可度。如果要按这种方法排序，应该怎么做呢？通过函数取出后面几位所需的信息即可。

SELECT last_name AS 名称,
           phone_number AS 号码,
           salary as 工资,
           substr(phone_number, -4) AS 尾号
      FROM hr.employees
     WHERE rownum < 5
     ORDER BY 4;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_20_color_FFFFFF_t_70_g_se_x_16][]

由此可见：只要能将数据査询出来，就能根据相应的信息排序。

## 4. TRANSLATE ##

> 语法格式：TRANSLATE(expr, from\_string, to\_string)

示例如下：

SELECT TRANSLATE（ 'ab 你好 bcadefg', 'abcdefg', '1234567' ） AS NEW_STR FROM DUAL;

![f595ad14baea4fd3a1be6338abaf031f.png][]

from\_string与to\_string以字符为单位，对应字符一一替换。

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

如果 to\_string为空，则返回空值

SELECT TRANSLATE（ 'ab 你好 bcadefg', 'abcdefg', '' ） AS NEW_STR FROM DUAL;

![dd82218e2993458d80f67140f363d212.png][]

如果 to\_string对应的位置没有字符，删除 from\_string中列出的字符将会被消掉。

SELECT TRANSLATE（ 'ab 你好 bcadefg', '1abcdefg', '1' ） AS NEW_STR FROM DUAL;

![bceaf55f2a904213b963f4c7accecbb9.png][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

## 5. 按数字和字母混合字符串中的字母排序 ##

首先创建VIEW如下：

CREATE OR REPLACE VIEW V 
    as 
    SELECT empno || ' ' || ename AS data FROM emp;

select * from V;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_3_color_FFFFFF_t_70_g_se_x_16][]

这个需求就难一点了，看到里面的字母（也就是原来的列 ename）吗？要求按其中的字母（列 ename）排序。

那么就要先取出其中的字母才行，我们可以用 translate的替换功能，把数字与空格都替换为空：

SELECt data, translate （data, '- 0123456789', '-' ）AS ename
      FROM v
     ORDER BY 2;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_6_color_FFFFFF_t_70_g_se_x_16][]

## 6. 处理排序空值 ##

Oracle默认排序空值在后面，如果想把空值(如emp.comm)显示在前面怎么办，用NVL(comm，-1)吗？

SELECT ename, sal, comm, nvl(comm， - 1) order_col FROM emp ORDER BY 4;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_8_color_FFFFFF_t_70_g_se_x_16][]

也许很多人都是用的这种方法，但这种方法需要对列类型及其中保存的数据有所了解才行，而且保存的数据如果有变化，该语句就要重新维护。  
其实可以用关键字 NULLS FIRST和 NULLS LAST。

空值在前

SELECT ename, sal, comm FROM emp ORDER BY 3 NULLS FIRST;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_6_color_FFFFFF_t_70_g_se_x_16 1][]

空值在后

SELECT ename, sal, comm FROM emp ORDER BY 3 NULLS LAST;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_6_color_FFFFFF_t_70_g_se_x_16 2][]

是不是要方便得多？

## 7. 根据条件取不同列中的值来排序 ##

有时排序的要求会比较复杂，比如：领导对工资在1000到2000元之间的员工更感兴趣，于是要求工资在这个范围的员工要排在前面，以便优先査看。  
对于这种需求，我们可以在查询中新生成一列，用多列排序的方法处理：

SELECT empno AS 编码,
           ename AS 姓名,
           CASE
             WHEN sal > 1000 AND sal < 2000 THEN
              1
             ELSE
              2
           END AS 级别,
           sal AS工资
      FROM emp
     WHERE deptno = 30
     ORDER BY 3, 4;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_8_color_FFFFFF_t_70_g_se_x_16 1][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

可以看到，950与2850都排在了后面，也可以不显示级别，直接把 case when放在order by中

SELECT empno AS 编码, ename AS 姓名, sal AS工资
      FROM emp
     WHERE deptno = 30
     ORDER BY CASE
                WHEN sal >= 1000 AND sal < 2000 THEN
                 1
                ElSE
                 2
              END,
              3;

![42de3912bb42442580f274ed31a4f0c5.png][]![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_20_color_FFFFFF_t_70_g_se_x_16 4][]

[b384a6abf6db4be3ad76e62fc695e6fb.png]: https://img-blog.csdnimg.cn/b384a6abf6db4be3ad76e62fc695e6fb.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_10_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/c1ebb7ec2631445b85681b7999fe4a55.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_10,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_16_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/3faa90e259f143c88bf064be6bcf98b0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_16,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/ed47c2c407de463b9947f9a716feea3f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_20,color_FFFFFF,t_70,g_se,x_16
[f595ad14baea4fd3a1be6338abaf031f.png]: https://img-blog.csdnimg.cn/f595ad14baea4fd3a1be6338abaf031f.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/c0cc0bce450d4a20a63e7ac4625bc686.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_20,color_FFFFFF,t_70,g_se,x_16
[dd82218e2993458d80f67140f363d212.png]: https://img-blog.csdnimg.cn/dd82218e2993458d80f67140f363d212.png
[bceaf55f2a904213b963f4c7accecbb9.png]: https://img-blog.csdnimg.cn/bceaf55f2a904213b963f4c7accecbb9.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/d0d37bb5052e4e66a035639970a52056.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_3_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/20df838d151c4591bb50ccbd23625436.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_3,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_6_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/96b7765ce9874dbd831e520c5f011143.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_6,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_8_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/bb55556fdf5d483f8b6be0707fa7ea44.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_8,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_6_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/efafd5c846ec474d95787710d333d887.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_6,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_6_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/55a4167b60af40d8bcb0f7ed0c83c310.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_6,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_8_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/e25db47ca52340b2b1213f5f508fd3e6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_8,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/14845afe94ab4044b90af3bbeb606298.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_20,color_FFFFFF,t_70,g_se,x_16
[42de3912bb42442580f274ed31a4f0c5.png]: https://img-blog.csdnimg.cn/42de3912bb42442580f274ed31a4f0c5.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAVGVyZW5jZSBXYW5n_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/a3319d57403049f8b61d1ea4008e731f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAVGVyZW5jZSBXYW5n,size_20,color_FFFFFF,t_70,g_se,x_16