Oracle 按指定字符串顺序排序：一次真实的踩坑记录

一、需求看起来很简单

有一张表 sd140，里面有个厂区字段 sd140_factory_no，值类似：

A、B、C、D、E、F

业务同学提了个“很普通”的需求：

页面上的厂区列表，不要按字母排，要按我给的顺序 A,B,C,D,E,F 显示。

我第一反应：这还不简单？

二、我的第一版 SQL：DECODE

当时图快，直接写了：

SELECT sd140_factory_no
FROM sd140
ORDER BY DECODE(sd140_factory_no,
                'A', 1,
                'B', 2,
                'C', 3,
                'D', 4,
                'E', 5,
                'F', 6);

✅ 测试环境跑得很好，顺序完全正确，于是愉快地提交了。

坑，就是从“需求会变”开始的。

三、第一次踩坑：排序规则变了，SQL 要跟着改

上线没多久，业务说：

以后厂区顺序要改成 C,B,A,D,E,F，以后还可能经常调。

我一看：DECODE 是写死在 SQL 里的，每次改顺序都要：

改代码 / 改 XML 里的 SQL
重新发布
还要回归测试

😫 一个纯“顺序配置”的东西，竟然要改代码！

👉 教训一：排序规则属于配置，不该写死在 SQL 里。

四、第二次踩坑：INSTR 误匹配

我想，能不能直接用一个字符串搞定，别写映射？于是改成了：

SELECT sd140_factory_no
FROM sd140
ORDER BY INSTR('A,B,C,D,E,F', sd140_factory_no);

看着很优雅，确实也只用改一个字符串。

结果没多久，数据里多了一个厂区：AB。

问题来了：

INSTR('A,B,C,D,E,F', 'AB') = 1
INSTR('A,B,C,D,E,F', 'A')  = 1

AB 被当成 A，排在了一起，顺序全乱了。

👉 教训二：INSTR 是“子串查找”，不是“精确匹配”，多字符值千万别用。

补救办法是两边拼逗号：

ORDER BY INSTR(','||'A,B,C,D,E,F'||',', ','||sd140_factory_no||',')

能用，但不够优雅，而且每次都要这么写，容易漏。

五、第三次踩坑：大小写不匹配，查不到

业务又加了几个厂区：a1、b2，字符串顺序也改成了小写：

'a,b,c,d'

结果页面上这几个厂区“消失”了——其实是排不出来了。

原因是 Oracle 默认区分大小写：

'a' ≠ 'A'

INSTR 和 JOIN 全都匹配不上。

👉 教训三：Oracle 区分大小写，字符串顺序和字段值必须统一大小写，或用 UPPER()。

六、第四次踩坑：LEFT JOIN 没写，数据“莫名丢失”

最后我把顺序抽成了一张排序表：

CREATE TABLE sd140_factory_order (
    factory_no  VARCHAR2(20),
    sort_order  NUMBER
);

然后用 INNER JOIN 排序：

SELECT t.sd140_factory_no
FROM sd140 t
JOIN sd140_factory_order o
  ON t.sd140_factory_no = o.factory_no
ORDER BY o.sort_order;

结果测试同学跑来问：

怎么有些厂区不见了？

原因是：新增的厂区还没来得及维护进排序表，INNER JOIN 直接把它们过滤掉了。

改成 LEFT JOIN 就好了：

SELECT t.sd140_factory_no
FROM sd140 t
LEFT JOIN sd140_factory_order o
       ON t.sd140_factory_no = o.factory_no
ORDER BY
    NVL(o.sort_order, 999999),
    t.sd140_factory_no;

👉 教训四：排序表一定要 LEFT JOIN，没配置顺序的放到最后，而不是丢掉。

七、最终方案：一张排序表，彻底解决问题

1️⃣ 排序表

CREATE TABLE sd140_factory_order (
    factory_no  VARCHAR2(20),
    sort_order  NUMBER
);

2️⃣ 初始化顺序

INSERT INTO sd140_factory_order VALUES ('A', 1);
INSERT INTO sd140_factory_order VALUES ('B', 2);
INSERT INTO sd140_factory_order VALUES ('C', 3);
INSERT INTO sd140_factory_order VALUES ('D', 4);
INSERT INTO sd140_factory_order VALUES ('E', 5);
INSERT INTO sd140_factory_order VALUES ('F', 6);
COMMIT;

3️⃣ 最终 SQL

SELECT t.sd140_factory_no
FROM sd140 t
LEFT JOIN sd140_factory_order o
       ON t.sd140_factory_no = o.factory_no
ORDER BY NVL(o.sort_order, 999999);

✅ 改顺序：只改表数据

✅ 新厂区：自动排到最后

✅ 大小写：可用 UPPER() 统一

✅ SQL 永不改动

八、踩坑总结（血泪 4 条）

#	坑	原因	解决方案
1	顺序写死在 SQL	DECODE/CASE 是代码	抽到排序表
2	INSTR 误匹配	子串查找	精确匹配 / 排序表
3	大小写不匹配	Oracle 区分大小写	`UPPER()` 或统一大小写
4	数据“丢失”	INNER JOIN 过滤	一律 LEFT JOIN + NVL

九、一句话感悟

“看起来越简单的需求，越容易在生产上翻车。” 一个 ORDER BY，背后藏着配置、大小写、精确匹配和 NULL 处理四道题。把排序当成“数据”，而不是“代码”，问题就彻底解决了。