insert into score
-> values
-> ('张三','数学',90),
-> ('张三','语文',50),
-> ('张三','地理',40),
-> ('李四','语文',55),
-> ('李四','政治',45),
-> ('王五','政治',30);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select *from score;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三 | 数学 | 90 |
| 张三 | 语文 | 50 |
| 张三 | 地理 | 40 |
| 李四 | 语文 | 55 |
| 李四 | 政治 | 45 |
| 王五 | 政治 | 30 |
+------+---------+-------+
6 rows in set (0.00 sec)
mysql> #查询出两门及两门以上不及格者的平均成绩；
select sum(score<60) as cont, name,avg(score) as avga from score group by name having cont>=2;
+------+------+---------+
| cont | name | avga |
+------+------+---------+
| 2 | 张三 | 60.0000 |
| 2 | 李四 | 50.0000 |
+------+------+---------+
2 rows in set (0.00 sec)
