在(分区…在mysql 5.7中)转换rank() | 码农俱乐部 - Golang中国
Go语言中文社区 · · 2952 次点击 · · 开始浏览这是一个创建于 的文章,其中的信息可能已经有所发展或是发生改变。
- 请尽量让自己的回复能够对别人有帮助
- 支持 Markdown 格式, **粗体**、~~删除线~~、
`单行代码`
- 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
- 图片支持拖拽、截图粘贴等方式上传
首先,请注意一些有关原始查询的注意事项:
it is not valid SQL; you have an aggregate function in the
select
clause (max(t.hour)
), but nogroup by
clausethe
order by
clause ofrank()
is not optimized: it starts with columns that are part of thepartition by
clause, which is unecessary (we already know that all rows in the partition have the same values for these columns)
因此,我假设您要重写的查询是:
select
t.storeid as storeid,
t.artid as artid,
t.totamount,
t.hour,
rank() over(partition by t.storeid, t.artid order by t.hour desc) rn
from t
order by t.storeid, t.artid
Now, in MySQL 5.7, one method to emulate rank()
uses a correlated subquery:
select
t.storeid as storeid,
t.artid as artid,
t.totamount,
t.hour,
(
select 1 + count(*)
from t t1
where
t1.storeid = t.storeid
and t1.artid = t.artid
and t1.hour > t.hour
) as rn
from t
order by t.storeid, t.artid
注意,这实际上不如窗口函数有效,因为必须对原始表中的每一行执行子查询。其他解决方案通常涉及用户变量。
首先,请注意一些有关原始查询的注意事项:
it is not valid SQL; you have an aggregate function in the
select
clause (max(t.hour)
), but nogroup by
clausethe
order by
clause ofrank()
is not optimized: it starts with columns that are part of thepartition by
clause, which is unecessary (we already know that all rows in the partition have the same values for these columns)因此,我假设您要重写的查询是:
Now, in MySQL 5.7, one method to emulate
rank()
uses a correlated subquery:注意,这实际上不如窗口函数有效,因为必须对原始表中的每一行执行子查询。其他解决方案通常涉及用户变量。