这一期是Oracle系列的第23期专门把基础查询类关键词做一个补充详解。你可能会觉得基础查询有什么好讲的不就是SELECT ... FROM ... WHERE ...吗但这两年在处理报表、排查生产问题、带新人写SQL的过程中我发现真正让人卡住的往往不是分析函数或者存储过程反而是这些基础关键词在特殊场景下的细节。比如一张表里有个字符串字段要过滤掉所有不能转成数字的行比如分页查询翻到后面几页突然发现数据对不上再比如COUNT(*)和COUNT(列名)统计出来的结果不一样。这些问题都属于基础查询的范畴但每一个都能让老手也愣一下。这篇文章就把这些关键词一次性讲透适合正在入门Oracle的人也适合写了好几年SQL但没系统整理过这些细节的人。1. 基础查询“基础”在哪坑就在哪1.1 基础查询不等于简单查询很多人对“基础查询”的理解就是一句SELECT * FROM 表名再复杂一点加个WHERE、GROUP BY、ORDER BY。这样理解不能说错但太“骨架化”了。真正写业务SQL的时候你还需要处理去重、空值、字符串判断、类型转换、行数限制这些关键词它们都属于基础查询的范畴却经常被文档放在很靠后的位置。举个例子运营要查“每个地区最近一个月下单客户的数量”你写出来的SQL可能同时用到WHERE过滤时间、GROUP BY按地区分组、COUNT(DISTINCT ...)去重统计。这三个关键词单独看都不难但组合在一起还要保证数据正确、性能不差这就不是“简单查询”能一笔带过的了。所以我一直觉得基础查询真正难的地方不在于语法而在于对数据形态的判断和对关键词细节的把握。数据里有没有空值字符串里有没有混进不可见的空格同一客户会不会重复下单这些问题都比SELECT本身更值得花时间。1.2 查询语句的逻辑执行顺序Oracle里一条查询语句的逻辑执行顺序和你想的通常不太一样。实际顺序是FROM确定数据来源WHERE逐行过滤GROUP BY分组HAVING过滤分组SELECT投影列、计算表达式ORDER BY排序FETCH行数限制这个顺序解释了三个经典现象第一WHERE子句里不能使用SELECT中定义的别名因为WHERE执行时SELECT还没跑第二ORDER BY里可以使用别名因为它排在SELECT之后第三在WHERE里直接写ROWNUM 10没问题但写ROWNUM 2永远查不到数据因为伪列的行号是在通过WHERE过滤后、SELECT阶段才生成的第一行不满足条件就不会产生行号后面自然也就没有“第2行”了。理解这个顺序很多“莫名其妙”的查询结果都能解释清楚。我在带人的时候经常让新人先把这个执行顺序背下来再开始写复杂SQL效果比直接教语法好得多。2. 高频实战过滤、判断与转换的组合拳2.1 过滤掉不可转为数字的字符串这个需求太常见了。接口表或者Excel导入的数据金额、数量字段经常被定义成VARCHAR2里面什么脏数据都有。你要把这些数据转成NUMBER做统计直接TO_NUMBER(amount_str)一定会报ORA-01722: invalid number整个查询都跑不了。正确的做法是先过滤、再转换。根据不同版本我常用的写法有这几种写法一正则匹配全版本通用SELECT TO_NUMBER(amount_str) FROM tmp_order WHERE REGEXP_LIKE(amount_str, ^[0-9]$);这个正则只匹配纯数字简单直观。但如果数据里有负数、小数、千分位、科学计数法就要把正则改复杂些-- 支持负号和小数 SELECT TO_NUMBER(amount_str) FROM tmp_order WHERE REGEXP_LIKE(amount_str, ^[-]?[0-9]*\.?[0-9]$);写法二VALDATE_CONVERSION12.2及以上从Oracle 12.2开始提供了VALIDATE_CONVERSION函数可以直接判断某个值能否转成指定类型返回1表示可以返回0表示不行SELECT TO_NUMBER(amount_str) FROM tmp_order WHERE VALIDATE_CONVERSION(amount_str AS NUMBER) 1;这种方式不用写正则对小数、负数、科学计数法的判断规则由数据库内部处理比较省心。唯一的限制是版本低于12.2用不了。写法三TRANSLATE剔除杂质老版本兜底如果既不能用VALIDATE_CONVERSION又嫌正则慢可以用TRANSLATE把数字字符替换掉剩下的部分为空就说明全是数字SELECT TO_NUMBER(amount_str) FROM tmp_order WHERE TRANSLATE(amount_str, 0123456789, ) IS NULL;注意这个写法同样只适合判断正整数遇到小数点、负号需要先在TRANSLATE的字符集里额外处理灵活性不如正则。这里有个细节容易被忽略字符串前后的空格。TO_NUMBER( 123 )是能成功的因为Oracle会自动去空格但TRANSLATE和REGEXP_LIKE不会帮你处理。所以实际生产环境里我通常先TRIM(amount_str)再判断避免“明明显示是数字正则却不通过”的诡异情况。2.2 判断字符串是否包含某个子串“字段里有没有包含某个关键词”也是高频需求。Oracle里至少有三种写法功能类似但细节不一样写法示例特点INSTRINSTR(name, 张) 0返回子串位置大于0表示包含性能通常最好LIKEname LIKE %张%直观支持ESCAPE转义通配符适合模糊匹配REGEXP_LIKEREGEXP_LIKE(name, 张)正则能力最强适合复杂模式但开销略大先说结论如果只是最简单的“包含某个固定字符串”我优先用INSTR。原因有两个一是写法上不容易被通配符干扰二是很多老代码里LIKE %xxx%写法因为前导通配符导致无法走普通索引而INSTR配合函数索引规划起来更可控。LIKE的场景主要在模糊匹配比如“名字以李开头”用LIKE 李%就非常合适这种情况下如果列上有普通索引是能走索引的。REGEXP_LIKE则用在对模式有要求的时候比如手机号格式、邮箱格式校验这类需求INSTR和LIKE做不了。还有一点经验之谈如果你要对某一列反复做包含判断而且数据量大别每次都全表扫。可以建个函数索引CREATE INDEX idx_order_name_instr ON user_order(INSTR(cust_name, 张));这样查询WHERE INSTR(cust_name, 张) 0时就能利用索引。2.3 TRUNC还是ROUND日期和数字都能用TRUNC是Oracle里最容易“只记得一半”的函数。大家普遍知道TRUNC(SYSDATE)能把时间截到当天零点但它的能力远不止这个具体取决于第二个参数。SELECT TRUNC(SYSDATE) FROM dual; -- 当天零点 SELECT TRUNC(SYSDATE, MM) FROM dual; -- 当月第一天 SELECT TRUNC(SYSDATE, YYYY) FROM dual; -- 当年第一天 SELECT TRUNC(SYSDATE, IW) FROM dual; -- 本周周一 SELECT TRUNC(123.456, 2) FROM dual; -- 123.45不做四舍五入 SELECT TRUNC(123.456, -1) FROM dual; -- 120往十位截断注意TRUNC对数字是直接截断不四舍五入。如果需要四舍五入用ROUNDSELECT ROUND(123.456, 2) FROM dual; -- 123.46业务里最典型的就是“查当天的数据”WHERE order_time TRUNC(SYSDATE) AND order_time TRUNC(SYSDATE) 1。为什么不用WHERE order_time TRUNC(SYSDATE)因为order_time是DATE类型时一般带时间直接等值匹配会把当天非零点下单的记录全部漏掉。区间写法能覆盖一整天还能走索引这是我强烈推荐的习惯。3. 分页、去重、空值三个绕不开的关键词3.1 Oracle分页的正确姿势分页可能是Oracle基础查询里被讨论最多的一个点因为不同版本写法差异很大网上抄错的情况比比皆是。12c之前的经典ROWNUM写法SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM user_order ORDER BY order_time DESC ) t WHERE ROWNUM 20 ) WHERE rn 10;为什么要套三层最内层先排序中间层生成行号并限制最大行数最外层再过滤起始行。顺序不能乱中间层如果直接写ROWNUM 10结果是空集原因前面讲过ROWNUM是根据过滤顺序逐行生成的第一行不满足条件就不会继续。12c及以后的FETCH写法SELECT * FROM user_order ORDER BY order_time DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;简洁很多而且语义清楚跳过10行取接下来的10行。另外还有个FETCH FIRST 10 ROWS WITH TIES取并列排名时非常好用。不过无论用哪种写法分页都必须配合ORDER BY而且排序字段要尽量唯一。如果ORDER BY order_time里大量数据是同一天的翻页时会出现同一行在上一页和下一页都出现的情况。我的习惯是在排序字段最后补一个主键ORDER BY order_time DESC, order_id DESC确保顺序稳定。深分页是另一个问题。OFFSET 100000 ROWS或者ROWNUM嵌套到第10万行以后数据库要先把前面的行全部读出来再丢弃性能会明显下降。对报表类场景我一般建议改成“基于上一页最后一条记录的键值”来取下一页也就是所谓的键集分页这属于进阶话题这里先留个印象。3.2 去重DISTINCT、GROUP BY、ROW_NUMBER()怎么选很多人一听到去重就写DISTINCT但实际场景里DISTINCT只是最基础的一种。从业务角度看去重有三类常见需求对应三种不同写法。第一类查询结果不要重复行用DISTINCTSELECT DISTINCT region, status FROM user_order;适合单纯的“有哪些组合”问题。要注意如果后面跟ORDER BY排序字段必须在SELECT列表里出现过否则会报ORA-01785。第二类按某个维度统计用GROUP BYSELECT region, COUNT(*) FROM user_order GROUP BY region;这里其实也包含了去重思想但根本目的是聚合不是展示明细。第三类按组取一条比如每个客户最新的一笔订单用ROW_NUMBER()SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY cust_name ORDER BY order_time DESC) rn FROM user_order t ) WHERE rn 1;这个场景用DISTINCT和GROUP BY都做不了因为你要的不是“去掉重复后的所有列”而是“每组里的某一行”。总结一下我的选择逻辑只要列不够用、排序不够用、需要组内明细就直接上ROW_NUMBER()窗口函数只是简单查看有哪些唯一组合值才用DISTINCT。这个习惯能省掉不少返工时间。3.3 空值处理与排序规则Oracle里NULL的坑十个里面至少有八个跟它有关。NVL、NVL2、COALESCE三个函数功能相近但适用场景略有差异函数语法行为注意事项NVLNVL(a, b)a为NULL则返回b否则返回a两个参数类型要一致b会被立即计算NVL2NVL2(a, b, c)a不为NULL返回b为NULL返回c适合需要区分“有没有值”的场景COALESCECOALESCE(a, b, c, ...)返回第一个非NULL值参数个数不限逐个判断通常是最稳的选择我日常写SQL优先用COALESCE因为它不容易出现类型不匹配的问题而且多个备用值的时候写法也更自然。NVL更多用在老代码阅读和简单场景里。排序时空值的处理同样容易翻车。Oracle默认升序时NULL排在最后降序时NULL排在最前很多人没意识到这一点导致排序结果和预期不一致。显式控制的方法SELECT cust_name, order_time FROM user_order ORDER BY order_time DESC NULLS LAST;还有一个关于计数的陷阱COUNT(*)会统计所有行COUNT(column)只统计该列非NULL的行AVG(column)也会忽略NULL行参与计算而不是把NULL当0。比如5行数据里有一行金额是NULLAVG(amount)算的是剩下4行的平均值如果你想让NULL当0参与平均就要显式写AVG(NVL(amount, 0))。这类问题在报表核对数字时很容易引发“怎么对不上”的排查事故。4. 实操把上面的关键词串进一个报表查询4.1 准备一张订单表和测试数据光讲不练没意思。我模拟一个实际的订单表把前面提到的关键词全部串进一个查询里。CREATE TABLE user_order ( order_id NUMBER PRIMARY KEY, cust_name VARCHAR2(50), region VARCHAR2(20), amount_str VARCHAR2(20), status VARCHAR2(10), order_time DATE ); INSERT INTO user_order VALUES (1, 张三, 华东, 100.50, OK, DATE 2024-11-02 10:30:00); INSERT INTO user_order VALUES (2, 李四, 华北, abc, OK, DATE 2024-11-05 14:20:00); INSERT INTO user_order VALUES (3, 张三, 华东, 200, OK, DATE 2024-12-01 09:00:00); INSERT INTO user_order VALUES (4, 王五, 华南, 50.8, OK, DATE 2024-12-10 16:45:00); INSERT INTO user_order VALUES (5, 李四, 华北, , OK, DATE 2024-12-15 11:10:00); INSERT INTO user_order VALUES (6, 赵六, 西南, 0.00, OK, DATE 2024-12-20 08:30:00); INSERT INTO user_order VALUES (7, 张三, 华东, 300.25, OK, DATE 2025-01-03 13:00:00); INSERT INTO user_order VALUES (8, 钱七, 东北, 88.88, OK, DATE 2025-01-06 20:15:00); INSERT INTO user_order VALUES (9, 王五, 华南, 12, CANCEL, DATE 2025-01-08 09:40:00); INSERT INTO user_order VALUES (10, 李四, 华北, 66.6, OK, DATE 2025-01-10 17:05:00); COMMIT;这个表故意留了几个坑amount_str字段里有纯字母、有空格、有小数还有一个订单状态是CANCEL后面统计时要过滤掉。这就是业务表最常见的真实状态。4.2 从需求到SQL的完整推导需求是这样的按月份统计2024年有效订单状态为OK的总金额金额字段必须是合法数字而且同一个客户只保留最新一笔订单。拆解一下这个需求其实涉及四件事过滤非法金额、过滤取消单、客户去重只取最新、月份分组汇总。先处理“过滤非法金额”和“状态过滤”这两步都在最内层完成SELECT order_id, cust_name, region, amount_str, order_time FROM user_order WHERE status OK AND REGEXP_LIKE(TRIM(amount_str), ^[0-9]\.?[0-9]*$);这里用TRIM处理了空格用正则处理了小数。接着处理“客户去重只取最新”用ROW_NUMBER()SELECT order_id, cust_name, amount_str, order_time, ROW_NUMBER() OVER (PARTITION BY cust_name ORDER BY order_time DESC) rn FROM user_order WHERE status OK AND REGEXP_LIKE(TRIM(amount_str), ^[0-9]\.?[0-9]*$);然后包一层只保留rn 1的行最后按月份分组汇总并补上TO_NUMBER转换SELECT TO_CHAR(order_time, YYYY-MM) AS month, COUNT(*) AS valid_order_cnt, SUM(TO_NUMBER(amount_str)) AS total_amount FROM ( SELECT order_id, cust_name, amount_str, order_time, ROW_NUMBER() OVER (PARTITION BY cust_name ORDER BY order_time DESC) rn FROM user_order WHERE status OK AND REGEXP_LIKE(TRIM(amount_str), ^[0-9]\.?[0-9]*$) ) WHERE rn 1 GROUP BY TO_CHAR(order_time, YYYY-MM) ORDER BY month;这个SQL把TRIM、REGEXP_LIKE、ROW_NUMBER、GROUP BY、TO_CHAR、TO_NUMBER全部串在了一起。执行结果非常干净2024年11月只剩张三一笔100.5012月有王五50.8、赵六0.00、李四因为金额是空格被过滤张三在12月的200被过滤因为只保留最新一笔2025年1月则统计到王五、张三、李四的有效订单。4.3 验证和扩展验证SQL对不对不能只看“有结果”要拿业务逻辑逐条核对。我通常的做法是先去掉GROUP BY和SUM单独跑内层明细肉眼核对“哪些行被保留了、哪些行被去掉了”确认无误后再加汇总层。这样排查起来快得多。这个SQL后续要扩展也方便。比如要加“按地区分组”就在内层多保留一个region列外层GROUP BY里加上它要把月份改成参数就换成绑定变量要限制返回条数就在最外层加FETCH FIRST 10 ROWS ONLY。基础关键词组合起来扩展性很强。5. 常见问题与排查技巧实录5.1 高频报错和解决办法速查表下面这些报错是我在实际工作中见到最多的每个都能对应到一个基础关键词的细节问题。报错出现场景原因与解决ORA-01722: invalid number字符串字段转数字数据里有脏字符先过滤再转换参考2.1ORA-00979: not a GROUP BY expression使用GROUP BY聚合SELECT列必须在GROUP BY中出现或者被聚合函数包裹ORA-01785: ORDER BY item must be the number of a SELECT-list expressionDISTINCT加ORDER BYORDER BY的列必须出现在SELECT列表中ORA-00918: column ambiguously defined多表关联查询同名列没有加表别名前缀逐列加别名即可ORA-01861: literal does not match format string字符串与日期比较隐式转换导致日期比较建议写成order_time TO_DATE(2024-01-01, YYYY-MM-DD)ORA-01427: single-row subquery returns more than one row子查询赋值子查询返回多行改成聚合或FETCH FIRST 1 ROWS ONLY这里重点说下ORA-01722。很多新手一看到这个报错就以为是数据有问题其实有时候是SQL写法引起的比如WHERE order_id 123隐式转换把字符串转数字时如果某一行数据异常就会报错。排查思路是定位到具体是哪一列转换失败可以用REGEXP_LIKE把不符合的行筛出来看一眼。5.2 基础查询的性能习惯基础查询虽然简单但性能陷阱一点不少。三个最常见的问题第一避免在索引列上用函数。WHERE TRUNC(order_time) DATE 2024-01-01会把普通索引废掉因为索引里存的是原始值而不是截断后的值。改成order_time DATE 2024-01-01 AND order_time DATE 2024-01-02就能走索引。这个习惯直接影响报表跑得快不快。第二注意隐式类型转换。VARCHAR2字段和数字比较时Oracle有时候会把列隐式转换为数字导致索引失效。解决方案是写SQL时显式转换或者干脆把存储类型改对。第三深分页的优化。前面提过的键集分页是常用办法把“翻到第N页”改成“从上一页最后一条记录继续往后取”能极大减少数据库扫描量。对动辄几十万上百万行的大表这种优化效果立竿见影。想验证SQL到底走没走对索引最简单的方法是EXPLAIN PLAN FOR SELECT * FROM user_order WHERE order_time DATE 2024-01-01; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);看到执行计划里出现TABLE ACCESS FULL就要警惕是不是条件写法有问题出现INDEX RANGE SCAN说明索引生效了。这个习惯值得所有人都养成别等到报表超时才想起看执行计划。写多了SQL之后你会发现基础查询的关键词就像工具箱里的螺丝刀每把都能用但用得对不对、顺手不顺手全看对细节的理解。我个人在实际操作中的体会是拿到一个查询需求先别急着写SQL先把原始数据扫一眼看看有没有空值、脏数据、重复行想清楚数据长什么样再动手写语句。这一步做好了后面几乎所有坑都能绕开。