【mysql】成绩单表，找到所有成绩都不及格的所有人

简述

这里考虑到两种情况。如果人的信息和成绩的信息都在一个表上，和在不同的表上的。

文章目录

简述在一个表上的时候在两张表上

在一个表上的时候

先看看数据

mysql> select * from sc; +---------+-----+-------+ | sno | cno | grade | +---------+-----+-------+ | 2005001 | 1 | 87.00 | | 2005001 | 2 | 67.00 | | 2005001 | 3 | 90.00 | | 2005002 | 2 | 95.00 | | 2005003 | 3 | 88.00 | | 2005003 | 7 | NULL | | 2005010 | 1 | 87.00 | | 2005010 | 2 | NULL | | 2005010 | 3 | 80.00 | | 2005010 | 4 | 87.00 | | 2005010 | 6 | 85.00 | | 2005011 | 1 | 52.00 | | 2005011 | 2 | 47.00 | | 2005011 | 3 | 53.00 | | 2005011 | 5 | 45.00 | | 2005012 | 1 | 84.00 | | 2005012 | 3 | NULL | | 2005012 | 4 | 67.00 | | 2005012 | 5 | 81.00 | | 2005012 | 7 | NULL | +---------+-----+-------+ 20 rows in set (0.00 sec)

查询方法

mysql> select sno from sc -> group by sno -> having max(grade) < 60; +---------+ | sno | +---------+ | 2005011 | +---------+ 1 row in set (0.00 sec)

在两张表上

比如，还是基于刚刚的问题，我这时候需要获得这个编号所对应的人的名字和信息。

mysql> select * from student s -> where s.sno in ( -> select sno from sc -> group by sno -> having max(grade) < 60 -> ); +---------+--------+------+------+-------+ | sno | sname | sage | ssex | sdept | +---------+--------+------+------+-------+ | 2005011 | 张丽萍 | 19 | 女 | Ch | +---------+--------+------+------+-------+ 1 row in set (0.01 sec) mysql>

如果想要基于这张表还创建一个新的表。

mysql> create table ts -> ( select * from student s -> where s.sno in ( -> select sno from sc -> group by sno -> having max(grade) < 60 -> )); Query OK, 1 row affected (0.18 sec) Records: 1 Duplicates: 0 Warnings: 0

看看新表的结果

mysql> select * from ts; +---------+--------+------+------+-------+ | sno | sname | sage | ssex | sdept | +---------+--------+------+------+-------+ | 2005011 | 张丽萍 | 19 | 女 | Ch | +---------+--------+------+------+-------+ 1 row in set (0.00 sec)