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

本文是小编为大家收集整理的关于SQL选择组查询的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

下面是我的桌子

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

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

所以我想构建单个SQL查询,从而为我提供了下面的结果.

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

请帮助我构建此类查询.

推荐答案

您可以使用此解决方案基于amount:

来检索" group-wise mimust "
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中的登录更改为>=.

.

您可以在此处解决此解决方案:其他推荐答案

select product, make, amount, rnk
from (
  select l.product, l.make, l.amount, count(*) as rnk
  from table1 as l
  left join table1 as r
  on (r.product = l.product and l.amount <= r.amount) 
  group by l.product, l.make
) a
where rnk <= 2

请参阅此处的IDEEA和其他示例: http://www.xaprb.com/blog/2005/09/27/simulating-the-sql-row_number-function/

sql小提琴基于Zane bien测试数据.

其他推荐答案

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
ORDER BY a.amount desc

请参阅本文地址:https://www.itbaoku.cn/post/597660.html

问题描述

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

其他推荐答案

select product, make, amount, rnk
from (
  select l.product, l.make, l.amount, count(*) as rnk
  from table1 as l
  left join table1 as r
  on (r.product = l.product and l.amount <= r.amount) 
  group by l.product, l.make
) a
where rnk <= 2

see the ideea and other examples here: http://www.xaprb.com/blog/2005/09/27/simulating-the-sql-row_number-function/

and sql fiddle based on zane bien test data.

其他推荐答案

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
ORDER BY a.amount desc

Please refer to http://sqlfiddle.com/#!2/9ba82/1