将数据库结果过滤到查找列中每个值的前n条记录 [英] Filtering Database Results to Top n Records for Each Value in a Lookup Column

问题描述

假设我的数据库中有两个表。

 表：类别
 ID | CategoryName 
 01 | CategoryA 
 02 | CategoryB 
 03 | CategoryC 
  

  TABLE：CategoriesAndNumbers 
 CategoryType | Number 
 CategoryA | 24 
 CategoryA | 22 
 CategoryC | 105 
 .....（20,000条记录）
 CategoryB | 3 
  

现在，如何过滤掉这些数据？所以，我想知道每个类别中3个最小的数字，并删除其余的。最终结果将是这样：

  TABLE：CategoriesAndNumbers 
 CategoryType | Number 
 CategoryA | 2 
 CategoryA | 5 
 CategoryA | 18 
 CategoryB | 3 
 CategoryB | 500 
 CategoryB | 1601 
 CategoryC | 1 
 CategoryC | 4 
 CategoryC | 62 
  

现在，我可以得到所有类别之间最小的数字，

编辑：我使用的是Access，这里是我的代码到目前为止

  SELECT TOP 10 cdt1.sourceCounty，cdt1.destCounty，cdt1.distMiles 
从countyDistanceTable为cdt1，countyTable 
 WHERE cdt1.sourceCounty = countyTable.countyID 
 ORDER BY cdt1.sourceCounty，cdt1.distMiles，cdt1.destCounty 
  
 
 
 
  EDIT2： Remou，这里是工作查询，解决我的问题。谢谢！ 
  DELETE 
 FROM categoriesAndNumbers a 
 WHERE a.Number NOT IN 
 SELECT Top 3 [Number] 
 FROM CategoriesAndNumbers b 
 WHERE b.CategoryType = a.CategoryType 
 ORDER BY [Number]）
  
解决方案
您可以使用：
  SELECT a.CategoryType，a.Number 
 FROM CategoriesAndNumbers a 
 WHERE a.Number IN（
 SELECT Top 3 [Number] 
 FROM CategoriesAndNumbers b 
 WHERE b.CategoryType = a.CategoryType 
 ORDER BY [Number]）
 ORDER BY aategory类别
  
这样做的困难是Jet / ACE Top会在它们存在的地方选择重复的值，所以你不一定会得到三个值，但是如果有连接的话，就会更多。如果存在一个键字段，通常可以解决这个问题：
  WHERE a.Number IN（
 SELECT Top 3 [Number] 
 FROM CategoriesAndNumbers b 
 WHERE b.CategoryType = a.CategoryType 
 ORDER BY [Number]，[KeyField]）
  
但是，我不认为这将有助于在这种情况下，因为外表将包括领带。
 
Let's say I have two tables in my database.
TABLE:Categories
ID|CategoryName
01|CategoryA
02|CategoryB
03|CategoryC
and a table that references the Categories and also has a column storing some random number.
TABLE:CategoriesAndNumbers
CategoryType|Number
   CategoryA|24
   CategoryA|22
   CategoryC|105
   .....(20,000 records)
   CategoryB|3
Now, how do I filter out this data?  So, I want to know what the 3 smallest numbers are out of each category and delete the rest.  The end result would be like this:
TABLE:CategoriesAndNumbers
CategoryType|Number
   CategoryA|2
   CategoryA|5
   CategoryA|18
   CategoryB|3
   CategoryB|500
   CategoryB|1601
   CategoryC|1
   CategoryC|4
   CategoryC|62
Right now, I can get the smallest numbers between all the categories, but I would like each category to be compared individually.

EDIT: I'm using Access and here's my code so far
SELECT TOP 10 cdt1.sourceCounty, cdt1.destCounty, cdt1.distMiles
FROM countyDistanceTable as cdt1, countyTable
WHERE cdt1.sourceCounty = countyTable.countyID
ORDER BY cdt1.sourceCounty, cdt1.distMiles, cdt1.destCounty
EDIT2: Thanks to Remou, here would be the working query that solved my problem.  Thank you!
DELETE
FROM CategoriesAndNumbers a
WHERE a.Number NOT IN (
    SELECT Top 3 [Number] 
    FROM CategoriesAndNumbers b 
    WHERE b.CategoryType=a.CategoryType 
    ORDER BY [Number])

解决方案
You could use something like:
SELECT a.CategoryType, a.Number
FROM CategoriesAndNumbers a
WHERE a.Number IN (
    SELECT Top 3 [Number] 
    FROM CategoriesAndNumbers b 
    WHERE b.CategoryType=a.CategoryType 
    ORDER BY [Number])
ORDER BY a.CategoryType
The difficulty with this is that Jet/ACE Top selects duplicate values where they exist, so you will not necessarily get three values, but more, if there are ties. The problem can often be solved with a key field, if one exists :
WHERE a.Number IN (
    SELECT Top 3 [Number] 
    FROM CategoriesAndNumbers b 
    WHERE b.CategoryType=a.CategoryType 
    ORDER BY [Number], [KeyField])
However, I do not think it will help in this instance, because the outer table will include ties.

                        
这篇关于将数据库结果过滤到查找列中每个值的前n条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！