MYSQL没有RANK PARTITION能力。因此,无法通过排序后读取rs=1的方式得到最大值的对应的其他参数。
百度后得到了一个方案如下:
select * from
(
select ta.a1,ta.a2,ta.utilization,count(*) as rank from
(
select
a1,
a2,
utilization
from table_a
)ta
left join
(
select
a1,
a2,
utilization
from table_a
)tb
ON ta.a1 = tb.a1
AND ta.utilization <= tb.utilization
GROUP BY ta.a1,ta.a2,ta.utilization
)s
where s.rank =1
order by utilization desc
重点:MYSQL支持非等值 JOIN,a1等于原来的partition的参数;group by必须带上所有参数,不纳入group by的会取随机值。
ON ta.a1 = tb.a1
AND ta.utilization <= tb.utilization
GROUP BY ta.a1,ta.a2,ta.utilization