<?php /* 燕十八 公益PHP培训 课堂地址:YY频道88354001 学习社区:www.zixue.it 表名:stu 有如下表及数据 +------+---------+-------+ | name | subject | score | +------+---------+-------+ | 张三 | 数学 | 90 | | 张三 | 语文 | 50 | | 张三 | 地理 | 40 | | 李四 | 语文 | 55 | | 李四 | 政治 | 45 | | 王五 | 政治 | 30 | +------+---------+-------+ 要求:查询出2门及2门以上不及格者的平均成绩 */ //正想思维 //1 先找出挂科超过两门的同学 $sql="select name from stu group by name having sum(score<60)>=2;"; //+------+ //| name | //+------+ //| 张三 | //| 李四 | //+------+ //2 求出所有同学的平均分 $sql="select name,avg(score) from stu group by name;"; //+------+------------+ //| name | avg(score) | //+------+------------+ //| 张三 | 60.0000 | //| 李四 | 50.0000 | //| 王五 | 30.0000 | //| 赵六 | 88.0000 | //+------+------------+ //有1和2的结果,我们知道结果只要从张三,李四两个人的平均分就可以了 $sql="select name,avg(score) from stu where name in ('张三','李四') group by name;" //+------+------------+ //| name | avg(score) | //+------+------------+ //| 张三 | 60.0000 | //| 李四 | 50.0000 | //+------+------------+ //将语句整合 $sql="select name ,avg(score) from stu where name in (select name from (select name from stu group by name having sum(score<60)>=2)as temp) group by name;"; //+------+------------+ //| name | avg(score) | //+------+------------+ //| 张三 | 60.0000 | //| 李四 | 50.0000 | //+------+------------+ //方法2 方向思维 //1想求出平均值 $sql='select name,avg(score) from stu group by name;'; //+------+------------+ //| name | avg(score) | //+------+------------+ //| 张三 | 60.0000 | //| 李四 | 50.0000 | //| 王五 | 30.0000 | //| 赵六 | 88.0000 | //+------+------------+ //再求挂科超过2门的学生 $sql="select name,avg(score),sum(score<60) as gk from stu group by name having gk>=2;"; //+------+------------+------+ //| name | avg(score) | gk | //+------+------------+------+ //| 张三 | 60.0000 | 2 | //| 李四 | 50.0000 | 2 | //+------+------------+------+ //熟悉sql语句,一句话可以搞定,而且效率比前面的高了好几倍