【oracle】oracle按照某字段指定顺序排序

喜欢ヅ旅行 2021-09-24 18:20 1349阅读 0赞

### 目录 ###

*  方案一、通过order by instr实现指定顺序排序
 *  方案二：通过order by decode
 *  补充：通过拼音、比划、部首排序方法
 *   *  使用拼音排序
     *  使用部首排序
     *  使用笔画数排序

--------------------

在做报表展现时，会遇到这样的需求，按照某个字段指定的顺序进行排序展示，比如按照面积段从大到小进行排序，这种需求如果直接使用order by 进行排序，对于字符串是按照字典顺序进行排序的，并不是我们想要的顺序排序，oracle提供了两种方法可以实现该需求：

先准备测试数据

drop table BR_DICT;
    create table br_dict(
    xl varchar2(32) not null,
    mjd varchar2(32),
    zb number(4,2)
    );
    
    insert into br_dict(xl, mjd, zb) values
    ('系列一', '≤60㎡', 2.05);
    insert into br_dict(xl, mjd, zb) values
    ('系列一', '60-90㎡', 7.04);
    insert into br_dict(xl, mjd, zb) values
    ('系列一', '90-120㎡', 36.92);
    insert into br_dict(xl, mjd, zb) values
    ('系列一', '120-144㎡', 39.82);
    insert into br_dict(xl, mjd, zb) values
    ('系列一', '144-180㎡', 9.41);
    insert into br_dict(xl, mjd, zb) values
    ('系列一', '180-220㎡', 2.6);
    insert into br_dict(xl, mjd, zb) values
    ('系列一', '>220㎡', 2.15);
    insert into br_dict(xl, mjd, zb) values
    ('列一', '>220㎡', 2.15);
    insert into br_dict(xl, mjd, zb) values
    ('一', '180-220㎡', 2.6);
    insert into br_dict(xl, mjd, zb) values
    ('二', '144-180㎡', 9.41);

# 方案一、通过order by instr实现指定顺序排序 #

select * 
      from br_dict t
     order by instr('≤60㎡,60-90㎡,90-120㎡,120-144㎡,144-180㎡,180-220㎡,>220㎡',mjd)
    ;
    
    result:
    系列一	≤60㎡	2.05
    系列一	60-90㎡	7.04
    系列一	90-120㎡	36.92
    系列一	120-144㎡	39.82
    二	144-180㎡	9.41
    系列一	144-180㎡	9.41
    一	180-220㎡	2.6
    系列一	180-220㎡	2.6
    系列一	>220㎡	2.15
    列一	>220㎡	2.15

注意：用order by instr时字段名称放在后面

# 方案二：通过order by decode #

select * 
      from br_dict t
     order by decode
     (mjd, 
     '≤60㎡', 1,
     '60-90㎡', 2,
     '90-120㎡', 3,
     '120-144㎡', 4,
     '144-180㎡', 5,
     '180-220㎡', 6,
     '>220㎡', 7) 
    ;
    
    result:
    系列一	≤60㎡	2.05
    系列一	60-90㎡	7.04
    系列一	90-120㎡	36.92
    系列一	120-144㎡	39.82
    二	144-180㎡	9.41
    系列一	144-180㎡	9.41
    一	180-220㎡	2.6
    系列一	180-220㎡	2.6
    系列一	>220㎡	2.15
    列一	>220㎡	2.15

注意：用order by instr时字段名称放在前面

# 补充：通过拼音、比划、部首排序方法 #

准备测试数据

drop table order_tb;
    CREATE TABLE order_tb(
    py VARCHAR2(32) NOT NULL,
    bh VARCHAR2(32),
    bs VARCHAR2(32)
    );
    
    insert into order_tb(py, bh, bs) values
    ('as', '一', '仁');
    insert into order_tb(py, bh, bs) values
    ('ab', '二', '们');
    insert into order_tb(py, bh, bs) values
    ('ab', '二', '们');
    insert into order_tb(py, bh, bs) values
    ('rw', '三', '情');
    insert into order_tb(py, bh, bs) values
    ('rw', '思', '思');
    insert into order_tb(py, bh, bs) values
    ('bd', '四', '情');
    insert into order_tb(py, bh, bs) values
    ('cd', '五', '据');
    insert into order_tb(py, bh, bs) values
    ('c', '六', '次');

## 使用拼音排序 ##

默认生序，可以添加desc关键字设置为降序排序

select * 
      from order_tb t
     order by nlssort(py,'NLS_SORT=SCHINESE_PINYIN_M') --desc
    ;
    
    result:
    ab	二	们
    ab	二	们
    as	一	仁
    as	一	仁
    bc	三	怡
    bc	三	怡
    bd	四	情
    bd	四	情
    c	六	次
    c	六	次
    cd	五	据
    cd	五	据
    rw	思	思
    rw	三	情

## 使用部首排序 ##

默认生序，可以添加desc关键字设置为降序排序

select * 
      from order_tb t
     order by nlssort(bs,'NLS_SORT=SCHINESE_RADICAL_M') --desc
    ;
    
    result:
    as	一	仁
    as	一	仁
    ab	二	们
    ab	二	们
    bc	三	怡
    bc	三	怡
    rw	思	思
    rw	三	情
    bd	四	情
    bd	四	情
    cd	五	据
    cd	五	据
    c	六	次
    c	六	次

## 使用笔画数排序 ##

默认生序，可以添加desc关键字设置为降序排序

select * 
      from order_tb t
     order by nlssort(bh,'NLS_SORT=SCHINESE_STROKE_M') --desc
    ;
    
    result:
    as	一	仁
    as	一	仁
    ab	二	们
    ab	二	们
    bc	三	怡
    bc	三	怡
    rw	三	情
    cd	五	据
    cd	五	据
    c	六	次
    c	六	次
    bd	四	情
    bd	四	情
    rw	思	思