本文将探讨以下问题
1.count(*) 、 count(n)、count(null)与count(fieldName)
2.distinct 与 count 连用
3.group by (多个字段) 与 count 实现分组计数
4.case when 语句与 count 连用实现按过滤计数
参考文章:
Select count(*)和Count(1)的区别和执行方式
准备工作
-- 创建表
CREATE TABLE `tb_student` (
`id` int(11) NOT NULL,
`stu_name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '学生姓名',
`tea_name` varchar(255) DEFAULT NULL COMMENT '教师姓名',
`stu_class` varchar(255) DEFAULT NULL COMMENT '所在班级名称',
`stu_sex` varchar(255) DEFAULT NULL COMMENT '学生性别',
`stu_sex_int` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('0', '小明', '老张', '一班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('1', '小红', '老张', '一班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('2', '小刚', '老王', '一班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('3', '小兰', '老王', '一班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('4', '小军', '老张', '二班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('5', '小芳', '老张', '二班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('6', '小强', '老王', '二班', '男',0);
INSERT