您当前的位置: 用一条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);
更简单的：
SELECT name FROM grade GROUP BY name HAVING MIN(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;
更简单的：
select name, avg(score) as sc from grade g1 group by name having avg(score)>80 ;
- 1楼陶子
- select name, avg(score) as sc from grade g1 group by name having avg(score)gt;80 ;
- Re: praglody
- @陶子，感谢指导