首先,请注意一些有关原始查询的注意事项:

selectmax(t.hour)group byorder byrank()partition by

因此,我假设您要重写的查询是:

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
rank()
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

注意,这实际上不如窗口函数有效,因为必须对原始表中的每一行执行子查询。其他解决方案通常涉及用户变量。