Oracle数据库查询里那些你可能没注意但挺管用的小窍门分享

说到Oracle数据库查询，大家肯定都用过SELECT、WHERE、JOIN这些基础得不能再基础的东西，但就像你有一个功能强大的智能手机，却只用来打电话发短信一样，Oracle里面其实藏着不少“骚操作”，能让你干活儿更省力，查询跑得更快，这些东西可能官方手册里都有，但没人提醒的话，自己很难注意到，今天我就分享几个我觉得特别管用的小窍门，它们都不需要你是什么DBA专家,普通的开发人员或者数据分析师也能马上用起来。

第一个小窍门：用WITH子句把复杂的查询拆解开，让它变得像搭积木一样简单。

这个技巧的来源是Oracle官方文档中关于子查询因子的部分，你有没有写过那种超级长的查询？里面层层嵌套，光看缩进就能让你眼花缭乱，这时候，WITH子句（也叫公用表表达式，CTE）就是你的救星，它的妙处在于，你可以先把一块复杂的查询逻辑定义成一个临时的“虚拟表”，给它起个简单的名字，比如叫“临时销售数据”，在主查询里，你就可以像引用一张真正的表一样，反复使用这个“临时销售数据”,而不用把那段复杂的代码复制粘贴好几遍。

举个例子，你可能需要先计算每个部门上个月的平均工资，然后再找出高于本部门平均工资的员工，传统写法可能要写两个子查询，用WITH子句，你可以先写：WITH 部门平均工资 AS (SELECT 部门ID, AVG(工资) 平均工资 FROM 员工表 WHERE 月份 = '上个月' GROUP BY 部门ID)，你看，这就定义好了一个积木块，然后主查询就特别清爽：SELECT * FROM 员工表 e JOIN 部门平均工资 a ON e.部门ID = a.部门ID WHERE e.工资 > a.平均工资，这样做不仅代码看起来清晰，容易维护，而且Oracle有时候还能更好地优化这种写法,说不定能跑得更快。

第二个小窍门：让CASE语句不再只是简单的条件判断，变身成为数据整形利器。

我们都知道CASE WHEN THEN ELSE END这个语法，通常用来给数据打标签，当工资大于一万标记为‘高’，否则标记为‘普通’”，但它的潜力远不止于此，一个很实用的场景是配合聚合函数，实现按条件计数或求和,这在实际业务中非常常见。

领导让你统计一个报表：一列是员工总数，一列是男性员工数，一列是女性员工数，笨办法是写三个不同的SELECT COUNT()语句，然后用UNION ALL拼起来，或者关联三次表，但用CASE语句，一行查询就能搞定：`SELECT COUNT() AS 总人数, SUM(CASE WHEN 性别 = '男' THEN 1 ELSE 0 END) AS 男性人数, SUM(CASE WHEN 性别 = '女' THEN 1 ELSE 0 END) AS 女性人数 FROM 员工表`，它的原理是，CASE语句会为每条记录生成一个值（男性记录生成1，女性记录生成0），然后SUM函数再把所有这些1和0加起来，自然就得到了人数，这种方法非常灵活，你可以轻松地统计不同状态、不同类型的数据,而不用把查询写得特别复杂。

第三个小窍门：用ROWNUM和排序轻松搞定“取前N名”或者“分页”查询。

这个技巧来源于Oracle对查询结果行号的处理机制，ROWNUM是一个神奇的伪列，它会给查询出来的每一行按顺序分配一个从1开始的号码，但这里有个坑，新手很容易掉进去：ROWNUM是在数据被筛选和排序之前就分配好的，如果你直接写SELECT * FROM 表 WHERE ROWNUM <= 10 ORDER BY 工资 DESC，你得到的可能不是工资最高的10个人，而是最先被数据库读到的10条记录,然后再对这10条记录进行排序。

正确的做法是，先把排序搞定，然后再从排序好的结果里取前N条，这就需要用到子查询：SELECT * FROM (SELECT * FROM 员工表 ORDER BY 工资 DESC) WHERE ROWNUM <= 10，这样，最里面的子查询负责把所有员工按工资从高到低排好队，外面的查询再从这个排好队的“临时结果”中取前10行，这就一定是真正的“工资最高的十大土豪”了，这个方法也是实现简单分页的基础，比如你想看第11到第20条记录，可以写成：SELECT * FROM (SELECT 临时表.*, ROWNUM AS 行号 FROM (SELECT * FROM 员工表 ORDER BY 工号) 临时表 WHERE ROWNUM <= 20) WHERE 行号 >= 11，虽然12c以后有更标准的OFFSET-FETCH语法，但在很多老系统里,你还是会经常看到这种经典的ROWNUM分页写法。

第四个小窍门：用LISTAGG函数把多行数据“捏”成一行，告别繁琐的字符串拼接。

这个功能是Oracle 11g R2版本引入的，在官方文档的聚合函数部分有详细说明，我们经常遇到这种需求：需要把同一个分组下的多个值，用逗号或者其他分隔符连接起来，变成一个大字符串，查询每个部门，并且把这个部门所有员工的名字列出来,用顿号隔开。

在没有LISTAGG之前，你可能需要写很复杂的PL/SQL循环或者用XML函数绕弯子，现在简单多了，一句查询就能解决：SELECT 部门ID, LISTAGG(员工姓名, '、') WITHIN GROUP (ORDER BY 员工ID) AS 部门成员 FROM 员工表 GROUP BY 部门ID，这样查询结果里，每一行代表一个部门，后面一列就是这个部门所有员工姓名拼接好的字符串，看起来非常直观，这对于生成报表、数据导出或者快速查看关联信息特别有帮助。

第五个小窍门：用NVL、COALESCE或NULLIF这些函数优雅地处理烦人的空值。

数据里的NULL值（空值）经常是导致查询结果出错的“罪魁祸首”，在计算平均值或者进行字符串连接时，如果碰到NULL，整个结果可能就变成NULL了，Oracle提供了一些小函数来专门对付它们，NVL(字段名, 默认值)是最简单的，如果字段是NULL，它就返回你指定的默认值，否则返回字段本身的值,这能保证你的计算永远不会因为NULL而中断。

COALESCE(值1, 值2, 值3, ...)则更聪明一点，它会从左到右检查，返回第一个不是NULL的值，这在你需要从多个可能为空的字段中选一个有效值时特别方便，而NULLIF(值1, 值2)则相反，如果值1等于值2，它就返回NULL，否则返回值1，这个可能用得少一点，但在一些数据清洗场景下很有用，比如可以把一些无意义的默认值（如‘N/A’）统一转换成NULL。

这些小函数就像是你工具箱里的螺丝刀、小钳子，看起来不起眼，但在处理数据“毛刺”时，能让你事半功倍,避免很多意想不到的错误。

就是一些在Oracle日常查询中容易被忽略但又相当实用的技巧，它们的目的都是为了让你写出的SQL代码更清晰、更高效、更健壮，下次当你在写一个感觉有点别扭、有点复杂的查询时，不妨想想,是不是能用上这些小窍门来优化一下。