MYSQL查询每个班级的人数

MYSQL查询每个班级的人数

由简单到复杂，逐步增加指令内容
表名称：NAME_TB
表头名称：ID，NAME，CLASS，SEX
首先是如何在同学姓名表中查看班级列表

SELECT CLASS 
FROM NAME_TB 
GROUP BY CLASS; 

然后是如何在同学姓名表统计班级人数

SELECT COUNT(*) 
FROM NAME_TB 
GROUP BY CLASS; 

最后把班级和同学姓名排到一起

SELECT CLASS,COUNT(*) 
FROM NAME_TB 
GROUP BY CLASS; 

那么要如何统计班上的男生人数

SELECT CLASS,COUNT(CASE WHEN SEX="男" THEN 1 END) 
FROM NAME_TB 
GROUP BY CLASS; 

按照男生人数由多到少排序后输出

SELECT CLASS,COUNT(CASE WHEN SEX="男" THEN 1 END) AS SEXBOY 
FROM NAME_TB 
GROUP BY CLASS 
ORDER BY SEXBOY DESC; 

接下来统计男生在班级人数中所占的比例

SELECT CLASS,ROUND(COUNT(CASE WHEN SEX="男" THEN 1 END)/COUNT(*)*100,1) 
FROM NAME_TB 
GROUP BY CLASS; 

接下来统计男生在班级人数中所占的比例由高到低排序后输出

SELECT CLASS,ROUND(COUNT(CASE WHEN SEX="男" THEN 1 END)/COUNT(*)*100,1) AS SEXBOY 
FROM NAME_TB 
GROUP BY CLASS 
ORDER BY SEXBOY DESC;