MySQL:获取表中每个组的第n个最大值 [英] MySQL: Get nth highest value for each group in a table
问题描述
免责声明:我对此很陌生，因此如果我使用不正确的术语，我会提前道歉-如果有任何不清楚的地方，我将很乐意澄清.
Disclaimer: I'm pretty new to this so I apologize in advance if I use incorrect terminology - I'll be happy to clarify if anything isn't clear.
说我有一个像这样的表，该表按商店位置和部门跟踪所有订单:
Say I have a table like this, that tracks all orders by the store location and department:
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
... ... ... ...
如果我想要每个部门，每个商店的最高销售额，我认为那只是
If I wanted the highest sale in each department, in each store, I think that's just
SELECT Store, Dept, max(Amount)
FROM mytable
GROUP BY Store, Dept
但是，如果我想要每个部门的第二高销售额怎么办?换句话说，我想要一个从上表中产生以下结果的查询:
But what if I want the 2nd highest sale in each department? In other words, I want a query that produces the following results from the above table:
STORE DEPT ORDER AMOUNT
--------------------------------------------------
NYC Clothing P00001 $30
NYC Housewares P00013 $140
CHI Clothing P00022 $20
CHI Housewares P00033 $125
请注意，我不一定要第二高的UNIQUE金额-如果与最高价值并列，就像NYC-Housewares一样，我需要返回该价值(而不是$ 125).
Note that I don't necessarily want the second highest UNIQUE amount - if there's a tie for the top value, as there is in NYC-Housewares, I need that value returned (not $125).
在SQL Server中，我了解到可以使用PARTITION BY在组中进行排序，然后选择所需的排名，但是似乎没有相同的语法适用于MySQL.我在网上发现的类似问题涉及使用LIMIT，但我还没有找到适合我情况的解决方案.
In SQL Server I've learned I can use PARTITION BY to order within the groups and then select the rank that I want, but it doesn't appear that the same syntax works for MySQL. Similar questions I've found on the web involve using LIMIT but I haven't found a solution that works for my situation.
任何帮助将不胜感激.
Any help would be greatly appreciated.
推荐答案
我希望有更好的方法，但是可以通过相交或不相交两个子查询来实现:
I hope there's a better way, but you can get this by intersecting or not-ing two subqueries:
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;
这篇关于MySQL:获取表中每个组的第n个最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
