SQL中的Null值处理
在日常的开发中,遇到需要处理 Null 值的场景还是蛮常见的。比如,查询某个字段包含 Null 值的记录、在展示的时候将 Null 值转为其它值、聚合包含 Null 值的列等。
今天就和大家聊聊在 MySQL 中处理 Null 值时需要注意的点,本文包含以下内容:
-
查找 Null 值 -
将 Null 值转为实际值 -
在排序中对 Null 值的处理 -
计算非 Null 值的数量 -
聚合 Null 列
1 查找 Null 值
查找 Null 值不能使用 “=”,而应该使用“is Null”。同理,查找非 Null 值也不能使用 “<>” ,而要使用 “is not Null”。这是初学者容易犯的错误。
比如,查询 emp 表中字段 comm 为 Null 的记录,就这么写 SQL:
SELECT
*
FROM
emp
WHERE comm IS NULL
有时候根据业务需要,我们要找出在 emp 表中没有提成的员工的信息。没有提成可以理解为提成为 0 或者本身就不包含提成,因此查询的条件就应该这么写 comm IS NULL OR comm = 0
。
类似的,在处理字符串类型的字段的时候,我们要找出某个字段没有值的记录。假设该字段叫作 xxx,xxx 允许设置 Null 值。初学者经常犯的错误就是给出的查询条件不完整,要么写成 xxx = ''
,要么写成 xxx is null
,正确的写法是 xxx = '' or xxx is null
,或者是其它变体。
2 将 Null 值转为实际值
有时候做报表展示的时候,我们不希望将 Null 值直接展示出来,而是转为其它值。比如,是数值类型的字段就展示成 0,是字符串类型就展示成空白字符。
使用函数 COALESCE()
可将 Null 值转成其它值,将 emp 表中 comm 列的 Null 值转成 0 就可以这么写:COALESCE(comm,0)
。在 MySQL 中还可以使用 IFNULL(comm,0)
,或者 case when
。
3 处理排序中的 Null 值
如果是使用默认的升序对包含有 Null 值的列做排序,有 Null 值的记录会排在前面,而使用了降序排序,包含了 Null 值的记录才会排在后面。
对于下面这条语句,它的排序结果在它下方。
SELECT
empno,
ename,
comm
FROM
emp
ORDER BY comm
排序结果:
empno ename comm
------ ------ ---------
7369 SMITH (NULL)
7566 JONES (NULL)
7698 BLAKE (NULL)
7782 CLARK (NULL)
7788 SCOTT (NULL)
7839 KING (NULL)
7876 ADAMS (NULL)
7900 JAMES (NULL)
7902 FORD (NULL)
7934 MILLER (NULL)
7844 TURNER 0.00
7499 ALLEN 300.00
7521 WARD 500.00
7654 MARTIN 1400.00
如果要想在升序排序中将有 Null 值的记录排在后面,可以将 Null 值转为一个最大值(比 comm 中的最大值还要大就行),或者增加一个排序列 is_null,记录有有 Null 值的时候 is_null = 1,其它时候为 0 。
SELECT
empno,
ename,
comm
FROM
emp
ORDER BY IF(comm IS NULL, 1, 0),
comm
重新排序后的结果如下:
empno ename comm
------ ------ ---------
7844 TURNER 0.00
7499 ALLEN 300.00
7521 WARD 500.00
7654 MARTIN 1400.00
7369 SMITH (NULL)
7566 JONES (NULL)
7698 BLAKE (NULL)
7782 CLARK (NULL)
7788 SCOTT (NULL)
7839 KING (NULL)
7876 ADAMS (NULL)
7900 JAMES (NULL)
7902 FORD (NULL)
7934 MILLER (NULL)
4 计算非 Null 值的数量
要统计 emp 表中 comm 字段非 Null 值的数量,可以就这么写:
SELECT
COUNT(comm)
FROM
emp
结果是 4。
count(comm)
-------------
4
注意,如果要统计一张表有多少记录时,不要在允许设置为 Null 值的列上做统计,得出来的结果和实际数据有偏差。注意对比一下使用 count(*)
和 count(comm)
的区别。
count(*)
----------
14
5 聚合 Null 列
聚合函数 sum()
、avg()
、min()
等会忽略 Null 值,在使用 avg()
时要警惕 Null 值。通常的做法是先将列中的 Null 值转为 0,再做聚合操作。
比如,要求员工的平均提成,正确的 SQL 应该这么写:
SELECT
AVG(COALESCE(comm, 0)) AS avg_comm
FROM
emp
聚合的结果:
avg_comm
------------
157.142857
如果直接使用 AVG(comm)
,得出来的结果就千差万别了。
avg(comm)
------------
550.000000
封面图由 DarkWorkX 在 Pixabay 上发布 。
本文分享自微信公众号 - SQL实现(gh_684ee9235a26)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
npm 依赖管理中被忽略的那些细节
👆 这是第 66 篇 不掺水的原创 ,想要了解更多 ,请戳上方蓝色字体: 政采云前端团队 关注我们吧~ 本文首发于政采云前端团队博客:npm 依赖管理中被忽略的那些细节 https://www.zoo.team/article/npm-details 前言 提起 npm,大家第一个想到的应该就是 npm install 了,但是 npm install 之后生成的 node_modules 大家有观察过吗?package-lock.json 文件的作用大家知道吗?除了 dependencies 和 devDependencies,其他的依赖有什么作用呢?接下来,本文将针对 npm 中的你可能忽略的细节和大家分享一些经验。 npm 安装机制 A 和 B 同时依赖 C,C 这个包会被安装在哪里呢?C 的版本相同和版本不同时安装会有什么差异呢?package.json 中包的前后顺序对于安装时有什么影响吗?这些问题平时大家可能没有注意过,今天我们就来一起研究一下吧。 A 和 B 同时依赖 C,这个包会被安装在哪里呢? 假如有 A 和 B 两个包,两个包都依赖 C 这个包,npm 2 会依次...
- 下一篇
完美解决方案-雪花算法ID到前端之后精度丢失问题
最近公司的一个项目组要把以前的单体应用进行为服务拆分,表的ID主键使用Mybatis plus默认 的雪花算法来生成。 快下班的时候,小伙伴跑过来找我,:“快给我看看这问题,卡这卡了小半天了!”。连拉带拽,连哄带骗的把我拉到他的电脑前面。这位小伙伴在我看来技术不算是大牛,但经验也很丰富了。他都卡了半天的问题,应该不是小问题,如果我一时半会搞不定,真的是耽误我下班了,所以我很不情愿的在他的位置坐了下来。 一、现象是这样的 下面我把异常的现象给大家描述一下,小伙伴建了一张表,表的主键是id BigINT,用来存储雪花算法生成的ID,嗯,这个没有问题! CREATE TABLE user ( id BIGINT(20) NOT NULL COMMENT '主键ID', #其他字段省略 ); 使用Long 类型对应数据库ID数据。嗯,也没有问题,雪花算法生成的就是一串数字,Long类型属于标准答案! [@Data](https://my.oschina.net/difrik) public class User { private Long id; //其他成员变量省略 在后端下断点。看到数据...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- CentOS6,7,8上安装Nginx,支持https2.0的开启
- CentOS7编译安装Gcc9.2.0,解决mysql等软件编译问题
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- Linux系统CentOS6、CentOS7手动修改IP地址
- Red5直播服务器,属于Java语言的直播服务器
- CentOS7,8上快速安装Gitea,搭建Git服务器
- CentOS8,CentOS7,CentOS6编译安装Redis5.0.7
- Windows10,CentOS7,CentOS8安装MongoDB4.0.16
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题