用一条SQL语句查出每门课都大于80分的学生的姓名

SET FOREIGN_KEY_CHECKS=0; 2 3 -- ---------------------------- 4 -- Table structure for grade 5 -- ---------------------------- 6 DROP TABLE IF EXISTS `grade`; 7 CREATE TABLE `grade` ( 8 `name` varchar(255) NOT NULL, 9 `class` varchar(255) NOT NULL, 10 `score` tinyint(4) NOT NULL 11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 12 13 -- ---------------------------- 14 -- Records of grade 15 -- ---------------------------- 16 INSERT INTO `grade` VALUES (‘张三‘, ‘语文‘, ‘81‘); 17 INSERT INTO `grade` VALUES (‘张三‘, ‘数学‘, ‘75‘); 18 INSERT INTO `grade` VALUES (‘李四‘, ‘语文‘, ‘76‘); 19 INSERT INTO `grade` VALUES (‘李四‘, ‘数学‘, ‘90‘); 20 INSERT INTO `grade` VALUES (‘王五‘, ‘语文‘, ‘81‘); 21 INSERT INTO `grade` VALUES (‘王五‘, ‘数学‘, ‘100‘); 22 INSERT INTO `grade` VALUES (‘王五‘, ‘英语‘, ‘90‘); 23 SET FOREIGN_KEY_CHECKS=1; View Code

查询每门课都大于80分的同学的姓名：

1 select distinct name from grade where name not in (select distinct name from grade where score<=80);

View Code

还有一种简单的写法：

1 select name from grade group by name having min(score)>80;

View Code

 

查询平均分大于80的学生名单：

1 select name from (
2    select count(*) t, sum(score) num, name from grade group by name       
3 ) as a where a.num>80*t;

View Code

也有一种简单的写法：

1 select name, avg(score) as sc from grade group by name having avg(score)>80;

View Code

 

用一条SQL语句查出每门课都大于80分的学生的姓名

标签：语文   exist   学生   tab   drop   font   har   英语   blog