DECODE函数的奇怪用法的例子

背景：你们公司超级注重企业文化，要求大家要做好孩子多读书，公司老板叫王富贵，老板娘叫张翠花，另有员工若干人。

需求：领导要求搞一个员工读书记录排名，展示出每个员工所读过的书都有啥？

分析：从这样及其常见的一句话需求描述中，聪明的小趴菜你肯定知道，老板肯定要排在最前面，然后应该是老板娘，剩余的普通员工按照姓名首字母排等等这些隐藏的需求。

方案：

首先给出数据建表脚本（Oracle）

CREATE TABLE T_BOOK_RECORD (
    ID VARCHAR2(100) NOT NULL,
    USER_NAME VARCHAR2(100) NOT NULL,
    BOOK_NAME VARCHAR2(100) NOT NULL,
    CONSTRAINT T_BOOK_RECORD_PK PRIMARY KEY (ID)
);
COMMENT ON TABLE T_BOOK_RECORD IS '阅读记录';
COMMENT ON COLUMN T_BOOK_RECORD.ID IS '主键';
COMMENT ON COLUMN T_BOOK_RECORD.USER_NAME IS '人员姓名';
COMMENT ON COLUMN T_BOOK_RECORD.BOOK_NAME IS '书籍名称';

然后给出灵魂数据脚本

INSERT INTO T_BOOK_RECORD (ID, USER_NAME, BOOK_NAME) VALUES('1', '王富贵', '《如何合法罚款》');
INSERT INTO T_BOOK_RECORD (ID, USER_NAME, BOOK_NAME) VALUES('2', '李加成', '《舔狗的自我修养》');
INSERT INTO T_BOOK_RECORD (ID, USER_NAME, BOOK_NAME) VALUES('3', '孙正易', '《囚徒健身插图版》');
INSERT INTO T_BOOK_RECORD (ID, USER_NAME, BOOK_NAME) VALUES('4', '王丝匆', '《穷爸爸富爸爸》');
INSERT INTO T_BOOK_RECORD (ID, USER_NAME, BOOK_NAME) VALUES('5', '赵伪', '《资本游戏》');
INSERT INTO T_BOOK_RECORD (ID, USER_NAME, BOOK_NAME) VALUES('6', '王富贵', '《人家大爱-996福报》');
INSERT INTO T_BOOK_RECORD (ID, USER_NAME, BOOK_NAME) VALUES('7', '张翠花', '《拴住老公的胃》');
INSERT INTO T_BOOK_RECORD (ID, USER_NAME, BOOK_NAME) VALUES('8', '张翠花', '《老板娘不是老板的娘》');
INSERT INTO T_BOOK_RECORD (ID, USER_NAME, BOOK_NAME) VALUES('9', '李加成', '《老子明天不上班》');
INSERT INTO T_BOOK_RECORD (ID, USER_NAME, BOOK_NAME) VALUES('10', '王丝匆', '《投胎那些事》');

你最开始可能想到的方法

• 首先，按照姓名排序查出所有数据

SELECT USER_NAME ,BOOK_NAME FROM T_BOOK_RECORD ORDER BY NLSSORT(USER_NAME,'NLS_SORT = SCHINESE_PINYIN_M')

得到结果如下：

 

 

• 然后，通过代码筛选出老板王富贵和老板娘张翠花的数据依次塞到一个集合里

public List<BookRecord> list(){
    List<BookRecord> boss = new ArrayList<>();
    List<BookRecord> bossWife = new ArrayList<>();
    List<BookRecord> nobody = new ArrayList<>();
    for(BookRecord br : list){
        if ("王富贵".equals(br.getUserName())){
            boss.add(br);
        }else if ("张翠花".equals(br.getUserName())){
            bossWife.add(br);
        }else {
            nobody.add(br);
        }
    }
    boss.addAll(bossWife);
    boss.addAll(nobody);
    
    return boss;
}

其实也可以一个并不复杂的SQL直接实现

SELECT * FROM T_BOOK_RECORD ORDER BY DECODE(USER_NAME,'王富贵',1,'张翠花',2) ,NLSSORT(USER_NAME,'NLS_SORT = SCHINESE_PINYIN_M')

得到结果如下：

 

 

这里主要有以下几个知识点：

• 字段按照拼音字母排序

SELECT * FROM T_BOOK_RECORD ORDER BY NLSSORT(USER_NAME,'NLS_SORT = SCHINESE_PINYIN_M')

• 动态处理函数decode()，这个有点类似三目运算表达式

DECODE(USER_NAME,'王富贵',1,'张翠花',2)，这个代表，如果USER_NAME='王富贵'，函数返回1，如果USER_NAME='张翠花'，函数返回2，这个函数的返回值是优先级最高的排序条件