<?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语句,一句话可以搞定,而且效率比前面的高了好几倍
