Mysql5.7版本实现row_number窗口函数的分组排序功能

我在这篇博客https://www.cnblogs.com/chendongblog/p/11887712.html中说过,

在 sql server中outer apply / cross apply 可以更高效率的实现跟row_number函数同等的功能

但mysql 5.7 不仅outer apply / across apply 没有, row_number也没有. 哭 !

听说mysql 8.0 版本也可以使用row_number函数了

但我们使用的是5.7版本

网上的资料很多, 但感觉不够简洁明了, 我就自己写一下好了

SELECT * FROM
(
SELECT 
@rn:= CASE WHEN @securityid = securityid THEN @rn + 1 ELSE 1 END AS rn,
@securityid:= securityid as securityid,
volume, date
FROM
(SELECT * from us_historicaldaily WHERE DATE <= '2019-05-16' ORDER BY securityid, date DESC) a
,(SELECT @rn=0, @securityid=0) b
)a WHERE rn <= 5

这里有几点解释下:

1. 这里对表分组的依据是securityid, 排序的依据是date

相当于有个指针在从上往下滑动, 需要一个用户变量@securityid来记录最近一次securityid的值,

然后跟当前行的securityid列做对比, 如果相等(@securityid = securityid) 说明当前在同一个分组中, @rn 递增1 ,

否则说明当前组已经变更了 @rn重新计数, 从1开始

2. a表中order by 是必须的, 因为只有排序的表从上往下遍历才有意义,

而且order by的字段顺序要相当于row_number函数的 partition by securityid order by date desc

由于sql的执行顺序, order by 排在select 之后, 所以order by语句必须写在a表中, 而不是整个sql的末尾(恰好mysql支持order by语句写在表中)

3. b表也是必须的, b表相当于在a表后面加两个字段初始化这两个变量的值, 也可以用set关键字初始化, 但我还是喜欢用表.

结果如下

Mysql5.7版本实现row_number窗口函数的分组排序功能