例:查询出2门及2门以上不及格者的平均成绩
mysql> select * from a;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
|张三|数学| 90 |
|张三|语文| 50 |
|张三|地理| 40 |
|王五|政治| 30 |
|李四|政治| 45 |
|李四|语文| 55 |
+------+---------+-------+
6 rows in set (0.00 sec)
答案分析:
//每个人的评价成绩
mysql> select name, avg(score) from a
-> group by name;
+------+------------+
| name | avg(score) |
+------+------------+
|张三| 60 |
|李四| 50 |
|王五| 30 |
+------+------------+
3 rows in set (0.00 sec)
//每个人有多少科目
mysql> select name, count(subject) froma
-> group by name;
+------+----------------+
| name | count(subject) |
+------+----------------+
|张三| 3 |
|李四| 2 |
|王五| 1 |
+------+----------------+
3 rows in set (0.00 sec)
//不及格成绩有两门以上的
mysql> select name, count(subject) froma
-> where score<60
-> group by name
-> having count(subject)>=2;
+------+----------------+
| name | count(subject) |
+------+----------------+
|张三| 2 |
|李四| 2 |
+------+----------------+
2 rows in set (0.00 sec)
//不及格成绩有两门以上的,不及格的评价成绩
mysql> select name,count(subject),avg(score) from a
-> where score<60
-> group by name
-> having count(subject)>=2;
+------+----------------+------------+
| name | count(subject) | avg(score) |
+------+----------------+------------+
|张三| 2 | 45 |
|李四| 2 | 50 |
+------+----------------+------------+
2 rows in set (0.00 sec)
//两门以上不及格的的人
mysql> select name from a
-> where score<60
-> group by name
-> having count(subject)>=2;
+------+
| name |
+------+
|张三|
|李四|
+------+
2 rows in set (0.00 sec)
//
mysql> select name ,avg(score) from awhere name in (select name from a where sc
ore<60 group by name havingcount(subject)>=2) group by name;
+------+------------+
| name | avg(score) |
+------+------------+
|张三| 60 |
|李四| 50 |
+------+------------+
2 rows in set (0.00 sec)
mysql>