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

痛定思痛。 2022-05-15 13:40 290阅读 0赞

## 简述 ##

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

### 文章目录 ###

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

## 在一个表上的时候 ##

先看看数据

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)