[MySQL]group by 与 if 的统计技巧

group by查询旨在把某字段中相同的记录合并成一列，查询结果可受count(),sum()等统计函数影响

如下表

id	totalclick	validclick
1	3	1
2	3	1
3	5	2
4	2	1
5	3	1
6	5	2

以validclick为分组，统计每个分组的记录数，和每个分组的totalclick总和

SELECT count(`id`),sum(`totalclick`), validclick FROM diary_log GROUP BY validclick;

结果：

count(`id`)	sum(`totalclick`)	validclick
4	11	1
2	10	2

 

配合if，可以模拟count()统计每个分组的记录数

SELECT count(`id`), sum(if(validclick = 1,1,0)) validclick_1,sum(if(validclick = 2,1,0)) validclick_2,validclick FROM diary_log  GROUP BY validclick;

结果：

count(`id`)	validclick_1	validclick_2	validclick
4	4	0	1
2	0	2	2

 

对于count中应用if，则在第二第三个参数中只能赋值true和null：

SELECT COUNT(IF(`validclick`>2,TRUE,NULL)) AS `exceed_1_row_count` FROM diary_log;

错误的示范，这样做的话无论`validclick`是否大于2都会被计算：

SELECT COUNT(IF(`validclick`>2,1,0)) AS `exceed_1_row_count` FROM diary_log;