龙空技术网

MySQL分组统计你会吗?sum,if会用吗?

程序员实用技能 609

前言:

当前看官们对“mysqlsumdouble”大约比较重视,我们都想要分析一些“mysqlsumdouble”的相关文章。那么小编同时在网上网罗了一些关于“mysqlsumdouble””的相关内容,希望我们能喜欢,同学们快快来了解一下吧!

分组统计在日常工作中经常用到,今天分享使用sum,if进行分组统计。

IF表达式:

IF(expr1,expr2,expr3)

如果 expr1 是TRUE,则返回值是expr2; 否则返回值是expr3。

查询每个部门男生、女生分别多少人?

selectdept_id,count(*) as total_num,sum(if(sex=1,1,0)) as female_num,sum(if(sex=0,1,0)) as male_num,sum(if((sex<>1 and sex<>0) or sex is null,1,0)) as unknown_numfrom staff group by dept_id;+---------+-----------+------------+----------+-------------+| dept_id | total_num | female_num | male_num | unknown_num |+---------+-----------+------------+----------+-------------+| 101     |         5 |          2 |        2 |           1 || 102     |         3 |          1 |        1 |           1 || 103     |         1 |          1 |        0 |           0 || 104     |         1 |          0 |        1 |           0 |+---------+-----------+------------+----------+-------------+4 rows in set (0.00 sec)

表结构如下:

CREATE TABLE `staff` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',  `name` varchar(50) NOT NULL COMMENT '姓名',  `dept_id` varchar(10) DEFAULT NULL COMMENT '部门id',  `salary` double DEFAULT NULL COMMENT '工资',  `sex` int(11) DEFAULT NULL COMMENT '性别,1是男生,0是女生',  `created_time` datetime DEFAULT NULL COMMENT '创建时间',  `updated_time` datetime DEFAULT NULL COMMENT '更新时间',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='职员表'

现有记录:

mysql>  select * from staff ;+----+-------+---------+--------+------+---------------------+---------------------+| id | name  | dept_id | salary | sex  | created_time        | updated_time        |+----+-------+---------+--------+------+---------------------+---------------------+|  1 | Tom   | 101     |   2500 |    1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 ||  2 | Tonny | 101     |   3500 |    1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 ||  3 | Bob   | 101     |   3500 |    0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 ||  4 | Rob   | 101     |   4500 | NULL | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 ||  5 | Sudey | 101     |   5500 |    0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 ||  6 | Sunny | 102     |   5500 |    1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 ||  7 | Sedey | 102     |   3500 |    0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 ||  8 | Bobby | 102     |   4500 | NULL | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 ||  9 | Weedy | 103     |   4500 |    1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 || 10 | Jimme | 104     |   5500 |    0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |+----+-------+---------+--------+------+---------------------+---------------------+10 rows in set (0.00 sec)

当然也可以使用case when实现,详见:

MySQL分组统计你会吗?case when,sum,count会用吗?

MySQL,case when你真的会用吗?附避坑指南

「欢迎关注,每天更新工作实用技能」

标签: #mysqlsumdouble