首页 > 代码库 > 用一条SQL语句查出每门课都大于80分的学生的姓名

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

用一条SQL语句查出每门课都大于80分的学生的姓名,数据表结构如下:

技术分享

建表SQL如下:

SET 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分的学生的姓名