前言
前面的一篇文章写了下数据库设计和SQL语句基础,里面都是简单的增删改查,而且对最重要的查询也没有深入研究,实在是罪过,那么本文就sql复杂的查询语句进行探讨,也是当作一个记录好了
No.1
假设我们现在有张这样的score表(sn表示student_name学生姓名,cn表示课程class_name名字,sc表示score成绩)
sn | cn | sc |
---|---|---|
光头 | 语文 | 9 |
吴克 | 语文 | 100 |
光头 | 物理 | 99 |
吴克 | 物理 | 7 |
光头 | 体育 | 100 |
吴克 | 体育 | 5 |
现在我们要求不及格(<60)科目大于等于2门的那个人的平均分,一条sql解决?
先post答案:
SELECT sn,AVG(sc)
FROM score
WHERE sn
IN (SELECT sn FROM score WHERE sc<60 GROUP BY sn HAVING COUN(sc)>=2)
GROUP BY sn;
现在来理解上面这么一串东西
根据[1],我们知道sql执行顺序是FROM=>WHERE(IN)=>GROUP BY=>HAVING=>SELECT
先看外层的:
1. FROM score,我们还是得到score这张表
sn | cn | sc |
---|---|---|
光头 | 语文 | 9 |
吴克 | 语文 | 100 |
光头 | 物理 | 99 |
吴克 | 物理 | 7 |
光头 | 体育 | 100 |
吴克 | 体育 | 5 |
2. WHERE sn IN ( SELECT sn FROM score WHERE sc<60 GROUP BY sn HAVING COUN(sc)>=2)
这个就要求括号里面的 (SELECT sn FROM score WHERE sc<60 GROUP BY sn HAVING COUN(sc)>=2)
2.1 FROM score,我们可以得到原来的表
sn | cn | sc |
---|---|---|
光头 | 语文 | 9 |
吴克 | 语文 | 100 |
光头 | 物理 | 99 |
吴克 | 物理 | 7 |
光头 | 体育 | 100 |
吴克 | 体育 | 5 |
2.2 WHERE sc<60,我们可以筛出sc<60的项
sn | cn | sc |
---|---|---|
光头 | 语文 | 9 |
吴克 | 物理 | 7 |
吴克 | 体育 | 5 |
2.3 GROUP BY sn,有点像依据学生姓名来归档放在一起并合并了单元格,关于GROUP BY[2]会讲的更加好理解
2.4 HAVING COUNT(sc)>=2,HAVING的作用和WHERE很像都是筛选,不过WHERE不能带聚合函数的(比如SVG,SUM,COUNT等等)
2.5 SELECT sn,返回的自然就只有’吴克’了
所以WHERE sn IN (吴克),sn的取值只能是吴克,那么2这一步最后得到吴克所有科目的成绩
sn | cn | sc |
---|---|---|
吴克 | 语文 | 100 |
吴克 | 物理 | 7 |
吴克 | 体育 | 5 |
3. 再GROUP BY sn
4. 最后SELECT sn,AVG(sc),我们就可以球的我们这位挂课数目在2门以上的吴克同学的平均分了
sn | AVG(sc) |
---|---|
吴克 | 37.3333 |
Reference:
[1]十步完全理解 SQL
[2]可以这样去理解group by和聚合函数