SQL 按字母表顺序排列字符|极客教程

SQL 按字母表顺序排列字符，你想按照字母表顺序对字符串里的字符进行排序，考虑下面的结果集。

SQL 按字母表顺序排列字符问题描述

你想按照字母表顺序对字符串里的字符进行排序，考虑下面的结果集。

ENAME
----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD

你希望得到如下所示的结果集。

OLD_NAME   NEW_NAME
---------- --------
ADAMS      AADMS
ALLEN      AELLN
BLAKE      ABEKL
CLARK      ACKLR
FORD       DFOR
JAMES      AEJMS
JONES      EJNOS
KING       GIKN
MARTIN     AIMNRT
MILLER     EILLMR
SCOTT      COSTT
SMITH      HIMST
TURNER     ENRRTU
WARD       ADRW

SQL 按字母表顺序排列字符解决方案

本问题是一个绝佳的例证，它表明为什么理解一种数据库并掌握其提供的各项功能是多么重要。如果我们正在使用的数据库没有提供合适的内置函数来帮我们解决问题，我们就需要想一些别出心裁的办法。不妨比较下面的 MySQL 解决方案和其他数据库的解决方案。
DB2
为了对多行字符串进行排序，必须遍历每个字符串，然后对其中的字符进行排序。

 1  select ename,
 2         max(case when pos=1 then c else '' end)||
 3         max(case when pos=2 then c else '' end)||
 4         max(case when pos=3 then c else '' end)||
 5         max(case when pos=4 then c else '' end)||
 6         max(case when pos=5 then c else '' end)||
 7         max(case when pos=6 then c else '' end)
 8    from (
 9  select e.ename,
10         cast(substr(e.ename,iter.pos,1) as varchar(100)) c,
11         cast(row_number()over(partition by e.ename
12                              order by substr(e.ename,iter.pos,1))
13              as integer) pos
14    from emp e,
15         (select cast(row_number()over() as integer) pos
16            from emp) iter
17   where iter.pos <= length(e.ename)
18          ) x
19   group by ename

MySQL
这里的关键是 GROUP_CONCAT 函数，该函数不仅能连接员工名字字符串里的每个字符，还能对它们进行排序。

1  select ename, group_concat(c order by c separator '')
2    from (
3  select ename, substr(a.ename,iter.pos,1) c
4    from emp a,
5         ( select id pos from t10 ) iter
6   where iter.pos <= length(a.ename)
7         ) x
8   group by ename

Oracle
SYS_CONNECT_BY_PATH 函数能迭代地创建一个列表。

 1  select old_name, new_name
 2    from (
 3  select old_name, replace(sys_connect_by_path(c,' '),' ') new_name
 4    from (
 5  select e.ename old_name,
 6         row_number() over(partition by e.ename
 7                           order by substr(e.ename,iter.pos,1)) rn,
 8         substr(e.ename,iter.pos,1) c
 9    from emp e,
10         ( select rownum pos from emp ) iter
11   where iter.pos <= length(e.ename)
12   order by 1
13         ) x
14   start with rn = 1
15 connect by prior rn = rn-1 and prior old_name = old_name
16         )
17   where length(old_name) = length(new_name)

PostgreSQL
PostgreSQL 中没有能够方便地对字符串中的字符进行排序的内置函数，因此我们不仅要遍历每个字符串，还需要提前知道长度最大的员工名字。为了提高代码的可读性，本解决方案使用视图 V。

create or replace view V as
select x.*
  from (
select a.ename,
       substr(a.ename,iter.pos,1) as c
  from emp a,
       (select id as pos from t10) iter
 where iter.pos <= length(a.ename)
 order by 1,2
       ) x

下面的 SELECT 语句使用了上述视图。

 1 select ename,
 2        max(case when pos=1 then
 3                 case when cnt=1 then c
 4                      else rpad(c,cast(cnt as integer),c)
 5                 end
 6                 else ''
 7            end)||
 8        max(case when pos=2 then
 9                 case when cnt=1 then c
10                      else rpad(c,cast(cnt as integer),c)
11                 end
12                 else ''
13            end)||
14        max(case when pos=3 then
15                 case when cnt=1 then c
16                      else rpad(c,cast(cnt as integer),c)
17                 end
18                 else ''
19            end)||
20        max(case when pos=4 then
21                 case when cnt=1 then c
22                      else rpad(c,cast(cnt as integer),c)
23                 end
24                 else ''
25            end)||
26        max(case when pos=5 then
27                 case when cnt=1 then c
28                      else rpad(c,cast(cnt as integer),c)
29                 end
30                 else ''
31            end)||
32        max(case when pos=6 then
33                 case when cnt=1 then c
34                      else rpad(c,cast(cnt as integer),c)
35                 end
36                 else ''
37            end)
38   from (
39 select a.ename, a.c,
40        (select count(*)
41           from v b
42          where a.ename=b.ename and a.c=b.c ) as cnt,
43        (select count(*)+1
44           from v b
45          where a.ename=b.ename and b.c<a.c) as pos
46   from v a
47        ) x
48  group by ename

SQL Server
为了对多行字符串进行排序，必须遍历每个字符串，并对其中的字符进行排序。

 1  select ename,
 2            max(case when pos=1 then c else '' end)+
 3            max(case when pos=2 then c else '' end)+
 4            max(case when pos=3 then c else '' end)+
 5            max(case when pos=4 then c else '' end)+
 6            max(case when pos=5 then c else '' end)+
 7            max(case when pos=6 then c else '' end)
 8       from (
 9     select e.ename,
10           substring(e.ename,iter.pos,1) as c,
11           row_number() over (
12            partition by e.ename
13                order by substring(e.ename,iter.pos,1)) as pos
14       from emp e,
15            (select row_number()over(order by ename) as pos
16               from emp) iter
17      where iter.pos <= len(e.ename)
18            )  x
19      group by ename

SQL 按字母表顺序排列字符扩展知识

DB2 和 SQL Server
内嵌视图 X 把每个名字字符串里的每个字符都提取出来，并当作一行返回。函数 SUBSTR 或函数 SUBSTRING 提取出名字的每个字符，并且 ROW_NUMBER 函数按照字母表顺序排序每个字符。

ENAME    C    POS
-----    -    ---
ADAMS    A    1
ADAMS    A    2
ADAMS    D    3
ADAMS    M    4
ADAMS    S    5
...

为了把字符串中的每个字母都提取出来，并当作一行返回，我们必须遍历整个字符串。这个工作由内嵌视图 ITER 来完成。
现在，每个名字的字母都已经按照字母表顺序排列，最后一步就是将这些字母按顺序连接成新的字符串。CASE 语句（第 2 ～ 7 行）评估每个字母的位置。如果某个特定位置的字符被发现了，那么它就会被连接到下一个评估（后续的 CASE 语句）的结果里。因为调用了聚合函数 MAX，对应于每个 POS 值只有一个字符会被返回，因此对应于每个名字只会返回一行数据。CASE 语句一共有 6 个，这是因为 EMP 表里最长的名字只含有 6 个字符。
MySQL
内嵌视图 X（第 3 ～ 6 行）把每个名字的字符都提取出来，并当作一行返回。SUBSTR 函数可以提取名字字符串里的每个字符。

ENAME    C
-----    -
ADAMS    A
ADAMS    A
ADAMS    D
ADAMS    M
ADAMS    S
...

内嵌视图 ITER 用于遍历字符串。其余的工作都交由 GROUP_CONCAT 函数完成。通过指定排序方式，GROUP_CONCAT 函数不仅能串接每个字母，还能按照字母表顺序对它们进行排序。
Oracle
最重要的工作是由视图 X（第 5 ～ 11 行）完成的，它提取出每个名字的字符，并按照字母表顺序排列好。通过遍历字符串并对字符执行排序实现了这一点。查询语句的剩余部分只是将排好序的名字字符粘结到一起而已。
只执行内嵌视图 X 的话，就能看到把名字拆解之后得到的各个字符了。

OLD_NAME           RN C
---------- ---------- -
ADAMS               1 A
ADAMS               2 A
ADAMS               3 D
ADAMS               4 M
ADAMS               5 S
...

然后，提取出排好序的字符并重建每个名字。可以使用 SYS_CONNECT_BY_PATH 函数来完成这一步，它把所有的字符按顺序串接起来。

OLD_NAME   NEW_NAME
---------- ----------
ADAMS      A
ADAMS      AA
ADAMS      AAD
ADAMS      AADM
ADAMS      AADMS
...

最后，只保留那些和原名字具有相同长度的字符串。
PostgreSQL
为了提高可读性，本解决方案使用视图 V 来遍历字符串。视图里的 SUBSTR 函数会提取每个名字的全部字符，得到如下结果集。

ENAME    C
-----    -
ADAMS    A
ADAMS    A
ADAMS    D
ADAMS    M
ADAMS    S
...

该视图也按照 ENAME 和从每个名字提取出来的字母做了排序。内嵌视图 X（第 15 ～ 18 行）从视图 V 里检索出名字、字符、字符在名字里出现的次数及其位置（按字母表顺序排列）。

 ename | c | cnt | pos
-------+---+-----+-----
 ADAMS | A |   2 |   1
 ADAMS | A |   2 |   1
 ADAMS | D |   1 |   3
 ADAMS | M |   1 |   4
 ADAMS | S |   1 |   5

对于该解决方案而言，由内嵌视图 X 返回的 CNT 列和 POS 列非常重要。POS 列用来对每个字符进行排序，而 CNT 列用于确定每个字符在名字中出现的次数。最后一步是，评估每个字符的位置并重建名字。注意，每个 CASE 语句实际上都包含两个 CASE 子句。这是为了确认一个字符在名字中是否出现了不止一次。如果出现多次的话，那么返回的就不是该字符，而是由 CNT 个该字符串接而成的字符串。聚合函数 MAX 能确保每个名字只返回一行数据。