问题描述
在Mysql中,我想从每个类别中选择最下面的2个项目
In Mysql, I want to select the bottom 2 items from each category
Category Value
1 1.3
1 4.8
1 3.7
1 1.6
2 9.5
2 9.9
2 9.2
2 10.3
3 4
3 8
3 16
给我
Category Value
1 1.3
1 1.6
2 9.5
2 9.2
3 4
3 8
在我从sqlite3迁移之前,我必须首先从每个类别中选择一个最低的类别,然后排除与之相关的任何内容,我必须再次从每个类别中选择一个最低的类别.然后,等于该类别中新的最低或更少的任何事物都会获胜.如果出现平局,这也将选择2个以上,这很烦人...它的运行时间也很长.
Before I migrated from sqlite3 I had to first select a lowest from each category, then excluding anything that joined to that, I had to again select the lowest from each category. Then anything equal to that new lowest or less in a category won. This would also pick more than 2 in case of a tie, which was annoying... It also had a really long runtime.
我的最终目标是计算一个人进入该类别中最低的2个之一(还有一个名称字段)的次数,这是我不知道该怎么做的一部分.谢谢
My ultimate goal is to count the number of times an individual is in one of the lowest 2 of a category (there is also a name field) and this is the one part I don't know how to do.Thanks
推荐答案
您可以尝试以下方法:
SELECT * FROM (
SELECT c.*,
(SELECT COUNT(*)
FROM user_category c2
WHERE c2.category = c.category
AND c2.value < c.value) cnt
FROM user_category c ) uc
WHERE cnt < 2
它应该给您想要的结果,但是请检查性能是否还可以.
It should give you the desired results, but check if performance is ok.
这篇关于SQL查询以从每个类别中选择底部2的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!