7.5 排序查询检索的行

使用某种机制来排序信息，可以大大增强已检索的数据集的可用性。可以按字母表顺序、按数字顺序或者按时间顺序，以升序和降序的顺序来排序信息。而且，可以按一列或者多列(包括SELECT子句中没有列出的列)来排序数据。在获得SELECT语句的结果之后，才可以实现排序。排序参数不影响查询返回的记录，只会影响结果的显示。包含排序子句的语句返回的行与不包含排序子句的语句返回的行完全相同，只是输出的顺序不同而已。可以使用ORDER BY子句实现对查询结果的排序。

7.5.1 ORDER BY子句

ORDER BY子句总是SELECT语句中的最后一个子句。在SQL SELECT语句上下文中ORDER BY子句的格式如下所示：

        SELECT *|{[DISTINCT] column|expression [alias], ...}
        FROM table
        [WHERE condition(s)]
        [ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];

1．升序和降序排序

升序排列是大多数数据类型的正常顺序，因此，当指定ORDER BY子句时，升序是默认的排列顺序。数字的升序排列是由低到高，日期则是由早到晚，字符是按字母表顺序。ORDER BY子句的第一种形式表明可以按一个或者多个列或者表达式排序查询的结果：

        ORDER BY col(s)|expr;

假设需要一份报告，它必须包含员工的LAST_ NAME、HIRE_DATE和SALARY信息，按字母表顺序排序所有销售代表和市场经理的LAST_NAME列。使用下面的SELECT语句可以得到这样的报告：

        select last_name, hire_date, salary from employees
        where job_id in ('SA_REP', 'MK_MAN')
        order by last_name;

在FROM子句中，选中的数据可以按表的任何列排序，包括SELECT列表中没有出现的列。通过给ORDER BY子句添加关键字DESC，返回的行会按降序排列。可选的关键字NULLS LAST指定如果排序列包含空值，那么应该在依据NOT NULL值排序其他行之后再列出这些行。要首先显示排序列中包含空值的行，可以给ORDER BY子句添加NULLS FIRST关键字。下面的示例依据表达式排序数据集。

        select last_name, salary, hire_date, sysdate-hire_date tenure
        from employees order by tenure;

最小的TENURE值会首先出现在结果集中，因为ORDER BY子句指定结果按表达式别名排序。注意，结果按显式表达式排序，可以省略别名，但使用别名显示的查询更容易阅读。

当使用ORDER BY子句时，也选中了一些隐式默认选项。其中最重要的是，除非指定DESC，否则就假设排列顺序为升序。如果排列列中出现空值，那么在升序排列时默认顺序为NULLS LAST，降序排列时为NULLS FIRST。如果没有指定ORDER BY子句，多次执行相同查询会返回相同的结果集，但行的排列顺序可能不同，因此，无法假设行的默认排列顺序。

2．位置排序

Oracle 提供了另一种更简单的方法来指定排列列或者表达式。不是指定列名，而是将SELECT列表中列出现的位置添加到ORDER BY子句。例如下面的示例：

        select last_name, hire_date, salary from employees order by 2;

ORDER BY子句指定数字字面值2。这相当于指定了ORDER BY HIRE_DATE，因为这是SELECT子句中的第二列。位置排序只适用于SELECT列表中的列。

3．混合排序

使用混合排序可以按多个列排序查询的结果。在ORDER BY子句中，用逗号隔开两个或者更多列，这样就可以将它们(在字面上或者按位置)指定为混合排序键。例如要求从EMPLOYEES表中提取JOB_ID、LAST_NAME、SALARY和HIRE_DATE值。更进一步的要求是，结果必须是首先对JOB_ID列按字母表反序排列，然后对LAST_NAME列按字母表升序排列，最后对SALARY列按数字降序排列。下面的查询满足这些条件：

        select job_id, last_name, salary, hire_date from employees
        where job_id in ('SA_REP', 'MK_MAN') order by job_id desc, last_name, 3 desc;

练习7-4 使用ORDER BY子句排序数据

JOBS表包含对组织中员工可能从事的不同类型的工作的描述。它包含JOB_ID、JOB_TITLE、MIN_SALARY和MAX_SALARY列。要求写一个查询，提取每行的JOB_TITLE、MIN_ SALARY和MAX_SALARY列以及名为VARIANCE的表达式，它是MAX_SALARY和MIN_ SALARY值之间的差额。结果必须只包含带有单词“President”或者“Manager”的JOB_TITLE值。依据VARIANCE表达式的降序排列列表。如果有多行的VARIANCE值相同，那么再依据JOB_TITLE列按字母顺序的反序排列这些行。

(1) 启动SQL Developer，连接到HR模式。

(2) 使用ORDER BY子句完成排序。需要以降序顺序使用VARIANCE表达式和JOB_TITLE列来进行混合排序。

(3) 执行下列语句会返回匹配这一要求的结果集。

        SELECT JOB_TITLE, MIN_SALARY, MAX_SALARY, (MAX_SALARY - MIN_SALARY) VARIANCE
        FROM JOBS WHERE JOB_TITLE LIKE '%President%' OR JOB_TITLE LIKE '%Manager%'
        ORDER BY VARIANCE DESC, JOB_TITLE DESC;

7.5.2 SQL限制行的子句

许多分析查询只需要数据集的一部分，例如组织中的前三名销售员或最近雇佣的4个成员。SELECT语句中限制行的子句允许用多种方式给数据集分段。在SQL SELECT语句中，限制行的子句如下：

        SELECT *|{[DISTINCT] column|expression [alias], ...}
        FROM table
        [WHERE condition(s)]
        [ORDER BY order_by_clause]
        [OFFSET offset { ROW|ROWS}]
        [FETCH {FIRST | NEXT } [{ number_of_rows | percent percentage_of_rows }]
        { ROW | ROWS } {ONLY | WITH TIES }];

OFFSET子句指定要跳过多少行，才会到达结果集中的开始位置。如果没有OFFSET子句，OFFSET就默认为0，如果OFFSET子句是NULL、负数，或者偏移量大于所提供结果集中的总行数，OFFSET也默认为0。ROW和ROWS关键字的含义相同，同时提供单数和复数形式，会使OFFSET子句读起来更自然。

FETCH子句指定要返回的准确number_of_row(行数)或percentage_of_row(行数百分比)。没有这个子句，就会返回整个结果集，从行号OFFSET+1到最后一行。FIRST和NEXT关键字也用于澄清语义，但含义相同。在数据集中，从OFFSET 100开始提取FIRST 2，得到的结果与从OFFSET 100开始提取NEXT 2相同。

如果指定了NULL或负数行，FETCH子句的 number_of_row 选项就默认为0。给number_of_row提供混合分数会被截断，仅将整数部分用作这个参数值。如果number_of_row值大于从OFFSET+1到末尾的行数，就返回结果集中的所有行。percentage_of_row选项必须是一个数字，如果它是NULL或负值，就默认为0。这个选项指定从结果集中提取的百分比。

同样，ROW和ROWS关键字的含义相同，同时提供单数和复数形式，会使FETCH子句读起来更自然。

关键字ONLY或WITH TIES应与ROW/ROWS关键字一起使用。ROW ONLY检索指定的确切行数或百分比。使用ROWS WITH TIES，会通过检索上一行时使用的关键字，提取出其他行，但只有指定了ORDER BY子句才有效。

如图7-10所示，第一个查询从EMPLOYEES表中检索出所有107行，按薪水降序排序，再跳过100行，仅提取7行，因为该查询语句中有offset子句，最后返回前7个薪水最高的员工的姓氏和薪水。第二个查询在这7行中，从101(OFFSET+1)行开始提取前5行，缩短了这个列表。注意最后一个查询把另外一行返回给第二个查询，因为员工De Haan和Kochhar的薪水相同($17 000)，因此与同一个排序键关联。