熟练掌握sql语句的力量

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

 

转载于:https://www.cnblogs.com/luowen/archive/2012/10/25/2740414.html