SQL选择查询帮助需要 [英] SQL select query help required

问题描述

我的问题类似于 SQL选择组查询。但是模式有变化，我想要不同的结果，如下所述。给定链接的解决方案不给我正确的解决方案。您可以使用 SQL小提示来解决此问题。

My question is similar to SQL select Group query. But there is change in schema and I want different result as mentioned below. the solutions of given link doesn't give me the proper solutions. You can use SQL fiddle to solve this.

以下是我的表格

Table1

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

现在，我想显示每个产品的两个最低金额，如果金额相同，任何人根据升序字母顺序...

Now I want to display the two lowest amount for each product and if the amount is same then anyone according to ascending alphabet order of make column...

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

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

+--------+----------+---------+  
| amount | make     | product |  
+--------+----------+---------+  
|    100 | Nokia    | Mobiles |   
|    300 | Lava     | Mobiles |   
|    100 | Floyer   | Gift    |   
|    300 | Feeling  | Gift    |   
+--------+----------+---------+ 

请帮我构建这样的查询。

Kindly help me to build such query..

推荐答案

帮助你。

第一个有一个错误，现在更新。

First one had a bug, it is updated now.

SELECT  t.*
FROM    (
    SELECT  @lim := 2,
            @cg := ''
    ) vars,
    (select * from Table1 order by product,amount, make)  t
WHERE   CASE WHEN @cg <> product THEN @r := @lim ELSE 1 END > 0
    AND (@r := @r - 1) >= 0
    AND (@cg := product) IS NOT NULL
ORDER BY
    product,amount, make

与提琴手一起玩得很开心：
http://sqlfiddle.com/#!2/bdd1a/115/0

Have fun with it and with the fiddler : http://sqlfiddle.com/#!2/bdd1a/115/0

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