免责声明:我对此很陌生,因此如果我使用不正确的术语,我会先道歉-如果有任何不清楚的地方,我将很乐意澄清。
假设我有一个这样的表,该表按商店位置和部门跟踪所有订单:
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金额-如果与最高价值并列,就像NYC-Housewares一样,我需要返回该价值(而不是$ 125)。
在SQL Server中,我了解到可以PARTITION BY
在组中进行排序,然后选择所需的等级,但是似乎没有相同的语法适用于MySQL。我在网上发现的类似问题涉及使用LIMIT,但我还没有找到适合我情况的解决方案。
任何帮助将不胜感激。
我希望有更好的方法,但是您可以通过相交或不相交两个子查询来实现:
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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句