mysql精彩案例

来源：互联网发布：莫烦 tensorflow 编辑：程序博客网时间：2024/05/16 03:09

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