SQL查询以从每个类别中选择底部2 [英] SQL Query to select bottom 2 from each category

问题描述

在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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！