MySQL之伪列实现与实践

-------------------------------------------------------------------------------------------------正文--------------------------------------------------------------------------------------------------------------
问题来源：基情
问题描述：看图说明一切



建表语句与模板数据：


实践环境： MySQL-5.7.7-rc

实践过程：
首先看到这个需求，第一时间想到的就是Oracle的分组统计分析，当然了，MySQL没有，所以先生成了数据，直观的看一看效果：


总共四组， 每一组都要选取成绩最好的前N(N=3)

直观的看，已有的ID，country，name，socre都无法作为where的条件去筛选出score最高的三个

那么跳出这些实际的数据， 如果以上图这种顺序的数据结构为基础，要实现这个需求的话，

可以用这样子的抽象描述来表达实际需求的意思： 以country的值为分组条件，每一组选第一行数据，第二行数据，...，第N行数据

那么在这种抽象描述里面， where的条件就可以做出来： 每一组选第一行数据，第二行数据，...， 第N行数据

so，可行方法就得出来了： 以country的值为分组条件，构建伪列，最终结果筛选前N行数据，伪列值<=N(N=3)

问题来了， MySQL的伪列怎么构造？

构造位列的思想也是靠自连接来完成，使用count(*)来充当伪列的计数器，然后附加上计数的规则，
简单的构造示例：

那么在这次的问题里面，这个伪列是有前提条件的：

1. 以country的值为分组条件，那么显然，在构造伪列的where条件里面，我们必须限定这个伪列的count(*)所在的范围必须是在同一个country里面 ，

添加clause1：连接条件为country

2. 要选取分数最高的N(N=3)个 ，所以生成这个伪列的序列号的count(*)的计算方式，也是一个限制条件，

之前的clause1已经把范围限定在了同一个country， 那么取分数最高，无非就是算一下比其他低的有多少，

比如以中国为例，想要最高分92的行作为伪列的第一行，代表着，92应该是<=(country=中国)的score的count

所以添加clause2：内表.score<=外表.score

分析完伪列构造的条件，那么就来看看实际构造的效果：


鼓掌撒花~按照每一个country为一组，根据score的大小成功构建了伪列

剩下，各位应该都知道怎么做了~加上 where rownum <=3即可~

最后贴上未经优化的强迫症SQL
点击( 此处 )折叠或打开
实际上MySQL对这个渣语句已经进行了优化：


目测是独立子查询往上提，果不其然：


------------------------------------------------------------------------------------------------全文完-----------------------------------------------------------------------------------------------------------

PS：伪列不错，灵活使用能够解决很多奇奇怪怪的需求~