Let’s talk about the various sortings of ORDER BY in ORACLE

This article brings you relevant knowledge about Oracle, which mainly introduces issues related to multiple sorting of ORDER BY, including sorting by a single field, sorting by multiple fields, Sort by substring, etc. I hope this helps everyone.

Recommended tutorial: "Oracle Learning Tutorial"

1. Sort by a single field

Ascending order: ORDER BY ASC (default)

Descending order: ORDER BY DESC

Return query results in the specified order. When actually extracting data or generating reports, you generally have to view them in a certain order. For example, if you want to view Information about employees employed by the unit.

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

Many people can write this kind of statement to sort query results, but in addition to "ORDER BY hiredate ASC", it can also be written as "ORDER BY 3 ASC" , meaning sort by the third column.

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

When the value is uncertain, it is very convenient to use this method. It should be noted that

Using data to replace the column position can only be used in the order by clause. , cannot be used elsewhere

2. Sort by multiple fields

What should I do if I sort by multiple columns and there is an increase or decrease? For example, when sorting by department number in ascending order and salary in descending order, there are two keywords: ASC means ascending order and DESC means descending order. So we add two columns after order by and mark ASC and DESC respectively.

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

The following is introduced in the form of a diagram, as shown in the figure below. When sorting multiple columns, if the previous column has duplicate values ​​(such as deptno = 10, there are 3 rows of data ), the subsequent sorting is only useful. It is equivalent to dividing the data into several groups by the previous columns, and then sorting the data of each group by the following columns.

3. Sort by substring

Sort by substring There is a quick way to record the customer phone number in the order of the last number, so that when searching You can quickly narrow the scope of your query and enhance customer recognition. If you want to sort in this way, what should you do? Just use the function to get out the required information for the next few bits.

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

It can be seen that as long as the data can be queried, it can be sorted according to the corresponding information.

4. TRANSLATE

Syntax format: TRANSLATE(expr, from_string, to_string)

Examples are as follows:

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

from_string and to_string are in character units, and the corresponding characters are replaced one by one.

If to_string is empty, return a null value

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

If there is no character in the corresponding position of to_string, delete from_string The characters listed in will be deleted.

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

5. Sort by letters in a mixed string of numbers and letters

First create a VIEW as follows:

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

select * from V;

This requirement is a little more difficult. Do you see the letters inside (that is, the original column ename)? Requires alphabetical ordering within it (column ename).

Then we need to remove the letters first. We can use the replace function of translate to replace the numbers and spaces with empty spaces:

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

6. Handling sorted null values

Oracle sorts null values ​​at the back by default. What if you want to display null values ​​(such as emp.comm) at the front? Do you use NVL (comm, -1)?

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

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

空值在前

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

空值在后

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

是不是要方便得多？

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;

可以看到，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;

 

推荐教程：《Oracle教程》

The above is the detailed content of Let’s talk about the various sortings of ORDER BY in ORACLE. For more information, please follow other related articles on the PHP Chinese website!