Oracle对空值(NULL)的 聚合函数 排序

Oracle对空值(NULL)的 聚合函数 排序本文介绍了 Oracle 数据库中处理 NULL 值的五种方法 NVL NVL2 COALESCE CASE 表达式以及 NULLIF 函数的应用 重点讲解了如何在聚合函数如 SUM AVG 等中处理 NULL 值 以确保数据查询的准确性

大家好,欢迎来到IT知识分享网。

除count之外sum、avg、max、min都为null,count为0

Null 不支持加减乘除,大小比较,相等比较,否则只能为空;只能用‘is [not] null’来进行判断;
Max等聚合函数会自动“过滤null” 
null排序默认最大:
4.处理:

 1)NVL(expr1,expr2):

当两个参数数据类型不同时,oracle会将两个参数进行隐式转换,如果不能隐式转换刚 会报错,隐式转换规则如下:

         1.如果参数1为字符型,则把参数2转换为参数1的类型,返回值为 VARCHAR2

         2.如果参数1为数值型,则判断两个参数的最高数值优先级(如双精实数比单 精实 数优先级高),然后转换成高优先级的数值,返回该类型的值.

2)  NVL2(expr1,expr2, expr3):只看expr2类型

3) COALESCE ( expression,value1,value2……,valuen) :数据类型要一致

将会返回包括expression在内的所有参数中的第一个非空表达式

如果expression不为空值则返回expression;否则判断value1是否是空值,

如果value1不为空值则返回value1;否则判断value2是否是空值,

那么这里我们可以得出结论:在使用AVG、SUM、MIN、MAX、COUNT聚合函数时,当时用一个列名计算的时候空值会被自动去掉,特别的,当使用COUNT函数使用常量例如(*或1)时,空值不会被自动去掉。

Oracle对空值(NULL)的5种处理

Oracle 中 null 和 ” (空字符串)是一样的
所以用 nvl(field_eng,'') = '' 来判断 field_eng 字段的值是否为 '' 和 null 是不行的
直接用 field_eng is null 即可


数据库中的空值(NULL)经常会导致一些不可预知的错误,我们需要使用数据库提供的方法对空值进行处理,确保数据查询的准确性。

如下表所示,红色标记的值为空值。

Oracle对空值(NULL)的 聚合函数 排序

分别使用5种方法对数据库的空值进行判断和处理:

1.使用COALESCE函数

COALESCE(expr1,expr2,expr3,…)函数接受一个输入参数的列表,返回第1个非空的参数。如果所有的参数都为空,则返回空值。

案例:

假如我们现在需要查找员工的联系电话,查找的规则如下:先找移动电话;如果没有移动电话,就找工作电话;如果没有工作电话,就找家庭电话;如果没有家庭电话,就找紧急联系人电话;如果以上电话都没有找到,则返回“N/A”。

Oracle对空值(NULL)的 聚合函数 排序

实现SQL:


SELECT t.emp_id,
COALESCE(t.mobile_phone,t.work_phone,t.home_phone,t.emergency_phone,'N/A') AS phone
FROM emp_contact t;

执行结果:

Oracle对空值(NULL)的 聚合函数 排序

2.使用NULLIF函数

NULLIF(expr1,expr2)函数接受两个输入参数,如果第1个参数等于第2个参数,返回空值;否则,返回第1个参数的值。

实例SQL:


SELECT NULLIF(1,2),NULLIF(2,2)
FROM DUAL;

执行结果:

Oracle对空值(NULL)的 聚合函数 排序

1和2不相等,故返回第一个参数值1。

2和2相等,故返回空。

NULLIF函数的一个常见用途是防止除零错误,例如:

Oracle对空值(NULL)的 聚合函数 排序

通过NULLIF函数处理后就不会报错了:

Oracle对空值(NULL)的 聚合函数 排序

3.使用CASE表达式

案例:

查询部门ID为2的部门员工全年收入(包括工资和奖金)

实现SQL:


SELECT t.emp_name,
t.salary AS "工资",
t.bonus AS "奖金",
CASE WHEN t.bonus IS NULL THEN t.salary*12
ELSE t.salary*12 + t.bonus
END AS "全年收入"
FROM employee t WHERE t.dept_id=2;

执行结果:

Oracle对空值(NULL)的 聚合函数 排序

4.使用NVL(expr1,expr2)函数

NVL(expr1,expr2)函数返回第1个非空的参数值,等价于只有两个参数的COALESCE函数。

案例:

查询部门ID为2的部门员工全年收入(包括工资和奖金)

在不使用NVL函数对空值进行处理的情况下,会查询出错误的结果:

Oracle对空值(NULL)的 聚合函数 排序

使用NVL函数对奖金的值进行处理

实现SQL:


SELECT t.emp_name,
t.salary AS "工资",t.bonus AS "奖金",
t.salary*12 + NVL(t.bonus,0) AS "全年收入"
FROM employee t WHERE t.dept_id=2;

执行结果:

Oracle对空值(NULL)的 聚合函数 排序

5.使用NVL2(expr1,expr2,expr3)函数

NVL2(expr1,expr2,expr3)函数包含3个参数,如果第1个参数不为空,返回第2个参数的值;否则,返回第3个参数的值。

案例:

查询部门ID为2的部门员工全年收入(包括工资和奖金)

实现SQL:


SELECT t.emp_name,
t.salary AS "工资",t.bonus AS "奖金",
NVL2(t.bonus,t.salary*12 + t.bonus,t.salary*12 ) AS "全年收入"
FROM employee t WHERE t.dept_id=2;

执行结果:

Oracle对空值(NULL)的 聚合函数 排序

免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://yundeesoft.com/158156.html

(0)
上一篇 2025-01-25 21:05
下一篇 2025-01-25 21:10

相关推荐

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

关注微信