本文是小编为大家收集整理的关于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中的登录更改为>=.
.您可以在此处解决此解决方案:其他推荐答案 请参阅此处的IDEEA和其他示例: http://www.xaprb.com/blog/2005/09/27/simulating-the-sql-row_number-function/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
其他推荐答案
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
问题描述
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
更多相关问答
联系站长