SQL 对含有字母和数字的列排序|极客教程

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

SQL 对含有字母和数字的列排序问题

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

create view V
as
select ename||' '||deptno as data
  from emp
　
select * from V

DATA
-------------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10

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

DATA
-------------
CLARK 10
KING 10
MILLER 10
SMITH 20
ADAMS 20
FORD 20
SCOTT 20
JONES 20
ALLEN 30
BLAKE 30
MARTIN 30
JAMES 30
TURNER 30
WARD 30

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

DATA
---------
ADAMS 20
ALLEN 30
BLAKE 30
CLARK 10
FORD 20
JAMES 30
JONES 20
KING 10
MARTIN 30
MILLER 10
SCOTT 20
SMITH 20
TURNER 30
WARD 30

SQL 对含有字母和数字的列排序解决方案

Oracle 和 PostgreSQL

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

/* 按照DEPTNO排序 */
　
select data
  from V
  order by replace(data,
          replace(        translate(data,'0123456789','##########'),'#',''),'')

/* 按照ENAME排序 */

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

DB2

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

/* 按照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 函数，因此不能提供针对本问题的解决方案。

SQL 对含有字母和数字的列排序扩展知识

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

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

DATA         NUMS   CHARS
------------ ------ ----------
SMITH 20     20     SMITH
ALLEN 30     30     ALLEN
WARD  30     30     WARD
JONES 20     20     JONES
MARTIN 30    30     MARTIN
BLAKE 30     30     BLAKE
CLARK 10     10     CLARK
SCOTT 20     20     SCOTT
KING 10      10     KING
TURNER 30    30     TURNER
ADAMS 20     20     ADAMS
JAMES 30     30     JAMES
FORD 20      20     FORD
MILLER 10    10     MILLER