数据库SQL面试题分析:用一条SQL 语句 查询出每门课都大于80 分的学生姓名

本文介绍一个数据库SQL面试题,用一条SQL 语句 查询出每门课都大于80 分的学生姓名。给出分析过程和解答结果。

环境

如下是3个学生,3门课程的成绩,不是每个学生都有成绩。

gbase> create table t_chengji(name varchar(20),kecheng varchar(10),fenshu int);
Query OK, 0 rows affected (Elapsed: 00:00:00.11)

gbase> insert into t_chengji values('张三','语文',81);
Query OK, 1 row affected (Elapsed: 00:00:00.05)

gbase> insert into t_chengji values('张三','数学',75);
Query OK, 1 row affected (Elapsed: 00:00:00.06)

gbase> insert into t_chengji values('李四','语文',76);
Query OK, 1 row affected (Elapsed: 00:00:00.05)

gbase> insert into t_chengji values('李四','数学',90);
Query OK, 1 row affected (Elapsed: 00:00:00.04)

gbase> insert into t_chengji values('王五','语文',81);
Query OK, 1 row affected (Elapsed: 00:00:00.05)

gbase> insert into t_chengji values('王五','数学',100);
Query OK, 1 row affected (Elapsed: 00:00:00.06)

gbase> insert into t_chengji values('王五','英语',90);
Query OK, 1 row affected (Elapsed: 00:00:00.05)

gbase> select * from t_chengji;
+--------+---------+--------+
| name   | kecheng | fenshu |
+--------+---------+--------+
| 张三   | 语文    |     81 |
| 张三   | 数学    |     75 |
| 李四   | 语文    |     76 |
| 李四   | 数学    |     90 |
| 王五   | 语文    |     81 |
| 王五   | 数学    |    100 |
| 王五   | 英语    |     90 |
+--------+---------+--------+
7 rows in set (Elapsed: 00:00:00.01)

问题

查询每个科目成绩都超过80分的。注意这里功课,只包含已经考试的,没有考试的算符合。

查询 数据库SQL面试题:查询学生张三没有选修的课程号有哪些

解答

方案1

获得有科目小于等于80分的人员列表,然后从人员完整列表里扣除这部分。

有科目小于等于80分的人员列表

通过where fenshu<=80可以获得人员信息。

gbase> select * from t_chengji where fenshu<=80;
+--------+---------+--------+
| name   | kecheng | fenshu |
+--------+---------+--------+
| 张三   | 数学    |     75 |
| 李四   | 语文    |     76 |
+--------+---------+--------+
2 rows in set (Elapsed: 00:00:00.00)

通过distinct name 去掉可能重复的数据,稍微提升点性能。

gbase> select distinct name from t_chengji where fenshu<=80;
+--------+
| name   |
+--------+
| 李四   |
| 张三   |
+--------+
2 rows in set (Elapsed: 00:00:00.05)

获得所有人员名字列表

直接全表不带条件的distinct name就可以了。

gbase> select distinct name from t_chengji;
+--------+
| name   |
+--------+
| 张三   |
| 王五   |
| 李四   |
+--------+
3 rows in set (Elapsed: 00:00:00.05)

所有名字扣除有科目小于等于80的就是所有科目大于80分的。

gbase> select distinct name from t_chengji where name not in (select distinct name from t_chengji where fenshu<=80);
+--------+
| name   |
+--------+
| 王五   |
+--------+
1 row in set (Elapsed: 00:00:00.10)

方案2

获得每个姓名的最高分和最低分

gbase> select name,min(fenshu),max(fenshu) from t_chengji group by name;
+--------+-------------+-------------+
| name   | min(fenshu) | max(fenshu) |
+--------+-------------+-------------+
| 李四   |          76 |          90 |
| 张三   |          75 |          81 |
| 王五   |          81 |         100 |
+--------+-------------+-------------+
3 rows in set (Elapsed: 00:00:00.04)

然后过滤最低分大于80的

gbase> select name,min(fenshu),max(fenshu) from t_chengji group by name having min(fenshu)>80;
+--------+-------------+-------------+
| name   | min(fenshu) | max(fenshu) |
+--------+-------------+-------------+
| 王五   |          81 |         100 |
+--------+-------------+-------------+
1 row in set (Elapsed: 00:00:00.07)

gbase> select name  from t_chengji group by name having min(fenshu)>80;        +--------+
| name   |
+--------+
| 王五   |
+--------+
1 row in set (Elapsed: 00:00:00.05)

查看科目语文成绩,比科目数学成绩高的人员

gbase> select * from t_chengji tc1 left join t_chengji tc2 on tc2.name=tc1.name  and tc2.kecheng='数学'  where tc1.kecheng='语文' ;
+--------+---------+--------+--------+---------+--------+
| name   | kecheng | fenshu | name   | kecheng | fenshu |
+--------+---------+--------+--------+---------+--------+
| 张三   | 语文    |     81 | 张三   | 数学    |     75 |
| 李四   | 语文    |     76 | 李四   | 数学    |     90 |
| 王五   | 语文    |     81 | 王五   | 数学    |    100 |
+--------+---------+--------+--------+---------+--------+
3 rows in set (Elapsed: 00:00:00.05)

gbase> select * from t_chengji tc1 left join t_chengji tc2 on tc2.name=tc1.name  and tc2.kecheng='数学'  where tc1.kecheng='语文' and tc1.fenshu > tc2.fenshu ;
+--------+---------+--------+--------+---------+--------+
| name   | kecheng | fenshu | name   | kecheng | fenshu |
+--------+---------+--------+--------+---------+--------+
| 张三   | 语文    |     81 | 张三   | 数学    |     75 |
+--------+---------+--------+--------+---------+--------+
1 row in set (Elapsed: 00:00:00.15)