MYSQL 行转列 以及基本的聚合函数count，与group by 以及distinct组合使用

时间：2021-12-16 16:54:06

在统计查询中，经常会用到count函数，这里是基础的 MYSQL 行转列 以及基本的聚合函数count，与group by 以及distinct组合使用

-- 创建表

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`  VALUES ('0', '小明', '老张', '一班', '男',0);

INSERT INTO `tb_student` VALUES ('1', '小红', '老张', '一班', '女',0);

INSERT INTO `tb_student`  VALUES ('2', '小刚', '老王', '一班', '男',0);

INSERT INTO `tb_student`  VALUES ('3', '小兰', '老王', '一班', '女',0);

INSERT INTO `tb_student`  VALUES ('4', '小军', '老张', '二班', '男',0);

INSERT INTO `tb_student`  VALUES ('5', '小芳', '老张', '二班', '女',0);

INSERT INTO `tb_student`  VALUES ('6', '小强', '老王', '二班', '男',0);

INSERT INTO `tb_student`  VALUES ('7', '小娜', '老王', '二班', '女',0);

INSERT INTO `tb_student` VALUES ('8', null, null, null, null,null);*/

/***************************/

EXPLAIN SELECT count(2) from tb_student;

SELECT count(*) from tb_student;                //8

SELECT count(1) from tb_student;                //8

SELECT count(stu_name) from tb_student; //7

SELECT count(NULL) from tb_student;            //0

/**总结

当count的表达式为 NULL 时 不会计数 ,所以count(fieldName) 当fieldName 为null时 不会计数

所以 count(n)用于查询表的记录数

*/

SELECT COUNT(DISTINCT tea_name) from tb_student;

SELECT DISTINCT tea_name from tb_student;

SELECT *,count(tea_name) from tb_student GROUP BY tea_name;

/**查询每个老师在一班教了多少学生，在二班教了多少学生*/

 select *,count(id) FROM tb_student GROUP BY tea_name,stu_class;

/*这种方法不太直观我们可以把结果行转列更加清晰表达每个教师交每个班的人数*/

SELECT tea_name,

    COUNT(case when stu_class='一班' then 1 ELSE NULL END ) AS '一班人数',

    COUNT(case when stu_class='二班' then 5 ELSE NULL END ) AS '二班人数'

FROM tb_student GROUP BY tea_name;

/**每个老师各自教了多少学生*/

SELECT tea_name,

    COUNT(*) AS '学生人数'

FROM tb_student GROUP BY tea_name;

相关文章