用一条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分的同学的姓名：

<span style="color: #008080">1</span> <span style="color: #0000ff">select</span> <span style="color: #0000ff">distinct</span> name <span style="color: #0000ff">from</span> grade <span style="color: #0000ff">where</span> name <span style="color: #808080">not</span> <span style="color: #808080">in</span> (<span style="color: #0000ff">select</span> <span style="color: #0000ff">distinct</span> name <span style="color: #0000ff">from</span> grade <span style="color: #0000ff">where</span> score<span style="color: #808080"><=</span><span style="color: #800000; font-weight: bold">80</span>);

View Code

还有一种简单的写法：

<span style="color: #008080">1</span> <span style="color: #0000ff">select</span> name <span style="color: #0000ff">from</span> grade <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> name <span style="color: #0000ff">having</span> <span style="color: #ff00ff">min</span>(score)<span style="color: #808080">></span><span style="color: #800000; font-weight: bold">80</span>;

View Code

 

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

<span style="color: #008080">1</span> <span style="color: #0000ff">select</span> name <span style="color: #0000ff">from</span><span style="color: #000000"> (
</span><span style="color: #008080">2</span>    <span style="color: #0000ff">select</span> <span style="color: #ff00ff">count</span>(<span style="color: #808080">*</span>) t, <span style="color: #ff00ff">sum</span>(score) num, name <span style="color: #0000ff">from</span> grade <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span><span style="color: #000000"> name       
</span><span style="color: #008080">3</span> ) <span style="color: #0000ff">as</span> a <span style="color: #0000ff">where</span> a.num<span style="color: #808080">></span><span style="color: #800000; font-weight: bold">80</span><span style="color: #808080">*</span>t;

View Code

也有一种简单的写法：

<span style="color: #008080">1</span> <span style="color: #0000ff">select</span> name, <span style="color: #ff00ff">avg</span>(score) <span style="color: #0000ff">as</span> sc <span style="color: #0000ff">from</span> grade <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> name <span style="color: #0000ff">having</span> <span style="color: #ff00ff">avg</span>(score)<span style="color: #808080">></span><span style="color: #800000; font-weight: bold">80</span>;

View Code

 

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

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