我的问题类似于SQL select Group query。但是模式有变化,我希望得到不同的结果,如下所述。给定链接的解决方案没有给出正确的解决方案。你可以使用SQL fiddle来解决这个问题。
下面是我的表格
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 |
+--------+----------+---------+ 现在我想要显示每个产品的两个最低金额,如果金额相同,则根据make列的字母升序的任何人…
因此,我想构建一个SQL查询,它给出如下结果。
+--------+----------+---------+
| amount | make | product |
+--------+----------+---------+
| 100 | Nokia | Mobiles |
| 300 | Lava | Mobiles |
| 100 | Floyer | Gift |
| 300 | Feeling | Gift |
+--------+----------+---------+ 请帮助我建立这样的查询..
发布于 2012-09-26 14:54:27
这应该会对你有帮助..
第一个有个bug,现在已经更新了。
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
发布于 2012-09-26 14:48:41
试试这个:
select * from table1 ORDER BY amount DESC LIMIT 2;发布于 2012-09-26 14:54:13
SELECT amount, make,product
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY product ORDER BY amount) AS RowID,*
FROM Table1) RESULT
WHERE RowID <= 2这在PostgreSQL中工作得很好,mysql不支持窗口函数,通过在mysql refer中获得类似窗口函数的工作
https://stackoverflow.com/questions/12595841
复制相似问题