首页 > 代码库 > 6.12 按字母顺序排列字符串

6.12 按字母顺序排列字符串

问题:如下的结果集:

+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| CLARK  |
| KING   |
| TURNER |
| JAMES  |
| MILLER |
+--------+

需要结果如下:

+--------+-----------------------------------------+
| ename  | group_concat(c order by c separator ‘‘) |
+--------+-----------------------------------------+
| ALLEN  | AELLN                                   |
| BLAKE  | ABEKL                                   |
| CLARK  | ACKLR                                   |
| JAMES  | AEJMS                                   |
| KING   | GIKN                                    |
| MARTIN | AIMNRT                                  |
| MILLER | EILLMR                                  |
| TURNER | ENRRTU                                  |
| WARD   | ADRW                                    |
+--------+-----------------------------------------+

 

解决方案:

select ename,group_concat(c order by c separator ‘‘)
from (
select ename,substr(a.ename,iter.pos,1) c
from emp a,
(select id pos from t10) iter
where iter.pos <=length(a.ename)
) x
group by ename;

 

+--------+-----------------------------------------+
| ename  | group_concat(c order by c separator ‘‘) |
+--------+-----------------------------------------+
| ALLEN  | AELLN                                   |
| BLAKE  | ABEKL                                   |
| CLARK  | ACKLR                                   |
| JAMES  | AEJMS                                   |
| KING   | GIKN                                    |
| MARTIN | AIMNRT                                  |
| MILLER | EILLMR                                  |
| TURNER | ENRRTU                                  |
| WARD   | ADRW                                    |
+--------+-----------------------------------------+

 

6.12 按字母顺序排列字符串