对字符串重新按字母排序重新组合

需求描述

需求：将emp表里ename按照字母顺序重新组合生成新的字符.

解决方法:这里通过字符串合并函数或者结合substring和row_number完成该效果.

注: 数据库数据集SQL脚本详见如下链接地址

​​员工表结构和数据初始化SQL脚本​​

SQL代码

--Sql Server:
WITH x(ename,ename_Split) AS
(
SELECT TOP 100000 ename,SUBSTRING(e.ENAME,seq.pos,1) AS ename_Split
                FROM (SELECT ENAME FROM emp) e,
                (SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P' AND number>0) seq
                WHERE seq.pos<=LEN(e.ename)
                ORDER BY ename, ename_Split
)
 SELECT DISTINCT
      ename
    , STUFF((
        SELECT N'' + CAST(ename_Split AS VARCHAR(255))
        FROM x e1
        WHERE e1.ename = e2.ename 
        FOR XML PATH ('')), 1, 0, '') AS StrByAlph
FROM x e2

注: 如果想在CTE里使用[]排序,需要在查询里指定TOP.

消息 1033，级别 15，状态 1，第 67 行

除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

执行结果

处理字符串_10_对字符串重新按字母排序重新组合_字符串

-- Mysql:
SELECT ename,group_concat(SUBSTRING(e.ENAME,seq.num,1) ORDER BY SUBSTRING(e.ENAME,seq.num,1) separator '') AS StrByAlph 
FROM (SELECT ENAME FROM emp) e,
    (SELECT i AS num FROM tb_incr)seq
WHERE seq.num<=LENGTH(e.ename)
GROUP BY ename
ORDER BY ename,SUBSTRING(e.ENAME,seq.num,1)

-- 注: 这里借助group_concat函数里的ORDER BY关键字,对已经排序的字母进行合并.