本文介绍一个数据库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)