分组排序取前n行数据

数据库笛卡尔积

实际应用中经常需要两张表，甚至多张表关联，表一旦关联就会产生笛卡尔积。
两张表关联时，因为两张表要融合为一张表，所以两张表的每一列都会链接起来。
例：A表和B表，A表有10行10列，B表有8行8列。则B表里的每一行都会跟A表的每一行连起来，也就是AB表关联，会产生80行18列数据。

SQL执行顺序

1. form
   首先对from子句中的前两个表执行一个笛卡尔乘积，生成虚拟表vt1（选择相对较小的表做基础表）
2. on
   on中的表达式应用到vt1中各行，筛选出所有满足条件的行，生成虚拟表vt2
3. join
   如果为outer join那么这一步将添加外部行，left outer join把左表在第二步中过滤的添加进来，如果是right outer join把右表在第二步过滤的行添加进来，生成虚拟表vt3
   如果form子句表的数目大于两个，将vt3和第三个表连接从而计算笛卡尔乘积，即重复1-3步，得到新的虚拟表vt3
4. where
   对vt3筛选,生成虚拟表vt4（先执行on,后执行where）
5. group by（开始可以使用select中别名，后面语句都可以使用）
   将一样的值组成一组，得到虚拟表vt5。后面的步骤只能得到分组的列，或聚合函数。
6. avg、sum…
   使用聚合函数，为vt5生成超组，生成vt6
7. having
   对已分组的数据筛选，生成vt7
8. select
   筛选在select中出现的列，生成vt8
9. distinct
   对vt8去重，生成vt9
10. order by
    按照排序条件排序，返回一个游标
11. limit
    应用limit，生成vt10返回结果。

所有的查询语句都是从form开始，每个步骤都会为下个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。
MySQL中select执行顺序
开始->from子句->where子句->group by子句->having子句->select子句->order by子句->limit子句->结果

子查询

基本执行逻辑

对于外部查询返回的每一行数据，内部查询都要执行一次。
外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录，然后外部查询根据返回的记录做出决策。

eg：查找工资大于同职位的平均工资的员工信息。

使用关联子查询：
select * from emp a where sal >
(select avg(sal) from emp b where a.job = b.job)
分析：
第一步先执行外层查询，即先执行select * from emp a；结果为整张表。
第二步因为子查询中连接这个表，将第一条记录转到子查询，查找计算子表中所有与第一条记录相同的职位的平均工资，返回给外层。
第三步将平均工资与第一条记录工资对比，符合条件留下。
然后循环123步，直到外表循环结束（会不会重复计算同职位的平均薪资？不会，SQL内部已经优化）。
分组排序取topN

eg：按照课程分组，查询每个课程最高的两个成绩

方法一：使用关联子查询：
select course,score from table a where 2>
(select count(1) from table b where a.score < b.score and a.course = b.course)
order by a.course,a.score desc;
分析：
第一步先执行外层select course,score from table a；结果为整表。
第二步将表的第一条记录传给内部子查询，筛选相同课程并且大于外部第一条分数的记录的个数，返给外层。
第三步保留个数小于二的外层记录（数量为0：表示外部记录为该课程最大的分数，数量为1：表示外部记录为该课程分数第二大）。
循环123步，直到外部循环完毕，找出所有课程分数最大的两个成绩。
按照排序条件进行排序。
问题：如果最高分有多个相同的，则有的课程出现多于两条的数据。
解决：
方法二：使用join连接查询