我的问题类似于 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与提琴手一起玩得很开心: sqlfiddle/#!2/bdd1a/115/0
Have fun with it and with the fiddler : sqlfiddle/#!2/bdd1a/115/0
更多推荐
SQL选择查询帮助需要
发布评论