MYSQL 行轉列 以及基本的聚合函數count，與group by 以及distinct組合使用

在統計查詢中，經常會用到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;