我在这篇博客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版本
网上的资料很多, 但感觉不够简洁明了, 我就自己写一下好了
1SELECT * FROM 2( 3SELECT 4@rn:= CASE WHEN @securityid = securityid THEN @rn + 1 ELSE 1 END AS rn, 5@securityid:= securityid as securityid, 6volume, date 7FROM 8(SELECT * from us_historicaldaily WHERE DATE <= '2019-05-16' ORDER BY securityid, date DESC) a 9,(SELECT @rn=0, @securityid=0) b 10)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关键字初始化, 但我还是喜欢用表.
结果如下
