SQL选择组查询 [英] SQL select group query

问题描述

以下是我的表格

Table1

  ------ + ---------- + --------- + 
 |金额| make |产品| 
 + -------- + ---------- + --------- + 
 | 100 |诺基亚|移动| 
 | 300 | Samesung |移动| 
 | 700 | Micromax |移动| 
 | 1000 | Karbonn |移动| 
 | 500 |熔岩|移动| 
 | 100 | Floyer |礼品| 
 | 500 | Arichies |礼品| 
 | 300 |感觉|礼品| 
 + -------- + ---------- + --------- + 
  

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

所以我想构建单SQL查询，其结果如下所示。

  + -------- + ------ ---- + --------- + 
 |金额| make |产品| 
 + -------- + ---------- + --------- + 
 | 1000 | Karbonn |移动| 
 | 700 | Micromax |移动| 
 | 500 | Arichies |礼品| 
 | 300 |感觉|礼品| 
 + -------- + ---------- + --------- + 
  

请帮我构建这样的查询。

解决方案

使用此解决方案根据金额：

$ c
检索 group-wise maximum pre> 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 更改为您希望每个产品检索的许多顶部行即可。

如果您要检索每个产品的最低两行，您只需更改< = 登录 INNER JOIN 至> = 。

您可以在此处解决此解决方案： SQL-Fiddle演示

Below is my Table

Table1

+--------+----------+---------+  
| 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    |   
+--------+----------+---------+  

Now I want to display the two highest amount for each product...

So I want to build single SQL query which gives me result as below..

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

Kindly help me to build such query..

解决方案

You can use this solution to retrieve the "group-wise maximum" based on the 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

Simply change the 2 to however many of the top rows you want to retrieve per product.

If you wanted to retrieve the lowest two rows per product, you can simply change the <= sign in the INNER JOIN to a >=.

You can fiddle around with this solution here: SQL-Fiddle Demo

这篇关于SQL选择组查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！