代码之家 › 专栏 › 技术社区 › Killer

字符串字段中的Order By number第一个

sql-order-by oracle sql

Killer · 技术社区 · 7 年前

我不知道该怎么设置 order by 在此查询中。我想先按数字排序,然后按字符排序。也许把每个字符改成十六进制会有用吗?我不知道。我点菜时会把结果记下来

COLA   COLB
----   ----
00     1
000A   2
000B   2
000C   2
000D   2
000F   2
000G   2
000H   2
000I   2
0000   2
0001   2
0002   2
0003   2
00030A 3
00030B 3
000300 3

我想得到的结果是:

COLA   COLB
----   ----
00     1
0000   2
0001   2
0002   2
0003   2
000A   2
000B   2
000C   2
000D   2
000F   2
000G   2
000H   2
000I   2
000300 3
00030A 3
00030B 3

这就是我的疑问:

with table1
as (
  select '00' COLA, 1 COLB from dual union
  select '000A' COLA, 2 COLB from dual union
  select '000B' COLA, 2 COLB from dual union
  select '000C' COLA, 2 COLB from dual union
  select '000D' COLA, 2 COLB from dual union
  select '000E' COLA, 2 COLB from dual union
  select '000F' COLA, 2 COLB from dual union
  select '000G' COLA, 2 COLB from dual union
  select '000H' COLA, 2 COLB from dual union
  select '000I' COLA, 2 COLB from dual union
  select '0000' COLA, 2 COLB from dual union
  select '0001' COLA, 2 COLB from dual union
  select '0002' COLA, 2 COLB from dual union
  select '0003' COLA, 2 COLB from dual union
  select '00030A' COLA, 3 COLB from dual union
  select '00030B' COLA, 3 COLB from dual union
  select '000300' COLA, 3 COLB from dual 
)
select * from table1 order by COLB, COLA

我已经尝试过使用LPAD,但没有成功。

4 回复 | 直到 7 年前

BriteSponge 7 年前

当我在数据库中运行您的示例时,我会根据您的列表得到“正确”的排序顺序。检查NLS\U排序设置;我的设置为二进制。

通过在会话中更改它来尝试它;

ALTER SESSION SET nls_sort='BINARY';

此参数有很多可能性,因此如果您想进行实验,可以在V$NLS\u VALID\u VALUES视图中找到这些可能性。

我还发现,您可以执行以下操作:;

SELECT * FROM table1 ORDER BY colb, NLSSORT(cola, 'NLS_SORT=BINARY')

Gordon Linoff 7 年前

一种方法是提取数字:

order by colb,
         cast(regexp_substr(cola, '[0-9]', 1, 1) as number),
         cola

S.Ali 7 年前

请试试这个

with table1
as (
 select '00' COLA, 1 COLB from dual union
select '000A' COLA, 2 COLB from dual union
select '000B' COLA, 2 COLB from dual union
select '000C' COLA, 2 COLB from dual union
select '000D' COLA, 2 COLB from dual union
select '000E' COLA, 2 COLB from dual union
select '000F' COLA, 2 COLB from dual union
select '000G' COLA, 2 COLB from dual union
select '000H' COLA, 2 COLB from dual union
select '000I' COLA, 2 COLB from dual union
select '0000' COLA, 2 COLB from dual union
select '0001' COLA, 2 COLB from dual union
select '0002' COLA, 2 COLB from dual union
select '0003' COLA, 2 COLB from dual union
select '00030A' COLA, 3 COLB from dual union
select '00030B' COLA, 3 COLB from dual union
select '000300' COLA, 3 COLB from dual 
)
select * from table1 order by COLB, to_number(regexp_substr(COLA, '^[[:digit:]]*')), COLA

Kevin Burton 7 年前

您可以使用TRANSLATE函数:

  with table1 as (
  select '00' COLA, 1 COLB from dual union
  select '000A' COLA, 2 COLB from dual union
  select '000B' COLA, 2 COLB from dual union
  select '000C' COLA, 2 COLB from dual union
  select '000D' COLA, 2 COLB from dual union
  select '000E' COLA, 2 COLB from dual union
  select '000F' COLA, 2 COLB from dual union
  select '000G' COLA, 2 COLB from dual union
  select '000H' COLA, 2 COLB from dual union
  select '000I' COLA, 2 COLB from dual union
  select '0000' COLA, 2 COLB from dual union
  select '0001' COLA, 2 COLB from dual union
  select '0002' COLA, 2 COLB from dual union
  select '0003' COLA, 2 COLB from dual union
  select '00030A' COLA, 3 COLB from dual union
  select '00030B' COLA, 3 COLB from dual union
  select '000300' COLA, 3 COLB from dual 
)
select * from table1 order by COLB, TRANSLATE(COLA,'0123456789','0123456789'),TRANSLATE(COLA,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ')