Home > Database > Mysql Tutorial > mysql笔试题一:查询where having以及统计函数的使用_MySQL

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Release: 2016-06-01 13:36:24
Original
1227 people have browsed it

bitsCN.com

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

 

题目:

 

有表如下:

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

 

[sql] 

mysql> select * from student;  

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

| name | subject | score |  

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

| 张三 | 数学    |     80 |  

| 张三 | 语文    |     53 |  

| 张三 | 英语    |     59 |  

| 李四 | 数学    |     55 |  

| 李四 | 语文    |     56 |  

| 李四 | 英语    |     50 |  

| 王五 | 数学    |    100 |  

| 王五 | 语文    |     90 |  

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

8 rows in set  

 

要点:    

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

 

2、计算平均分。

 

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

 

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

 

[sql] 

mysql> select name,avg(score),sum(score = 2;  

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

| name | avg(score) | cnt |  

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

| 张三 | 64.0000    | 2   |  

| 李四 | 53.6667    | 3   |  

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

2 rows in set  

 

sum(score

 

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 拿到第一个,即最大分数的那条记录)

[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  

 

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

 

bitsCN.com
Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Issues
MySQL stops process
From 1970-01-01 08:00:00
0
0
0
Error when installing mysql on linux
From 1970-01-01 08:00:00
0
0
0
phpstudy cannot start mysql?
From 1970-01-01 08:00:00
0
0
0
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template