甲骨文 - 首先按字母排序然后数字

发布于 2025-02-13 18:53:46 字数 252 浏览 0 评论 0原文

我想按下面介绍的顺序对以下数据项进行排序（然后数字）：

AA

AB

AC

..

00

01

02

..

99

但是，我的查询 - 基于ASCII代码的xxxxx ASC的订单 - 它首先检索所有数字，然后用如图所示的字母检索：

00

01

..

99

AA

AB

..

ZZ

任何技巧以使其更适当地进行排序？

谢谢！

原文

I want to sort the following data items in the order they are presented below
( letters then numbers ) :

AA

AB

AC

..

00

01

02

..

99

However, my query - using Order by xxxxx ASC - based on ASCII code - which retrieves all numbers first and then letters as shown :

00

01

..

99

AA

AB

..

ZZ

Any tricks to make it sort more appropriately?

THANKS!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我一向站在原地 2025-02-20 18:53:46

一种做到这一点的方法是，在情况下使用情况和使用常规订购的情况下使用案例对项目的订购优先级。这样的优先级将在字母内进行，然后是数字，然后在相应类别中订购

：

select a.*
  from t a
order by case when translate(x,'0123456789','         ')<>x then 1 else 0 end
         ,x  

+--------+
| output |
+--------+
| AA     |
| AB     |
| AC     |
| AZ     |
| 00     |
| 01     |
| 02     |
| 4      |
+--------+

https://dbfiddle.uk/?rdbms=oracle_11.2&； fiddle = dff7E6805F1B59501D59CB91194CF1A0

One way to do this would be to prioritize the ordering of items by alphabets using case when condition and after that to use the regular ordering. This way the priority sort would be on alphabets then numbers, followed by ordering within the respective categories

Eg:

select a.*
  from t a
order by case when translate(x,'0123456789','         ')<>x then 1 else 0 end
         ,x  

+--------+
| output |
+--------+
| AA     |
| AB     |
| AC     |
| AZ     |
| 00     |
| 01     |
| 02     |
| 4      |
+--------+

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=dff7e6805f1b59501d59cb91194cf1a0

回复收藏 0 原文

小姐丶请自重 2025-02-20 18:53:46

另一个选项是使用正则表达式，以便先排序数字，然后将其余的（“休息”是字母）：

SQL> with test (col) as
  2    (select '00' from dual union all
  3     select 'AB' from dual union all
  4     select 'AC' from dual union all
  5     select '01' from dual union all
  6     select '99' from dual union all
  7     select 'ZZ' from dual
  8    )
  9  select col
 10  from test
 11  order by regexp_substr(col, '\d'), col
 12  /

CO
--
00
01
99
AB
AC
ZZ

6 rows selected.

SQL>

对于相对较小的数据集，这是可以的，但是 - 正则表达式似乎有些slow> slow 当我们必须使用大型数据集时，请查看是否有帮助。

Another option is to use regular expressions so that digits are sorted first, then the rest (and that "rest" will be letters):

SQL> with test (col) as
  2    (select '00' from dual union all
  3     select 'AB' from dual union all
  4     select 'AC' from dual union all
  5     select '01' from dual union all
  6     select '99' from dual union all
  7     select 'ZZ' from dual
  8    )
  9  select col
 10  from test
 11  order by regexp_substr(col, '\d'), col
 12  /

CO
--
00
01
99
AB
AC
ZZ

6 rows selected.

SQL>

That's OK for relatively small data sets, but - regular expressions seem to be somewhat slow when we have to work with large data sets so - see if it helps.

回复收藏 0 原文

岛徒 2025-02-20 18:53:46

您也可以使用正则表达式实现“ \ d”匹配任何非数字的情况。

    with t(x) as(select 'AZ' from dual union all
      select 'AA' from dual union all
      select 'AB' from dual union all
      select 'AC' from dual union all
      select '00' from dual union all
      select '01' from dual union all
      select '02' from dual union all
      select '4' from dual)

    select x
       from t
      order by (regexp_substr(x, '\D')), x

You can also use a regular expression to achieve the same where '\D' will match any non-digit.

    with t(x) as(select 'AZ' from dual union all
      select 'AA' from dual union all
      select 'AB' from dual union all
      select 'AC' from dual union all
      select '00' from dual union all
      select '01' from dual union all
      select '02' from dual union all
      select '4' from dual)

    select x
       from t
      order by (regexp_substr(x, '\D')), x

回复收藏 0 原文

~没有更多了~