分组排序取前n行数据

亦凉 2024-03-31 16:20 118阅读 0赞

###### 数据库笛卡尔积 ######

实际应用中经常需要两张表，甚至多张表关联，表一旦关联就会产生笛卡尔积。  
两张表关联时，因为两张表要融合为一张表，所以两张表的每一列都会链接起来。  
例：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连接查询