这里博主用的数据库是mysql8.0版本的,有用不同数据库的同志们也可以看看,毕竟sql语句的写法都差异不大,废话不多说直接上sql:
涉及的数据表:student (学生表)
建表语句:
CREATE TABLE `student` (
`SID` int(11) NOT NULL AUTO_INCREMENT,
`SNAME` varchar(10) DEFAULT NULL,
`CLASSID` varchar(45) DEFAULT NULL,
`CLASSNAME` varchar(10) DEFAULT NULL,
`CHENGJI` double DEFAULT NULL,
PRIMARY KEY (`SID`)
)
插入参数:
insert into student VALUES(1,"张三",1,"一班",90.3),
(2,"李明",4,"四班",79.8),
(3,"李四",2,"二班",60),
(4,"赵六",3,"三班",98),
(5,"赵涛",1,"一班",88.5);
关键函数:count(if())
SQL:
select CLASSNAME,count(if(CHENGJI between 70 and 80,true,null))as "70~80",count(if(CHENGJI between 80 and 90,true,null))as "80~90",count(if(CHENGJI between 90 and 100,true,null))as "90~100" from student group by CLASSNAME order by CLASSID;
执行结果:
这个表比较简单,可以复制上面的过程(写的不是很好如有更好的见解,望各位大大们提出来)