本文主要是介绍用一条sql语句查询出所有课程都大于80分的学生名单：，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
http://www.cnblogs.com/praglody/p/6854181.html
用一条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 ;
这篇关于用一条sql语句查询出所有课程都大于80分的学生名单：的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
