ORACLE SQL |在ORDER BY中修改数据 [英] ORACLE SQL | Modifying data in ORDER BY

问题描述

ORACLE表中的以下结构:

following structure in a ORACLE table:

FILE_NAME
-----------
12345_l.tif
12345_m.tif
12345_r.tif
12345_x.tif
12345_y.tif

需要以下结果:

First *_m*
Then *_l*
Then *_r*
Then * (everything else)

尝试:

SELECT FILE_NAME FROM TABLE
WHERE FILE_NAME LIKE '12345%'
ORDER BY regexp_replace(FILE_NAME, '_m', '_1'),
         regexp_replace(FILE_NAME, '_l', '_2'),
         regexp_replace(FILE_NAME, '_r', '_3')

但这给我一个错误的结果.

But this gives me a wrong result.

有人提示吗?

TIA马特

推荐答案

更改您的ORDER BY以按数字对其进行排序:

Change your ORDER BY to order it by a numeric:

 ORDER BY regexp_replace(FILE_NAME, '_m', 1),
          regexp_replace(FILE_NAME, '_l', 2),
          regexp_replace(FILE_NAME, '_r', 3);

例如

WITH t
  AS (SELECT '12345_l.tif' AS file_name FROM dual
      UNION
      SELECT '12345_m.tif' FROM dual
      UNION
      SELECT '12345_r.tif' FROM dual
      UNION
      SELECT '12345_x.tif' FROM dual
      UNION
      SELECT '12345_y.tif' FROM dual)
SELECT file_name
  FROM t
 ORDER BY regexp_replace(FILE_NAME, '_m', 1),
          regexp_replace(FILE_NAME, '_l', 2),
          regexp_replace(FILE_NAME, '_r', 3);

赠予:

==============
12345_m.tif
12345_l.tif
12345_r.tif
12345_x.tif
12345_y.tif

希望有帮助...

或者，您可以使用:

 ORDER BY (CASE SUBSTR(file_name, INSTR(file_name, '_')+1, 1)
             WHEN 'm' THEN 1
             WHEN 'l' THEN 2
             WHEN 'r' THEN 3
             ELSE 4
            END) ASC;

E.G.:

WITH t
  AS (SELECT '12345_l.tif' AS file_name FROM dual
      UNION
      SELECT '12345_y.tif' FROM dual
      UNION
      SELECT '12345_r.tif' FROM dual
      UNION
      SELECT '12345_x.tif' FROM dual
      UNION
      SELECT '12345_m.tif' FROM dual)
SELECT file_name
  FROM t
 ORDER BY (CASE SUBSTR(file_name, INSTR(file_name, '_')+1, 1)
             WHEN 'm' THEN 1
             WHEN 'l' THEN 2
             WHEN 'r' THEN 3
             ELSE 4
            END) ASC;

赠予:

12345_m.tif
12345_l.tif
12345_r.tif
12345_x.tif
12345_y.tif

这篇关于ORACLE SQL |在ORDER BY中修改数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！