常见Oracle SQL易出错点

最新推荐文章于 2021-04-12 00:34:39 发布

原创最新推荐文章于 2021-04-12 00:34:39 发布 · 2.5k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql #数据库 #TIPTOP ERP #ERP实施

Oracle SQL 同时被 2 个专栏收录

7 篇文章

订阅专栏

Oracle SQL & PL/SQL

7 篇文章

订阅专栏

本文总结了SQL编程中常见的陷阱，包括SUM函数返回NULL而非0、NULL值参与运算导致结果为NULL、NULL与空字符串的区别、错误地使用ROWNUM获取第一条记录以及NULL、空字符串和空格之间的比较差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

常见SQL陷阱

表栏位说明及数据demo如下：

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

1、SUM()求和：无资料时SUM()是NULL，而不是0

有资料时，某一笔被sum的数据为NULL值没有关系，依然可以正确sum

修正后的写法，注意两种nvl处理，nvl的位置不对也会导致结果为NULL

SELECT sum(nvl(base_salary,0)),  --×，结果依然为NULL
       nvl(sum(base_salary),0)   --√
  FROM t_salary              
  WHERE slip = '200812'

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

2、NULL值的参与的任何计算：计算结果后依然都是NULL

--将年月为201410 工号为1134807的绩效上调1000（原本是null）
UPDATE t_salary
   SET performance_salary = performance_salary + 1000
 WHERE slip = '201410'
   AND idcard = '1134807'
   
--上调绩效更新提交后再查询数据
SELECT * FROM t_salary
 WHERE slip = '201410'
   AND idcard = '1134807'

结果数据依然没更新上去：

应该这样写：用nvl处理空值

UPDATE t_salary
   SET performance_salary = nvl(performance_salary,0) + 1000
 WHERE slip = '201410'
   AND idcard = '1134807'

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

3、NULL值不等同空值' '

--①：结果为4
SELECT COUNT(*) FROM t_salary 
 WHERE idcard IN ('1234568','1156888','1134807') OR idcard = '';    --''，空
--② ：结果为5，t_salary表里面实际也是5条数据
SELECT COUNT(*) FROM t_salary 
 WHERE idcard IN ('1234568','1156888','1134807') OR idcard IS NULL --NULL

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

4、取第一条数据/前几条数据：你是否在用ORDER BY 和 rownum=1 / rownum<xx 来SQL

特别说明：上面提供的demo中有2条slip=201308的数据，且item分别为1、2，其中item=2的先插入数据库，再插入item=1的数据

--取201308年月里面ORDER BY slip ASC,item ASC的第一笔数据
--×，很多人经常如下写SQL，order by排序然后以为rownum=1就是取的第一条想要的数据
SELECT rowid,rownum,t_salary.* FROM t_salary
 WHERE slip='201308'
   AND rownum = 1
 ORDER BY slip ASC,item ASC

SQL应该如下这样写：

--先将查询排序做为1个结果集，再从这个子查询的结果集里面再取rownum=1的数据,取得到才是按排序后的第一条数据
SELECT * FROM 
  (SELECT t_salary.* FROM t_salary
    WHERE slip='201308'
   --AND rownum = 1
  ORDER BY slip ASC,item ASC
  )
 WHERE rownum = 1
 --ORDER BY slip ASC,item ASC

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

5、NULL、''、' ' 之间的区别

SELECT * FROM t_salary WHERE NULL = NULL;       --×，与NULL比较应该使用 is 或 is not
SELECT * FROM t_salary WHERE NULL is NULL;      --true
SELECT * FROM t_salary WHERE NULL is not NULL;  --false

SELECT * FROM t_salary WHERE ''  is NULL;       --true， ''中间没任何空格
SELECT * FROM t_salary WHERE ''  is not NULL;   --false，''中间没任何空格
SELECT * FROM t_salary WHERE ' ' is NULL;       --false，' '中间有一个空格
SELECT * FROM t_salary WHERE ' ' is not NULL;   --true， ' '中间有一个空格

http://blog.youkuaiyun.com/yihuiworld