select b.name, b.`Type`,br.UsageTime ,b.Location,br.updateTime ,br.benchStatus,
ROW_NUMBER () over(PARTITION by b.`name`,b.Location order by b.Location, b.Name, br.updateTime desc) as rk
from XX1 b left join 2XXX br on b.Name =br.benchName
) as ta where rk=1
order by location
PARTITION是根据后面的字段分组,分组后排序,仅取排序后需要=1的数据