Java OpenResty Spring Spring Boot MySQL Redis MongoDB PostgreSQL Linux Android Nginx 面试 小程序 Arthas JVM AQS juc Kubernetes Docker DevOps


SQL 查询出成绩表中成绩大于 90 的学生名字

SQL 面试 大约 1004 字

成绩表

mysql> select * from test_score;
+----+---------+---------+-------+
| id | student | subject | score |
+----+---------+---------+-------+
|  1 | 张三    | 语文    | 95    |
|  2 | 张三    | 数学    | 95    |
|  3 | 张三    | 英语    | 95    |
|  4 | 李四    | 语文    | 95    |
|  5 | 李四    | 数学    | 85    |
|  6 | 李四    | 英语    | 95    |
|  7 | 王五    | 语文    | 85    |
|  8 | 王五    | 数学    | 80    |
|  9 | 王五    | 英语    | 95    |
+----+---------+---------+-------+

问题一

查找出每门课成绩都大于等于90的学生名字

select * from test_score group by student HAVING MIN(score) >= 90

输出:

mysql> select student from test_score group by student HAVING MIN(score) >= 90;
+---------+
| student |
+---------+
| 张三    |
+---------+

问题二

两门或两门以上的课成绩大于等于90

select student from test_score where score >= 90 group by student HAVING count(student) >= 2

输出:

mysql> select student from test_score where score >= 90 group by student HAVING count(student) >= 2;
+---------+
| student |
+---------+
| 张三    |
| 李四    |
+---------+
阅读 1328 · 发布于 2022-04-12

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb

扫描下方二维码关注公众号和小程序↓↓↓

扫描二维码关注我
昵称:
随便看看 换一批