添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
发财的李子  ·  Unable to create ...·  1 月前    · 
难过的牛排  ·  【R语言】如何遍历数据框修改观测值 – ...·  2 月前    · 
怕老婆的四季豆  ·  【转】C# RSA ...·  5 月前    · 
含蓄的瀑布  ·  智能数据洞察是什么--智能数据洞察-火山引擎·  5 月前    · 
曾经爱过的吐司  ·  add seralization for ...·  7 月前    · 
link管理  ›  SQL 根据字符串里的数字排序|极客教程
replace 字符串函数 mysql字符串连接 select函数
https://geek-docs.com/sql/sql-examples/sql-ordered-according-to-the-figures-in-the-string.html
咆哮的爆米花
1 年前

SQL 根据字符串里的数字排序 问题描述

你希望根据字符串里的数字对结果集进行排序，考虑下面的视图。 

create view V as
select e.ename ||' '||
       cast(e.empno as char(4))||' '||
       d.dname as data
  from emp e, dept d
 where e.deptno=d.deptno

下面是上述视图返回的数据。

-----------------------------
CLARK   7782 ACCOUNTING
KING    7839 ACCOUNTING
MILLER  7934 ACCOUNTING
SMITH   7369 RESEARCH
JONES   7566 RESEARCH
SCOTT   7788 RESEARCH
ADAMS   7876 RESEARCH
FORD    7902 RESEARCH
ALLEN   7499 SALES
WARD    7521 SALES
MARTIN  7654 SALES
BLAKE   7698 SALES
TURNER  7844 SALES
JAMES   7900 SALES

以上数据包括员工名字、员工编号和部门名称三部分数据，你希望按照中间的员工编号对该数据进行排序。

----------------------------
SMITH   7369 RESEARCH
ALLEN   7499 SALES
WARD    7521 SALES
JONES   7566 RESEARCH
MARTIN  7654 SALES
BLAKE   7698 SALES
CLARK   7782 ACCOUNTING
SCOTT   7788 RESEARCH
KING    7839 ACCOUNTING
TURNER  7844 SALES
ADAMS   7876 RESEARCH
JAMES   7900 SALES
FORD    7902 RESEARCH
MILLER  7934 ACCOUNTING

SQL 根据字符串里的数字排序 解决方案


下面的每一种解决方案都使用了各个数据库特有的函数和语法，但方法（利用内置函数 REPLACE 和 TRANSLATE）却是相同的。基本思路都是使用 REPLACE 和 TRANSLATE 函数删除字符串里的非数字字符，只留下用于排序的数字。

使用内置函数 REPLACE 和 TRANSLATE 提取字符串里的数字，并按照数字排序。


1 select data
2   from V
3  order by
4         cast(
5      replace(
6    translate(data,repeat('#',length(data)),
7      replace(
8    translate(data,'##########','0123456789'),
9             '#','')),'#','') as integer)

Oracle

使用内置函数 REPLACE 和 TRANSLATE 提取字符串里的数字，并按照数字排序。


1 select data
2   from V
3  order by
4         to_number(
5           replace(
6         translate(data,
7           replace(
8         translate(data,'0123456789','##########'),
9                  '#'),rpad('#',20,'#')),'#'))

PostgreSQL

使用内置函数 REPLACE 和 TRANSLATE 提取字符串里的数字，并按照数字排序。


1 select data
2   from V
3  order by
4         cast(
5      replace(
6    translate(data,
7      replace(
8    translate(data,'0123456789','##########'),
9             '#',''),rpad('#',20,'#')),'#','') as integer)

MySQL 和 SQL Server

在写作本书时，这两种数据库尚不支持 TRANSLATE 函数。


SQL 根据字符串里的数字排序 扩展知识


视图 V 只是为了提供演示本实例解决方案的数据。该视图只是简单地把 EMP 表的一些列串联起来。上述解决方案展示了如何以串联后的文本作为输入数据，并按照嵌入其中的员工编号进行排序。

各个解决方案的 ORDER BY 子句虽然看起来有点吓人，但效果不错。如果我们一段一段地仔细阅读的话，就会发现它其实不难理解。为了按照字符串里的数字排序，最简单的办法就是删除所有的非数字字符。删除非数字字符后，把数字字符变成数值类型，并进行排序。在开始解释每一次函数调用之前，我们要先理解各个函数被调用的次序。先从最内层的 TRANSLATE 函数调用（每个解决方案的第 8 行）开始，我们可以看到：

(1) TRANSLATE 函数（第 8 行）被调用，把执行结果传递给；

(2) REPLACE 函数（第 7 行），并把执行结果传递给；

(3) TRANSLATE 函数（第 6 行），并把执行结果传递给；

(4) REPLACE 函数（第 5 行），其执行结果被返回，最后；

(5) 转换为数值类型。

第一步是把数字替换为一个特别的字符，它和去掉数字后字符串里剩下的字符都不相同。本例中我选择了 #，并使用 TRANSLATE 函数把所有的数字都替换成 #。例如，下面的查询左边显示的是原来的字符串，右边显示的是第一次转换后得到的结果。


select data,
       translate(data,'0123456789','##########') as tmp
  from V
DATA                           TMP
 ------------------------------ -----------------------
 CLARK   7782 ACCOUNTING        CLARK   #### ACCOUNTING
 KING    7839 ACCOUNTING        KING    #### ACCOUNTING
 MILLER  7934 ACCOUNTING        MILLER  #### ACCOUNTING
 SMITH   7369 RESEARCH          SMITH   #### RESEARCH
 JONES   7566 RESEARCH          JONES   #### RESEARCH
 SCOTT   7788 RESEARCH          SCOTT   #### RESEARCH
 ADAMS   7876 RESEARCH          ADAMS   #### RESEARCH
 FORD    7902 RESEARCH          FORD    #### RESEARCH
 ALLEN   7499 SALES             ALLEN   #### SALES
 WARD    7521 SALES             WARD    #### SALES
 MARTIN  7654 SALES             MARTIN  #### SALES
 BLAKE   7698 SALES             BLAKE   #### SALES
 TURNER  7844 SALES             TURNER  #### SALES
 JAMES   7900 SALES             JAMES   #### SALES

TRANSLATE 函数找到每个字符串里的数字字符，并逐一替换为 #。转换后字符串被传递到 REPLACE 函数（第 7 行），它会删除所有的 #。


select data,
replace(
translate(data,'0123456789','##########'),'#') as tmp
  from V
DATA                           TMP
 ------------------------------ -----------------------
 CLARK   7782 ACCOUNTING        CLARK    ACCOUNTING
 KING    7839 ACCOUNTING        KING     ACCOUNTING
 MILLER  7934 ACCOUNTING        MILLER   ACCOUNTING
 SMITH   7369 RESEARCH          SMITH    RESEARCH
 JONES   7566 RESEARCH          JONES    RESEARCH
 SCOTT   7788 RESEARCH          SCOTT    RESEARCH
 ADAMS   7876 RESEARCH          ADAMS    RESEARCH
 FORD    7902 RESEARCH          FORD     RESEARCH
 ALLEN   7499 SALES             ALLEN    SALES
 WARD    7521 SALES             WARD     SALES
 MARTIN  7654 SALES             MARTIN   SALES
 BLAKE   7698 SALES             BLAKE    SALES
 TURNER  7844 SALES             TURNER   SALES
 JAMES   7900 SALES             JAMES    SALES

然后，上述结果再一次被传递给 TRANSLATE 函数，但这次是本解决方案第二次（最外层）调用 TRANSLATE 函数。该 TRANSLATE 函数在原来的字符串中搜索和 TMP 相匹配的字符。如果找到的话，就把它们都替换成 #。这一转换使得所有非数字字符能够被当作单一字符来处理（因为它们都被替换成了相同的字符）。


select data, translate(data,
             replace(
             translate(data,'0123456789','##########'),
                       '#'),
                       rpad('#',length(data),'#')) as tmp
  from V
DATA                           TMP
------------------------------ ---------------------------
CLARK   7782 ACCOUNTING        ########7782###########
KING    7839 ACCOUNTING        ########7839###########
MILLER  7934 ACCOUNTING        ########7934###########
SMITH   7369 RESEARCH          ########7369#########
JONES   7566 RESEARCH          ########7566#########
SCOTT   7788 RESEARCH          ########7788#########
ADAMS   7876 RESEARCH          ########7876#########
FORD    7902 RESEARCH          ########7902#########
ALLEN   7499 SALES             ########7499######
WARD    7521 SALES             ########7521######
MARTIN  7654 SALES             ########7654######
BLAKE   7698 SALES             ########7698######
TURNER  7844 SALES             ########7844######
JAMES   7900 SALES             ########7900######

接下来，通过调用 REPLACE 函数（第 5 行）删除所有的 #，只留下数字字符。


select data, replace(
             translate(data,
             replace(
           translate(data,'0123456789','##########'),
                     '#'),
                     rpad('#',length(data),'#')),'#') as tmp
  from V
DATA                           TMP
------------------------------ -----------
CLARK   7782 ACCOUNTING        7782
KING    7839 ACCOUNTING        7839
MILLER  7934 ACCOUNTING        7934
SMITH   7369 RESEARCH          7369
JONES   7566 RESEARCH          7566
SCOTT   7788 RESEARCH          7788
ADAMS   7876 RESEARCH          7876
FORD    7902 RESEARCH          7902
ALLEN   7499 SALES             7499
WARD    7521 SALES             7521
MARTIN  7654 SALES             7654
BLAKE   7698 SALES             7698
TURNER  7844 SALES             7844
JAMES   7900 SALES             7900

最后，使用数据库管理系统中合适的函数（通常是 CAST）把 TMP 转换为数值类型（第 4 行），结果如下所示。


select data, to_number(
              replace(
             translate(data,
             replace(
       translate(data,'0123456789','##########'),
                     '#'),
                     rpad('#',length(data),'#')),'#')) as tmp
  from V
DATA                                  TMP
------------------------------ ----------
CLARK   7782 ACCOUNTING              7782
KING    7839 ACCOUNTING              7839
MILLER  7934 ACCOUNTING              7934
SMITH   7369 RESEARCH                7369
JONES   7566 RESEARCH                7566
SCOTT   7788 RESEARCH                7788
ADAMS   7876 RESEARCH                7876
FORD    7902 RESEARCH                7902
ALLEN   7499 SALES                   7499
WARD    7521 SALES                   7521
MARTIN  7654 SALES                   7654
BLAKE   7698 SALES                   7698
TURNER  7844 SALES                   7844
JAMES   7900 SALES                   7900

当编写类似这样的查询语句时，不妨把写好的表达式放入 SELECT 列表里试着执行一下，这会非常有用。因为我们能很容易看到中间结果，直到得出最终的解决方案。然而，因为本实例的重点是对结果集进行排序，所以最终仍需要把所有的函数调用都放进 ORDER BY 子句里。
 

select data
  from V
 order by
        to_number(
          replace(
        translate( data,
          replace(
        translate( data,'0123456789','##########'),
                  '#'),rpad('#',length(data),'#')),'#'))
---------------------------
SMITH   7369 RESEARCH
ALLEN   7499 SALES
WARD    7521 SALES
JONES   7566 RESEARCH
MARTIN  7654 SALES
BLAKE   7698 SALES
CLARK   7782 ACCOUNTING
SCOTT   7788 RESEARCH
KING    7839 ACCOUNTING
TURNER  7844 SALES
ADAMS   7876 RESEARCH
JAMES   7900 SALES
FORD    7902 RESEARCH
MILLER  7934 ACCOUNTING

最后值得注意的是，本例的视图数据包含 3 个字段，其中只有一个字段是数字。如果有多个数字字段，就需要将它们拼接成一个数字，然后再排序。

            
SQL 实例
SQL 删除单行记录SQL 删除指定记录SQL 删除全表记录SQL 删除违反参照完整性的记录SQL 删除被其他表参照的记录SQL 删除重复记录SQL 列举字段SQL 列举模式中的表SQL 列举非索引外键SQL 列举约束SQL 跳过n行记录SQL 在外连接查询里使用OR逻辑SQL 识别互逆的记录SQL 提取最靠前的n行记录SQL 找出最大和最小的记录SQL 查询未来的行SQL 行值轮转SQL 对结果排序SQL 删除重复项SQL 查找骑士值SQL 生成简单的预测SQL 列举索引列SQL 用SQL生成SQLSQL 描述Oracle数据字典视图SQL 遍历字符串SQL 嵌入引号SQL 统计字符出现的次数SQL 删除不想要的字符SQL 分离数字和字符数据SQL 判断含有字母和数字的字符串SQL 根据字符串里的数字排序SQL 变换结果集成多行SQL 反向变换结果集SQL 反向变换结果集成一列SQL 删除重复数据SQL 变换结果集以实现跨行计算SQL 创建固定大小的数据桶SQL 创建预定数目的桶SQL 创建水平直方图SQL 创建垂直直方图SQL 返回非分组列SQL 计算简单的小计SQL 计算所有可能的表达式组合的小计SQL 识别非小计行SQL 使用CASE表达式标记行数据SQL 创建稀疏矩阵SQL 按照时间单位分组SQL 变换结果集成一行SQL 多维度聚合运算SQL 动态区间聚合运算SQL 变换带有小计的结果集SQL 按照子字符串排序SQL 提取姓名的首字母SQL 创建分隔列表SQL 分隔数据转换为多值IN列表SQL 按字母表顺序排列字符SQL 识别字符串里的数字字符SQL 提取第n个分隔子字符串SQL 解析IP地址SQL 计算平均值SQL 查找最小值和最大值
 
推荐文章
发财的李子  ·  Unable to create tempDir. java.io.tmpdir is set to /tmp_mob64ca12e04e7a的技术博客_51CTO博客
1 月前
难过的牛排  ·  【R语言】如何遍历数据框修改观测值 – PingCode
2 月前
怕老婆的四季豆  ·  【转】C# RSA 加密，解密与签名，验证签名 - J.晒太阳的猫 - 博客园
5 月前
含蓄的瀑布  ·  智能数据洞察是什么--智能数据洞察-火山引擎
5 月前
曾经爱过的吐司  ·  add seralization for std::optional and std::variant · Issue #114 · boostorg/serialization · GitHub
7 月前
Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
link管理 - 链接快照平台