php mysql having_跟燕十八学习PHP-第二十五天-mysql"group by和having的综合练习"

最新推荐文章于 2024-01-22 08:56:27 发布

乖乖Show

最新推荐文章于 2024-01-22 08:56:27 发布

阅读量156

点赞数

文章标签： php mysql having

本文链接：https://blog.csdn.net/weixin_35849957/article/details/113289477

版权

/**

燕十八公益PHP培训

课堂地址:YY频道88354001

学习社区:www.zixue.it

**/

mysql> create table result (

-> name varchar(20),

-> subject varchar(20),

-> score tinyint

-> )enigne myisam charset utf8;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds

to your MySQL server version for the right syntax to use near 'enigne myisam charset utf8'

at line 5

mysql> create table result (

-> name varchar(20),

-> subject varchar(20),

-> score tinyint

-> )engine myisam charset utf8;

Query OK, 0 rows affected (0.11 sec)

mysql> desc result;

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

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

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

3 rows in set (0.06 sec)

mysql> insert into result

-> values

-> ('张三','数学',90),

-> ('张三','语文',50),

-> ('张三','地理',40),

-> ('李四','语文',55),

-> ('李四','政治',45),

-> ('王五','政治',30);

Query OK, 6 rows affected (0.03 sec)

Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from result;

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

| name | subject | score |

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

| 张三 | 数学 | 90 |

| 张三 | 语文 | 50 |

| 张三 | 地理 | 40 |

| 李四 | 语文 | 55 |

| 李四 | 政治 | 45 |

| 王五 | 政治 | 30 |

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

6 rows in set (0.01 sec)

mysql> show create table result;

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

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

| Table | Create Table

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

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

| result | CREATE TABLE `result` (

`name` varchar(20) DEFAULT NULL,

`subject` varchar(20) DEFAULT NULL,

`score` tinyint(4) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

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

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

1 row in set (0.00 sec)

mysql> #第一种错误

mysql> select name,avg(scocre) from result

-> where score<60 group by name having count(*)>=2;

ERROR 1054 (42S22): Unknown column 'scocre' in 'field list'

mysql> select name,avg(score) from result

-> where score<60 group by name having count(*)>=2;

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

| name | avg(score) |

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

| 张三 | 45.0000 |

| 李四 | 50.0000 |

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

2 rows in set (0.00 sec)

mysql> select name,avg(score) from result

-> where score<60 group by name;

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

| name | avg(score) |

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

| 张三 | 45.0000 |

| 李四 | 50.0000 |

| 王五 | 30.0000 |

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

3 rows in set (0.00 sec)

mysql> select * from result;

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

| name | subject | score |

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

| 张三 | 数学 | 90 |

| 张三 | 语文 | 50 |

| 张三 | 地理 | 40 |

| 李四 | 语文 | 55 |

| 李四 | 政治 | 45 |

| 王五 | 政治 | 30 |

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

6 rows in set (0.00 sec)

mysql> select name,avg(score) from result

-> group by name having count(score<60) >= 2;

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

| name | avg(score) |

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

| 张三 | 60.0000 |

| 李四 | 50.0000 |

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

2 rows in set (0.00 sec)

mysql> #第二种错误

mysql> select name,avg(score),count(score<60) as gks

-> from result

-> group by name having gks>=2;

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

| name | avg(score) | gks |

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

| 张三 | 60.0000 | 3 |

| 李四 | 50.0000 | 2 |

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

2 rows in set (0.00 sec)

mysql> #暴露他的错误

mysql> insert into result

-> values

-> ('赵六','语文',100),

-> ('赵六','数学',99),

-> ('赵六','品德',98);

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from result;

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

| name | subject | score |

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

| 张三 | 数学 | 90 |

| 张三 | 语文 | 50 |

| 张三 | 地理 | 40 |

| 李四 | 语文 | 55 |

| 李四 | 政治 | 45 |

| 王五 | 政治 | 30 |

| 赵六 | 语文 | 100 |

| 赵六 | 数学 | 99 |

| 赵六 | 品德 | 98 |

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

9 rows in set (0.00 sec)

mysql> select name,avg(score),count(score<60) as gks

-> from result

-> group by name having gks>=2;

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

| name | avg(score) | gks |

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

| 张三 | 60.0000 | 3 |

| 李四 | 50.0000 | 2 |

| 赵六 | 99.0000 | 3 |

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

3 rows in set (0.00 sec)

mysql> select count(1) from result;

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

| count(1) |

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

| 9 |

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

1 row in set (0.03 sec)

mysql> select count(0) from result;

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

| count(0) |

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

| 9 |

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

1 row in set (0.00 sec)

mysql> #正解

mysql> #第1步:我们就查询所有的平均分

mysql> select name,avg(score) from result group by name;

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

| name | avg(score) |

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

| 张三 | 60.0000 |

| 李四 | 50.0000 |

| 王五 | 30.0000 |

| 赵六 | 99.0000 |

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

4 rows in set (0.03 sec)

mysql> #下一步,再想办法计算出每个人挂科的情况

mysql> select name,subject,score,score<60 as g from result;

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

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

| 张三 | 数学 | 90 | 0 |

| 张三 | 语文 | 50 | 1 |

| 张三 | 地理 | 40 | 1 |

| 李四 | 语文 | 55 | 1 |

| 李四 | 政治 | 45 | 1 |

| 王五 | 政治 | 30 | 1 |

| 赵六 | 语文 | 100 | 0 |

| 赵六 | 数学 | 99 | 0 |

| 赵六 | 品德 | 98 | 0 |

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

9 rows in set (0.00 sec)

mysql> #如上,挂科数目就是g的sum结果

mysql> #综合上面两个语句

mysql> select name,avg(score),sum(score<60)) as gks

-> from result

-> group by name;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds

to your MySQL server version for the right syntax to use near ') as gks

from result

group by name' at line 1

mysql> select name,avg(score),sum(score<60) as gks

-> from result

-> group by name;

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

| name | avg(score) | gks |

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

| 张三 | 60.0000 | 2 |

| 李四 | 50.0000 | 2 |

| 王五 | 30.0000 | 1 |

| 赵六 | 99.0000 | 0 |

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

4 rows in set (0.00 sec)

mysql> #每个人的平均分及挂科数已算出,再having筛选一下即可.

mysql> select name,avg(score),sum(score<60) as gks

-> from result

-> group by name having gks>=2;

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

| name | avg(score) | gks |

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

| 张三 | 60.0000 | 2 |

| 李四 | 50.0000 | 2 |

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

2 rows in set (0.00 sec)

mysql> #这一个思路是逆向思维,先查出所有人的平均,再筛选.

mysql> #如果正常的考虑,我们可能会这样做:

mysql> #先找出谁的挂科数>=2,找到这些人,再求这些人的平均分

mysql> #先找挂科数>=2的那些人

mysql> select name from result where score<60;

+------+

| name |

+------+

| 张三 |

| 李四 |

| 王五 |

+------+

5 rows in set (0.01 sec)

mysql> select name,score from result where score<60;

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

| name | score |

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

| 张三 | 50 |

| 张三 | 40 |

| 李四 | 55 |

| 李四 | 45 |

| 王五 | 30 |

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

5 rows in set (0.00 sec)

mysql> select name,count(1),score from result where score<60;

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

| name | count(1) | score |

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

| 张三 | 5 | 50 |

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

1 row in set (0.00 sec)

mysql> select name,count(1),score from result where score<60

-> group by name;

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

| name | count(1) | score |

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

| 张三 | 2 | 50 |

| 李四 | 2 | 55 |

| 王五 | 1 | 30 |

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

3 rows in set (0.00 sec)

mysql> select name,count(1) from result where score<60

-> group by name;

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

| name | count(1) |

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

| 张三 | 2 |

| 李四 | 2 |

| 王五 | 1 |

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

3 rows in set (0.00 sec)

mysql> select name from result

-> where score<60 and count(1)>=2

-> group by name;

ERROR 1111 (HY000): Invalid use of group function

mysql> select name,count(1) as gks from result

-> where score<60

-> group by name

-> having gks>=2;

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

| name | gks |

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

| 张三 | 2 |

| 李四 | 2 |

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

2 rows in set (0.00 sec)

mysql> select name from (select name,count(1) as gks from result

-> where score<60

-> group by name

-> having gks>=2);

ERROR 1248 (42000): Every derived table must have its own alias

mysql> select name from (select name,count(1) as gks from result

-> where score<60

-> group by name

-> having gks>=2) as tmp;

+------+

| name |

+------+

| 张三 |

| 李四 |

+------+

2 rows in set (0.00 sec)

mysql> #最终3层嵌套的子查询

mysql> select name,avg(score)

-> from result

-> where name in (select name from (select name,count(1) as gks from result

-> where score<60

-> group by name

-> having gks>=2) as tmp)

-> group by name;

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

| name | avg(score) |

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

| 张三 | 60.0000 |

| 李四 | 50.0000 |

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

2 rows in set (0.00 sec)

mysql> #这个例子也做出来了,但是用了子查询,且嵌套,非常麻烦.

mysql> #如果group having用的熟悉,一条sql就完成了. 不必嵌套子查询

mysql> exit

燕十八老师太幽默了, 昨天的视频如下:

乖乖Show

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
php mysql having_跟燕十八学习PHP-第二十五天-mysql"group by和having的综合练习"

/**燕十八公益PHP培训课堂地址:YY频道88354001学习社区:www.zixue.it**/mysql> create table result (-> name varchar(20),-> subject varchar(20),-> score tinyint-> )enigne myisam charset utf8;ERROR 1064 (4200...
复制链接

扫一扫