MySQL:获取表中每个组的第n个最高值

Question

免责声明:我对此很陌生，所以如果我使用了不正确的术语，我会提前道歉 - 如果有任何不清楚的地方，我很乐意澄清。

假设我有一个这样的表，它按商店位置和部门跟踪所有订单:

STORE          DEPT          ORDER          AMOUNT
--------------------------------------------------   
NYC            Clothing      P00001         $30
NYC            Clothing      P00002         $25
NYC            Clothing      P00003         $40
...            ...           ...            ...
NYC            Housewares    P00011         $140
NYC            Housewares    P00012         $125
NYC            Housewares    P00013         $140
...            ...           ...            ...
CHI            Clothing      P00021         $30
CHI            Clothing      P00022         $20
CHI            Clothing      P00023         $20
...            ...           ...            ...
CHI            Housewares    P00031         $180
CHI            Housewares    P00032         $110
CHI            Housewares    P00033         $125
...            ...           ...            ...

如果我想要每个部门、每个商店的最高销售额，我认为这只是

SELECT Store, Dept, max(Amount)
FROM mytable
GROUP BY Store, Dept

但是如果我想要每个部门的第二高销售额怎么办？换句话说，我想要一个从上表中产生以下结果的查询:

STORE          DEPT          ORDER          AMOUNT
--------------------------------------------------
NYC            Clothing      P00001         $30
NYC            Housewares    P00013         $140
CHI            Clothing      P00022         $20
CHI            Housewares    P00033         $125

请注意，我不一定想要第二高的 UNIQUE 金额 - 如果最高值(value)存在平局，就像在 NYC-Housewares 中一样，我需要返回该值(value)(不是 125 美元)。

在 SQL Server 中，我了解到我可以使用 PARTITION BY 在组内进行排序，然后选择我想要的等级，但似乎同样的语法不适用于 MySQL。我在网上发现的类似问题涉及使用 LIMIT 但我还没有找到适合我的情况的解决方案。

如有任何帮助，我们将不胜感激。

Answer 1

我希望有更好的方法，但您可以通过交叉或不交叉两个子查询来实现:

Select mytable.Store, mytable.Dept, mytable.Order, mytable.Amount
from mytable m
inner join 
  (Select Amount from mytable n where m.store = n.store and m.dept = n.dept order by Amount desc limit 2) as high_enough
  on mytable.Amount = high_enough.Amount
left join 
   (Select Amount from mytable n where m.store = n.store and m.dept = n.dept order by Amount desc limit 2) as too_high
  where too_high.Amount is null
group by Store, Dept;