sql查询排序问题(包含字母、数字、小数)

Question

例如
数据库：Oracle
字段:num
部分数据如下：
AC 23-2A
AC 23-27
AC 23.1309-1D
AC 23-19A
AC 23.1419-2D
AC 23.629-1B
AC 20-66B
AC 20-42
AC 20-24b
AC 20-119
AC 20-118A
AC 25.1529-1A
AC 25.795-1A
AC 25-26
AC 25-11A
AC 33-9
AC 33.83-1
AC 33.174-1A
AC 33-12B
AC 121-138
AC 121-37
AC 135-13Q希望排序后得到的结果只按数字大小排序(小数点后的数字也是)，即
AC 20-24b
AC 20-42
AC 20-66B
AC 20-118A
AC 20-119
AC 23-2A
AC 23-19A
AC 23-27
AC 23.629-1B
AC 23.1309-1D
AC 23.1419-2D
AC 25-11A
AC 25-26
AC 25.795-1A
AC 25.1529-1A
AC 33-9
AC 33-12B
AC 33.83-1
AC 33.174-1A
AC 121-37
AC 121-138
AC 135-13Q请教能解决此问题的高手，sql语句如何写？

Answer 1

[TEST@orcl] SQL>WITH T1 AS(
  2  SELECT 'AC' A,'23-2A' B FROM DUAL UNION ALL
  3  SELECT 'AC' A,'23-27' B FROM DUAL UNION ALL
  4  SELECT 'AC' A,'23.1309-1D' B FROM DUAL UNION ALL
  5  SELECT 'AC' A,'23-19A' B FROM DUAL UNION ALL
  6  SELECT 'AC' A,'23.1419-2D' B FROM DUAL UNION ALL
  7  SELECT 'AC' A,'23.629-1B' B FROM DUAL UNION ALL
  8  SELECT 'AC' A,'20-66B' B FROM DUAL UNION ALL
  9  SELECT 'AC' A,'20-42' B FROM DUAL UNION ALL
 10  SELECT 'AC' A,'20-24b' B FROM DUAL UNION ALL
 11  SELECT 'AC' A,'20-119' B FROM DUAL UNION ALL
 12  SELECT 'AC' A,'20-118A' B FROM DUAL UNION ALL
 13  SELECT 'AC' A,'25.1529-1A' B FROM DUAL UNION ALL
 14  SELECT 'AC' A,'25.795-1A' B FROM DUAL UNION ALL
 15  SELECT 'AC' A,'25-26' B FROM DUAL UNION ALL
 16  SELECT 'AC' A,'25-11A' B FROM DUAL UNION ALL
 17  SELECT 'AC' A,'33-9' B FROM DUAL UNION ALL
 18  SELECT 'AC' A,'33.83-1' B FROM DUAL UNION ALL
 19  SELECT 'AC' A,'33.174-1A' B FROM DUAL UNION ALL
 20  SELECT 'AC' A,'33-12B' B FROM DUAL UNION ALL
 21  SELECT 'AC' A,'121-138' B FROM DUAL UNION ALL
 22  SELECT 'AC' A,'121-37' B FROM DUAL UNION ALL
 23  SELECT 'AC' A,'135-13Q' B FROM DUAL
 24  ),T2 AS(
 25    SELECT A,B,
 26           CASE WHEN INSTR(B,'.') = 0 THEN SUBSTR(B,1,INSTR(B,'-',1,1)-1)||'.0'||SUBSTR(B,INSTR(B,'-',1,1)) ELSE B END COL
 27     FROM T1
 28  ),T3 AS(
 29    SELECT A,B,COL,
 30           REGEXP_REPLACE(COL,'([^a-z|A-Z]+)([a-z|A-Z]+)','\1') COL1
 31     FROM T2
 32  )SELECT A,B,COL,COL1,
 33          REGEXP_REPLACE(COL1,'(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)','\1') A1,
 34          REGEXP_REPLACE(COL1,'(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)','\3') A2,
 35          REGEXP_REPLACE(COL1,'(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)','\5') A3
 36     FROM T3
 37    ORDER BY A,TO_NUMBER(A1),TO_NUMBER(A2),TO_NUMBER(A3);A  B          COL        COL1       A1    A2    A3
-- ---------- ---------- ---------- ----- ----- -----
AC 20-24b     20.0-24b   20.0-24    20    0     24
AC 20-42      20.0-42    20.0-42    20    0     42
AC 20-66B     20.0-66B   20.0-66    20    0     66
AC 20-118A    20.0-118A  20.0-118   20    0     118
AC 20-119     20.0-119   20.0-119   20    0     119
AC 23-2A      23.0-2A    23.0-2     23    0     2
AC 23-19A     23.0-19A   23.0-19    23    0     19
AC 23-27      23.0-27    23.0-27    23    0     27
AC 23.629-1B  23.629-1B  23.629-1   23    629   1
AC 23.1309-1D 23.1309-1D 23.1309-1  23    1309  1
AC 23.1419-2D 23.1419-2D 23.1419-2  23    1419  2
AC 25-11A     25.0-11A   25.0-11    25    0     11
AC 25-26      25.0-26    25.0-26    25    0     26
AC 25.795-1A  25.795-1A  25.795-1   25    795   1
AC 25.1529-1A 25.1529-1A 25.1529-1  25    1529  1
AC 33-9       33.0-9     33.0-9     33    0     9
AC 33-12B     33.0-12B   33.0-12    33    0     12
AC 33.83-1    33.83-1    33.83-1    33    83    1
AC 33.174-1A  33.174-1A  33.174-1   33    174   1
AC 121-37     121.0-37   121.0-37   121   0     37
AC 121-138    121.0-138  121.0-138  121   0     138
AC 135-13Q    135.0-13Q  135.0-13   135   0     13已选择22行。

Answer 2

首先再次感谢你的解答，不过我的要求是这样：
比如AC 135-13Q最后结果仍然是AC 135-13Q，而不能是AC 135.0-13Q;
其次，恕我愚昧，我本身不是搞数据库的，所以对数据库测试不了解，你能不能把执行的sql语句写给我(繁琐一点没关系)，因为我用到的只是sql语句，谢谢了~~

Answer 3

--第二列就是你想要的，我写全了只是为了方便你的理解。
--col 是我给补了一个.之后的结果
--col1是去掉字母之后的结果
--a1 是小数点之前的数字
--a2 是小数点和横线之间的数字
--a3 是横线之后的数字
--目的就是按照a1,a2,a3 to_number之后排序达到你想要的效果。不想显示按照下面的方式去掉就行了。
WITH T1 AS(
 SELECT 'AC' A,'23-2A' B FROM DUAL UNION ALL
 SELECT 'AC' A,'23-27' B FROM DUAL UNION ALL
 SELECT 'AC' A,'23.1309-1D' B FROM DUAL UNION ALL
 SELECT 'AC' A,'23-19A' B FROM DUAL UNION ALL
 SELECT 'AC' A,'23.1419-2D' B FROM DUAL UNION ALL
 SELECT 'AC' A,'23.629-1B' B FROM DUAL UNION ALL
 SELECT 'AC' A,'20-66B' B FROM DUAL UNION ALL
 SELECT 'AC' A,'20-42' B FROM DUAL UNION ALL
 SELECT 'AC' A,'20-24b' B FROM DUAL UNION ALL
 SELECT 'AC' A,'20-119' B FROM DUAL UNION ALL
 SELECT 'AC' A,'20-118A' B FROM DUAL UNION ALL
 SELECT 'AC' A,'25.1529-1A' B FROM DUAL UNION ALL
 SELECT 'AC' A,'25.795-1A' B FROM DUAL UNION ALL
 SELECT 'AC' A,'25-26' B FROM DUAL UNION ALL
 SELECT 'AC' A,'25-11A' B FROM DUAL UNION ALL
 SELECT 'AC' A,'33-9' B FROM DUAL UNION ALL
 SELECT 'AC' A,'33.83-1' B FROM DUAL UNION ALL
 SELECT 'AC' A,'33.174-1A' B FROM DUAL UNION ALL
 SELECT 'AC' A,'33-12B' B FROM DUAL UNION ALL
 SELECT 'AC' A,'121-138' B FROM DUAL UNION ALL
 SELECT 'AC' A,'121-37' B FROM DUAL UNION ALL
 SELECT 'AC' A,'135-13Q' B FROM DUAL
)SELECT A,
       B--,
       --COL,
       --COL1,
       --REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\1') A1,
       --REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\3') A2,
       --REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\5') A3
  FROM (SELECT A,
               B,
               COL,
               REGEXP_REPLACE(COL, '([^a-z|A-Z]+)([a-z|A-Z]+)', '\1') COL1
          FROM (SELECT A,
                       B,
                       CASE WHEN INSTR(B, '.') = 0 
                            THEN SUBSTR(B, 1, INSTR(B, '-', 1, 1) - 1) || '.0' || SUBSTR(B, INSTR(B, '-', 1, 1))
                         ELSE B END COL
                  FROM T1)) T3
 ORDER BY A, TO_NUMBER(REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\1')), 
             TO_NUMBER(REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\3')), 
             TO_NUMBER(REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\5'));

Answer 4

你的意思我明白了，但是如果写到一个sql语句中该如何写，例如
select num from xx where xx 
order by
......
麻烦你能指导一下，谢了~

Answer 5

--你的数据都是存在一个字段里的 【AC 】也是数据内容的一部分么，是的话截一下。
SELECT NUM
  FROM (SELECT NUM,
               COL,
               REGEXP_REPLACE(COL, '([^a-z|A-Z]+)([a-z|A-Z]+)', '\1') COL1
          FROM (SELECT NUM,
                       CASE WHEN INSTR(SUBSTR(NUM,3), '.') = 0 
                            THEN SUBSTR(SUBSTR(NUM,3), 1, INSTR(SUBSTR(NUM,3), '-', 1, 1) - 1) || '.0' || SUBSTR(SUBSTR(NUM,3), INSTR(SUBSTR(NUM,3), '-', 1, 1))
                         ELSE B END COL
                  FROM T1)) T3
 ORDER BY A, TO_NUMBER(REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\1')), 
             TO_NUMBER(REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\3')), 
             TO_NUMBER(REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\5'));

Answer 6

select * 
from 
  (
   select t2.*, substr(a,1,instr(a, '-')-1) b 
   from 
     (
      select t.*, substr(num,4) a 
      from test_str t
     ) t2
   )
order by to_number(b);不知道‘-’后面的是否也需要排序，不过总体思路是这样的

Answer 7

REGEXP_REPLACE(COL, '([^a-z|A-Z]+)([a-z|A-Z]+)', '\1')和
REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\1'）是正则表达式吗？有没有人能给解释下是什么意思啊？谢谢

Answer 8

【AC】是数据的一部分，例如AC 23-2A就是一条数据
不过其中我还是有很多地方不明白：
A、B、T1、T3、COL、COL1分别代表什么？我按照你给我的，在sql命令窗口中输入，但是有错误：
"faa"是表名，"description_1"是其中一列属性名
SQL> select num
  2  from( select num,
  3  col,
  4  REGEXP_REPLACE(COL, '([^a-z|A-Z]+)([a-z|A-Z]+)', '\1') COL1
  5  FROM (SELECT NUM,
  6  CASE WHEN INSTR(SUBSTR(NUM,3), '.')=0
  7  THEN SUBSTR(SUBSTR(NUM,3), 1, INSTR(SUBSTR(NUM,3), '-', 1, 1) - 1)|| '.0' || SUBSTR(SUBSTR(NUM,3), INSTR(SUBSTR(NUM,3), '-', 1, 1))
  8  ELSE B END COL
  9  FROM faa where description_1='咨询通告AC'))
 10  ORDER BY A, TO_NUMBER(REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\1')),
 11  TO_NUMBER(REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\3')),
 12  TO_NUMBER(REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\5'));
 
select num
from( select num,
col,
REGEXP_REPLACE(COL, '([^a-z|A-Z]+)([a-z|A-Z]+)', '\1') COL1
FROM (SELECT NUM,
CASE WHEN INSTR(SUBSTR(NUM,3), '.')=0
THEN SUBSTR(SUBSTR(NUM,3), 1, INSTR(SUBSTR(NUM,3), '-', 1, 1) - 1)|| '.0' || SUBSTR(SUBSTR(NUM,3), INSTR(SUBSTR(NUM,3), '-', 1, 1))
ELSE B END COL
FROM faa where description_1='咨询通告AC'))
ORDER BY A, TO_NUMBER(REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\1')),
TO_NUMBER(REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\3')),
TO_NUMBER(REGEXP_REPLACE(COL1, '(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)', '\5'))
 
ORA-00904: "B": 标识符无效我希望sql语句能直接执行排序功能，还请麻烦你帮下忙

Answer 9

ELSE B END COL
--改为,这个地方漏改了，具体的col 代表什么 col1 带表什么，看我3楼的解释。
ELSE SUBSTR(NUM,3) END COL

Answer 10

非常感谢你的帮助，问题已经解决了，最终sql语句我也写在下面了，结贴给分！SQL> select num from (
  2  select num,col,
  3  regexp_replace(col,'([^a-z|A-Z]+)([a-z|A-Z]+)','\1') col1
  4  from (
  5  select num,
  6  case when instr(substr(num,4),'.')=0 then substr(substr(num,4),1,instr(substr(num,4),'-',1,1)-1)||'.0'||substr(substr(num,4),instr(substr(num,4),'-',1,1)) else substr(num,4) end col
  7  from faa where description_1='咨询通报(AC)')
  8  )
  9  order by to_number(REGEXP_REPLACE(COL1,'(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)','\1')),
 10  to_number(REGEXP_REPLACE(COL1,'(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)','\3')),
 11  to_number(REGEXP_REPLACE(COL1,'(^[0-9]{2,})(.)([0-9]+)(-)([0-9]+)','\5'));