查询结果排序

2.1以指定顺序返回查询结果

1.问题

你想显示部门编号为10的员工的名字、职位和工资，并根据工资从低到高排序。你希望返回如下结果集。

2.解决方案

使用ORDER BY ZI子句。

SELECT
    ename,
    job,
    sal
FROM
    emp
WHERE
    deptno = 10
ORDER BY
    sal asc

3.讨论

ORDER BY子句可以对结果集排序。本实例针对SAL按照升序排序。默认情况下，ORDER BY会做升序排序，因此ASC子句是可选项。相应地，也可以通过指定DESC执行降序排序。

SELECT
    ename,
    job,
    sal
FROM
    emp
WHERE
    deptno = 10
ORDER BY
    sal desc

你也可以不指定用于排序的列名，而指定一个数值来指代该列。数值从1开始，从左向右匹配SELECT列表里的列，如下所示。

SELECT
    ename,
    job,
    sal
FROM
    emp
WHERE
    deptno = 10
ORDER BY
    3 desc 

上述ORDER BY子句里的数字3对应着SELECT列表的第3列，即SAL。

 

2.2多字段排序

1.问题

针对EMP表的数据，你想先按照DEPTNO升序排列，然后再按照SAL降序排列。你希望返回如下的结果集。

2.解决方案

在ORDER BY 子句中列出不同的排序列，以逗号分隔。

SELECT
    empno,
    deptno,
    sal,
    ename,
    job
FROM
    emp
ORDER BY
    deptno,
    sal DESC

3.讨论

ORDER BY的执行顺序是从左到右的。如果使用SELECT列表项对应的位置序号来指定排序项，那么这个数字序号不能大于SELECT列表里的项目个数。一般而言，你也可以根据一个没有被包含在SELECT列表里的列来排序，但必须明确的指定列名。不过，如果你的查询语句里有GROUP BY或DISTINCT，那么就不能按照SELECT列表之外的列进行排序。

 

2.3依据子串排序

1.问题

你想按照一个字符串的特定部分排列查询结果。例如，你希望从EMP表检索员工的名字和职位，并且按照职位字段的最后两个字符串对检索结果进行排序。结果集应该像下面这样。

2.解决方案

DB2、MySQ、Oracle和PostgreSQL

在ORDER BY子句里使用SUBSTR函数。

SELECT
    ename,
    job
FROM
    emp
ORDER BY
    SUBSTR(job, LENGTH(job) - 2)

SQL Server

在OEDER BY子句里使用SUBSTRING函数。

SELECT
    ename,
    job
FROM
    emp
ORDER BY
    substring(job, len(job) - 2,2)

3.讨论

利用数据库中的子串函数，你可以很方便的按照一个字符串的任意部分排序。要想按照一个字符串的最后两个字符排序，需要先找到该字符串的结尾处（即字符串的长度），然后减去2。这样，起始位置就是该字符串的倒数第2个字符。然后，你就可以截取从指定起始位置开始直到字符串结束的所有字符。SQL Server的SUBSTRING函数略有不同，它要求提供第3个参数来指定需要截取几个字符。对于本例而言，第3个参数既可以是2，也可以是任何大于2的数字。

 

2.4对含有字母和数字的列排序

1.问题

你有混合了字母和数字的数据，希望按照字母部分或者数字部分来排序。考虑如下所示的试图。

CREATE VIEW V AS SELECT
    concat(ename, ' ', deptno) AS DATA
FROM
    emp

select * FROM V

或

CREATE VIEW V AS 
SELECT
    ename || ' ' || deptno AS DATA
FROM
    emp

select * FROM V

 

你希望以DEPTNO或ENAME作为排序项。若按照DEPTNO排序，会产生如下所示的结果集。

若按照ENAME排序，会产生如下所示的结果集。

2.解决方案

Oracle和PostgreSQL

使用函数REPLACE和TRANSLATE修改用于排序的字符串。

/*按照DEPTNO排序*/
SELECT
    DATA
FROM
    V
ORDER BY
    REPLACE (
        DATA,
        REPLACE (
            TRANSLATE (
                DATA,
                '0123456789',
                '##########'
            ),
            '#',
            ''
        ),
        ''
    )

/*按照ENAME排序*/
SELECT
    data
FROM
    V
ORDER BY
    REPLACE (
        TRANSLATE (
            data,
            '0123456789',
            '##########'
        ),
        '#',
        ''
    );

DB2

DB2的隐式类型转换比Oracle和PostgreSQL更严格，因此在创建视图V的时候，要先将DEPTNO的类型转换成CHAR。这种方法没有创建一个新视图，而是直接使用内嵌视图。DB2中的REPLACE函数和TRANSLATE函数的使用方式与Oracle和PostgreSQL中的相同，只是TRABSLATE函数的参数顺序稍有不同。

/* 按照DEPTNO排序 */

select  * 

   from (

selectename||'  '||cast(deptno as char(2)) as data 

from emp

)  v

order by replace(data,replace(translate(data,'#########','0123456789')，'#',''),'')

 

/* 按照ENAME排序 */

select * 

    from (

selectename||'  '||cast(deptno as char(2)) as data 

from emp 

)v 

order by replace(

translate (data,'#########','0123456789'),'#','')

 

MySQL 和 SQL Server

这些数据库不支持TRANSLATE函数，因此不能提供针对本问题的解决方案。

 

3.讨论

使用TRANSLATE函数和ERPLACE函数删除每一行的数字或者字符后，就能方便地按照剩余的部分排序。上述代码里被传递给ORDER BY的值如下述的结果集所示。（以Oracle解决方案为例的原因是，这三种数据库使用了同样的技巧，唯一特别之处在于DB2的TRANSLATE函数的参数顺序略有不同。）

select data 

           replace(data,

           replace(

      translate(data,'0123456789','##########'),'#','') nums,

        replace(

      translate(data,'0123456789','##########'),'#','') chars 

from  v 

 

2.5 排序时对Null值的处理