SQLCookBook第二章学习日记3

第二章 –查询排序结果

本章重点介绍如何自定义查询结果的外观。理解了如何控制和修改结果集，便可以提供可读性更好而且有意义的数据。

2.1以指定的次序返回查询结果

问题：显示部门10中的员工名字职位和工资，并按照工资的升序排列。

解决方案：使用order by 子句：

select ename ,job,sal from emp
    where deptno = 10
        order by sal asc

使用order by 子句可以对结果集进行排序。sal表示升序排列，默认order by 以升序方式排序，因此asc子句是可选的。desc表示降序排列。

不一定要指定排序所给予的列名，也可以给出这列的编号。这个编号从1开始，从左到右依次对应select列表中的各项目。

select ename,job,sal from emp
    where deptno = 10
        order by 3 desc 

3指的是第三列，即sal列

2.2按多个字段排序

问题：在emp表中，首先按照deptno的升序排列行，然后按照工资的降序排列 。

解决方案：在order by子句中列出不同的排序列，使用逗号分隔;

select empno,deptno,sal,ename,job from emo
    order by deptno,sal desc

讨论:
在order by中，优先次序是从左到右。如果在select列表中使用列的数字位置排序，那么，这个数值不能大于select列表中项目的数目。

一把你情况下都可以按照select列表中没有的列来排序，但是必须显式的给出排序的列名。而如果在查询中使用 group by 或distinct 则不能按照select列表中没有的列来排序。

2.3按子串排序

问题：按子串的某一部分第查询结果排序。例如，要从emp表中返回员工名字和职位，并且按照职位字段的最后两个字符排序。

解决方案：
DB2、MySQL、Oracle、PostgreSQL
在order by 子句中使用substr函数

select ename,job from emp
    order by substr(job,length(job)-2)

SQL Server

select enmae,job from emp
    order by substring(job,len(job)-2,2)

讨论：
使用dbms的子串函数，可以很容易的按字符串的一部分来排序。

要按照字符串的最后两个字符排序，首先要找到字符串的末尾（就是字符串的长度），并减去2。起始位置就是字符串中的倒数第二个字符。然后，获取从起始位置开始的所有字符。

SQLserver 在substring中需要第三个参数来指定要获取的字符数。在本例中，只要这个数目大于或等于2就可以。

2.4对字母数字的混合排序

问题：现有字母和数字混合的数据，希望按照数字或字符部分来排序。考虑这个视图：

create view V
as  
    select enmae || ' ' || deptno as data from emp

select * from V

解决方案：
Oracle 和PostgreSQL
使用函数replace 和translate 修改要排序的字符串

select data from V
    order by replace(data,
             replace(
             translate (data,'0123456789','##########'),'#',''),'')

select data from emp
    order by replace (
             translate (data,'0123456789','##########'),'#','')

DB2
在DB2中，隐士类型转换比在oracle或postgresql中更为严格。所以，为了使视图V有效，需要将deptno转换为char类型。本解决方案不重新创建视图V，只是使用诶立案视图。本解决方案跟Oracle和PostgreSQL方案中使用replace和translate的方式基本相同，只是translate参数的次序不同：

select * from (
    select ename || ' '||cast(deptno as char(2)) as datafrom emp
) V
order by replace(data,
            replace(
            translate(data,'###########','0123456789'),'#',''),'')

select * from (
    select ename || ' '||cast(deptno as char(2)) as datafrom emp
) V
order by replace(
            translate(data,'###########','0123456789'),'#','')

MySQL 和SQL Server
这些平台当前不支持translate函数，这个问题无解决方案

讨论： translate和replace 函数从每一行中去掉数字或字符，这样就可以很容易得根据具体情况来排序。传递给order by的值在随后的查询结果中显示出来（使用oracle解决方案为例，三个平台都是用相同的技术。只有传递给translate的参数顺序与DB2不同）：

select data,replace(
    replace(
    translate(data,'0123456789','###########'),'#',''),'')nums,
    replace(
    translate(data,'0123456789','###########'),'#','') chars
from V