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

FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for grade -- ---------------------------- DROP TABLE IF EXISTS `grade`; CREATE TABLE `grade` ( `name` varchar(255) NOT NULL, `class` varchar(255) NOT NULL, `score` tinyint(4) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------- -- Records of grade -- ---------------------------- INSERT INTO `grade` VALUES (‘张三‘, ‘语文‘, ‘81‘); INSERT INTO `grade` VALUES (‘张三‘, ‘数学‘, ‘75‘); INSERT INTO `grade` VALUES (‘李四‘, ‘语文‘, ‘76‘); INSERT INTO `grade` VALUES (‘李四‘, ‘数学‘, ‘90‘); INSERT INTO `grade` VALUES (‘王五‘, ‘语文‘, ‘81‘); INSERT INTO `grade` VALUES (‘王五‘, ‘数学‘, ‘100‘); INSERT INTO `grade` VALUES (‘王五‘, ‘英语‘, ‘90‘); SET FOREIGN_KEY_CHECKS=1;

 

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

SELECT DISTINCT name FROM grade WHERE name NOT IN(SELECT DISTINCT name FROM grade WHERE score <=80);

 

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

SELECT name FROM (SELECT COUNT(*) AS t,SUM(score) AS num,name FROM `grade` GROUP BY name) AS a WHERE a.num > 80*t;

 

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

标签：str   utf8mb4   大于   ade   where   name   group   drop   core