sql:

create table users(username varchar(256));

insert into users values ('1112');
insert into users values ('126');
insert into users values ('124');
insert into users values ('cats');

select * from users order by username;

结果顺序:1112, 124, 126, cats
有没有办法让order子句按字母顺序排列,如果是数字,则为数字,即:124, 126, 1112,cats
我试过:
select * from users order by ('000'::varchar || username)
但那没用

最佳答案

select *
from users
order by
  case
    when username ~ '^[0-9]+$' then username::bigint
    else null
  end,
  username;

对于数据:
insert into users values ('1112');
insert into users values ('126');
insert into users values ('124');
insert into users values ('22');
insert into users values ('cats');

鉴于:
"22"
"124"
"126"
"1112"
"cats"

关于sql - 包含数字和文本的varchar字段的order子句,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/48788118/

10-09 20:12
查看更多