USE test
DROP TABLE if EXISTS grade
create table `grade` (
`id` int PRIMARY KEY,
`name` varchar (300),
`score` double
)
insert into `grade` (`id`, `name`, `score`) values('1','n1','59')
insert into `grade` (`id`, `name`, `score`) values('2','n2','66')
insert into `grade` (`id`, `name`, `score`) values('3','n3','78')
insert into `grade` (`id`, `name`, `score`) values('4','n1','48')
insert into `grade` (`id`, `name`, `score`) values('5','n3','85')
insert into `grade` (`id`, `name`, `score`) values('6','n5','51')
insert into `grade` (`id`, `name`, `score`) values('7','n4','98')
insert into `grade` (`id`, `name`, `score`) values('8','n5','53')
insert into `grade` (`id`, `name`, `score`) values('9','n2','67')
insert into `grade` (`id`, `name`, `score`) values('10','n4','88')
首先将上面内容保存在一个名字叫grade.sql 的文件里面,上面我私自修改了id的类型,实在看不下去了。然后登陆数据库使用mysql>source C:/grade.sql;
select name,max(score) from grade
select name,sum(score) from grade group by name order by score desc limit 1,5
select name,sum(score) as tot from grade group by name having tot<150
select name,avg(score) as scavg from grade group by name having scavg <80 and scavg >60
select name from grade group by name having sum(score) >150 and avg(score) <90这个应该是查询人吧,6才是查询人数吧
亲测有效
非常简单的: SELECT `class`, SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END), SUM(CASE WHEN score>=60 THEN 0 ELSE 1 END) FROM tb1 GROUP BY 1 主要是考试GROUP BY分组统计,CASE WHEN THEN ELSE END分情形功能,这个是非常典型的应用,几乎所有的数据库都支持上面的语句。欢迎分享,转载请注明来源:内存溢出
评论列表(0条)