mysql大型程序案例_mysql精彩案例

例：查询出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>