现在有一个成绩表Score,其中成绩表中有两个字段,分别是名字name,另外一个是grade。如下所示
name | grade |
---|---|
a | 20 |
b | 67 |
b | 90 |
a | 65 |
b | 32 |
name | low | middle | high |
a | 1 | 0 | 1 |
b | 1 | 0 | 2 |
count(*) 计算值为null的数
count(col_name) 不计算值为null的值
使用count聚合函数
先使用group by进行分组,然后通过if判断语句对每一组的grade做判读(如何符合条件,则值为grade,否则为null),最后通过count聚合函数得到每个组的low,middle和high的个数
select
name,
count(if(grade <= 30, grade, null)) as low,
count(if(grade >30 and grade <=60, grade, null)) as middle,
count(if(grade >60 and grade <=100, grade, null)) as high
from score
group by name
使用sum聚合函数
先使用group by进行分组,然后通过if判断语句对每一组的grade做判读(如果符合条件,则值为1,否则为0),最后通过sum聚合函数得到每个组的low,middle和high的个数和
select
name,
sum(if(grade <= 30, 1, 0)) as low,
sum(if(grade >30 and grade <=60, 1, 0)) as middle,
sum(if(grade >60 and grade <=100, 1, 0)) as high
from score
group by name