强大的SQL之SQL Cookbook读书笔记1——对字母数字混合的数据排序

最近，在看SQL Cookbook真的很不错的一本书，许多解决方案，都十分精妙，真切的体会到了，SQL的强大。

注：我用的是ORACLE 11g

下面是书2.4中的一个实例--对字母数字混合的数据序列的排序

首先，我们需要书中的一张表emp，书中没有提供建表的文件或者是语句。我用的是ORACLE数据库，按照书上的数据自己建的emp表，随便建一个表，然后把数据存起来，下面是数据插入的SQL，你们有福啦，就不要一条条输入了，PS：本来想上传emp表的dump文件的，可是上传限制。

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, null, 20);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, '20-FEB-1981', 1250, 500, 30);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, '02-APR-1981', 2975, null, 20);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, '01-MAY-1981', 2850, null, 30);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, '09-JUN-1981', 2450, null, 10);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, null, 20);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, '17-NOV-1981', 5000, null, 10);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, '08-SEP-1981', 1500, 0, 30);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, null, 20);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, '03-DEC-1981', 950, null, 30);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, '03-DEC-1981', 3000, null, 20);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, null, 10);

建好表，插好数据，然后，建一个视图，语句如下，

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

接着，就是要通过建立的视图v里面的字母或者数字，排序查询结果，如何做呢？

1. 分离出数字，通过数字(DEPTNO)排序查询结果，经ORACLE数据库验证，可行，SQL如下：

SELECT DATA
  FROM V
 ORDER BY REPLACE(DATA,
                  REPLACE(TRANSLATE(DATA, '0123456789', '##########'),
                          '#',
                          ''),
                  '');

2. 分离出字母，通过字母(ENAME)排序查询结果，经ORACLE数据库验证，可行，SQL如下：

SELECT DATA
  FROM V
 ORDER BY REPLACE(TRANSLATE(DATA, '0123456789', '##########'), '#', '');

我看的时候，就感觉分离出来的字母，后面多一个空格，于是看到了书中的分离视图(VIEW)的查询语句，证实，我的想法是正确的，查询语句如下

SELECT DATA,
       REPLACE(DATA,
               REPLACE(TRANSLATE(DATA, '0123456789', '##########'), '#', ''),
               '') NUMS,
       REPLACE(TRANSLATE(DATA, '0123456789', '##########'), '#', '') CHARS
  FROM V;

把分离查询出来的字段CHARS中的值拷贝，粘贴出来，确实后面多个空格。

SQL，如此强大，学到了，哈哈。