mysql - SQL选择组查询

Question

下面是我的表格

表1

+--------+----------+---------+  
| amount | make     | product |  
+--------+----------+---------+  
|    100 | Nokia    | Mobiles |   
|    300 | Samesung | Mobiles |   
|    700 | Micromax | Mobiles |   
|   1000 | Karbonn  | Mobiles |   
|    500 | Lava     | Mobiles |   
|    100 | Floyer   | Gift    |   
|    500 | Arichies | Gift    |   
|    300 | Feeling  | Gift    |   
+--------+----------+---------+  

现在我想显示每个产品的两个最高金额...

所以我想构建单个 SQL 查询，它给我的结果如下..

+--------+----------+---------+  
| amount | make     | product |  
+--------+----------+---------+  
|   1000 | Karbonn  | Mobiles |   
|    700 | Micromax | Mobiles |   
|    500 | Arichies | Gift    |   
|    300 | Feeling  | Gift    |   
+--------+----------+---------+  

请帮助我建立这样的查询..

Answer 1

您可以使用此解决方案根据 amount 检索“分组最大值” :

SELECT a.*
FROM Table1 a
INNER JOIN Table1 b ON a.product = b.product AND a.amount <= b.amount
GROUP BY a.amount, a.product
HAVING COUNT(*) <= 2

只需更改 2无论您要为每个产品检索多少 top 行。

如果您想检索每个产品的最低两行，您可以简单地更改 <=登录 INNER JOIN到 >= .

您可以在这里摆弄这个解决方案:SQL-Fiddle Demo