当前位置: 澳门新濠3559 > 数据库 > 正文

不及格科目大于或等于2科的学生,2、计算平均分

时间:2019-11-29 07:15来源:数据库
写一条SQL语句,求出2门以及2门以上不及格的科目平均分 mysql笔试题一:查询where having以及统计函数的使用 一、有表如下 要出现2门以及2门以上的学科不及格   +------+---------+--------+ |

写一条SQL语句,求出2门以及2门以上不及格的科目平均分

mysql笔试题一:查询where having以及统计函数的使用

一、有表如下

>要出现2门以及2门以上的学科不及格

 

+------+---------+--------+  
| name | subject | score |  
+------+---------+--------+  
| 张三 | 数学    |     80 |  
| 张三 | 语文    |     53 |  
| 张三 | 英语    |     59 |  
| 李四 | 数学    |     55 |  
| 李四 | 语文    |     56 |  
| 李四 | 英语    |     50 |  
| 王五 | 数学    |    100 |  
| 王五 | 语文    |     90 |  
+------+---------+--------+ 

>计算该考生所有学科的平均分,不单是,不及格的那几门

题目:

只用一个select语句查询:不及格科目大于或等于2科的学生,的平均分(所有科目的平均分)。
要点:
1、要统计每个学生不及格科目的个数。
2、计算平均分。
误解一:使用count统计。count永远是统计的所有行!

 #创建表:

 

正解:用sum统计。先计算所有人的平均分,再筛选。

create table `ecs_mian2` (
    `user_name` varchar (20),
    `subject` varchar (20),
    `score` int (4)
); 
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('张三','数学','90');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('张三','语文','50');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('张三','地理','40');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('李四','语文','55');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('李四','政治','45');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('王五','政治','30');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','物理','30');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','化学','50');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','语文','45');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','数学','90');

有表如下:

mysql> select name,avg(score),sum(score < 60) as cnt from student group by name having cnt >= 2;  
+------+------------+-----+  
| name | avg(score) | cnt |  
+------+------------+-----+  
| 张三 | 64.0000    | 2   |  
| 李四 | 53.6667    | 3   |  
+------+------------+-----+  
2 rows in set  

图片 1

  www.2cto.com  

sum(score < 60) 就是统计 挂科数目。

 

只用一个select语句查询:不及格科目大于或等于2科的学生,的平均分(所有科目的平均分)。

having 是用于筛选的,这里不用用where。where只能用于存在的列。
二、还是上面的表,查询出每个学生最大分数的科目及分数

思路一:

 

mysql> select * from student where score in(select max(score) from student group by name);  
+------+---------+-------+  
| name | subject | score |  
+------+---------+-------+  
| 张三 | 数学    |    80 |  
| 李四 | 语文    |    56 |  
| 王五 | 数学    |   100 |  
+------+---------+-------+  
3 rows in set

求出score < 60的科目数,然后统计平均分

[sql] 

三、
一个班级表(class),其中的属性有:id, class_name
一个学生表(student),其中的属性有:id, student_name, class_id
一个成绩表(score),其中的属性有:id, student_id, course_name, score
通过一条sql语句找出各个班级的各个科目的平均成绩,按从大到小排序。

#这种方法:用where过滤了score < 60的科目,就算不到score >= 60的科目的平均分
SELECT user_name, AVG(score ) AS avg_score, 
COUNT( * ) AS num FROM ecs_mian2 WHERE score < 60 GROUP BY user_name  HAVING num >= 2

<span style="font-size:18px;">mysql> select * from student;  

select a.class_name, c.course_name, avg(c.score) as avg
from class a, student b, score c
where a.id=b.class_id and b.id=c.student_id
group by a.class_name, c.course_name
order by avg desc;

图片 2

+------+---------+--------+  

需要注意的地方:
(1)avg() 求完平均值后要用as对求得的平均值列命名。
(2)在where后跟多个条件语句的时候,记得使用and 或者 or来连接。
(3)group by 后跟多个列进行分组时,用逗号分开,其表达的意思是先按第一个属性列中的元素进行分组,在分完组的基础上再根据第二个属性列中的元素进行分组。
(4)desc表示从大到小(降序),asc表示从小到大(升序)。
(5)order by 后要写清楚根据那个属性进行升序或者降序进行排序的。
附加一条不在上面出现的值得注意的点:
(1)order by 后跟多个属性时,用逗号隔开,其表达的意思是先根据第一个属性进行升(降)序进行排序,当第一个属性中有相同的值存在的时候,再对这些相同值对应的数据根据第二个属性进行升(降)序进行排序。

这个结果是错误的,原因在于: " 用where过滤了score < 60的科目,就算不到score >= 60的科目的平均分 "

| name | subject | score |  

 

+------+---------+--------+  

思路二:

| 张三 | 数学    |     80 |  

#查出所有人的平均分
SELECT user_name,AVG( score ) FROM ecs_mian2 GROUP BY user_name;
#查出所有人不及格的课程(不及格标记为1,及格标记为0 )
SELECT user_name,score, score < 60 FROM ecs_mian2;
#不及格在2门以上的人
SELECT user_name,SUM( score < 60 ) AS bujige FROM ecs_mian2 GROUP BY user_name HAVING bujige >= 2
#综合以上3条语句的结果,可以得出,不及格科目>=2的人的所有学科平均分
SELECT user_name,AVG( score ) AS pjf, SUM( score < 60 ) AS bujige FROM ecs_mian2 GROUP BY user_name HAVING bujige >= 2

| 张三 | 语文    |     53 |  

该思路不同于where,没有过滤任何一门学科的分数,所以能够统计到平均分

| 张三 | 英语    |     59 |  

图片 3

| 李四 | 数学    |     55 |  

 

| 李四 | 语文    |     56 |  

 小结:

| 李四 | 英语    |     50 |  

1,mysql语句也需要灵活的思路

| 王五 | 数学    |    100 |  

2,表中的字段,可以认为是变量,变量当然可以计算,比较,调用函数等

| 王五 | 语文    |     90 |  

+------+---------+--------+  

8 rows in set</span>  

 

要点:  www.2cto.com  

1、要统计每个学生不及格科目的个数。 

 

2、计算平均分。

 

误解一:使用count统计。count永远是统计的所有行!

 

正解:用sum统计。先计算所有人的平均分,再筛选。

 

[sql] 

<span style="font-size:18px;">mysql> select name,avg(score),sum(score < 60) as cnt from student group by name having cnt >= 2;  

+------+------------+-----+  

| name | avg(score) | cnt |  

+------+------------+-----+  

| 张三 | 64.0000    | 2   |  

| 李四 | 53.6667    | 3   |  

+------+------------+-----+  

2 rows in set</span>  

 

sum(score < 60) 就是统计 挂科数目。

 

having 是用于筛选的,这里不用用where。where只能用于存在的列。

 

当然也可以用 子查询 左链接, 这样反到麻烦了。

 

题目2:还是上面的表。

 

查询每个学生的最大分数的科目及分数。

 

误解1:

 

[sql] 

mysql> select *,max(score) from student group by name;  

+------+---------+-------+------------+  

| name | subject | score | max(score) |  

+------+---------+-------+------------+  

| 张三 | 数学    |    80 |         80 |  

| 李四 | 数学    |    55 |         56 |  

| 王五 | 数学    |   100 |        100 |  

+------+---------+-------+------------+  

3 rows in set  

 

虽然查出了最大分数,但显示的科目是错误的。因为group by总是取第一条记录。

 

正解:

 

1、where 子查询 (先找到每个学生的最大分数, 在根据分数刷新出记录)。

 

[sql] 

mysql> select * from student where score in(select max(score) from student group by name);  

+------+---------+-------+  

| name | subject | score |  

+------+---------+-------+  

| 张三 | 数学    |    80 |  

| 李四 | 语文    |    56 |  

| 王五 | 数学    |   100 |  

+------+---------+-------+  

3 rows in set  

 

2、from子查询 (先排序,在group by 拿到第一个,即最大分数的那条记录)

  www.2cto.com  

[sql] 

mysql> select * from (select * from student order by score desc) as tmp group by name;  

+------+---------+-------+  

| name | subject | score |  

+------+---------+-------+  

| 张三 | 数学    |    80 |  

| 李四 | 语文    |    56 |  

| 王五 | 数学    |   100 |  

+------+---------+-------+  

3 rows in set  

 

整两种方法是有很大区别的,第二种只能取出一条记录,如果最大分数相同的话。

 

having以及统计函数的使用 题目: 有表如下: www.2cto.com 只用一个select语句查询:不及格科目大于或等于2科的学生,...

编辑:数据库 本文来源:不及格科目大于或等于2科的学生,2、计算平均分

关键词: