如何为另一个字段的每个值选择字段的TOP X结果？ [英] How to select TOP X results of a field for each value of another field?
问题描述
您好！
有些东西看起来很简单，但我仍然不明白该怎么做：
我需要从下表创建一个查询：
表1：
P：日期：CountOfMeetings：
1 01/12 4
1 02/12 0
1 03/12 2
1 04/12 0
1 05/12 3
1 06/12 2
1 07/12 2
2 01/12 3
2 02/12 2
2 07/12 3
2 12/12 1
2 14 / 12 3
2 15/12 4
3 3/12 2
3 4/12 4
3 5/12 0
3 9/12 2
3 10/12 6
3 11/12 5
查询应该为每个P（人）占用前3个日期，因此结果应如下所示：
P：日期：CountOfMeetings：
1 01/12 4
1 02/12 0
1 03/12 2
2 01/12 3
2 02/12 2
2 07/12 3
3 3/12 2
3 4/12 4
3 5/12 0
（CountOfMeetings字段只是一个示例字段）
我赞美你的时间。
Hello!
There''s something that seems quite simple to do, but I still don''t understand how to do it:
I need to create a query from the following table:
Table1:
P: Date: CountOfMeetings:
1 01/12 4
1 02/12 0
1 03/12 2
1 04/12 0
1 05/12 3
1 06/12 2
1 07/12 2
2 01/12 3
2 02/12 2
2 07/12 3
2 12/12 1
2 14/12 3
2 15/12 4
3 3/12 2
3 4/12 4
3 5/12 0
3 9/12 2
3 10/12 6
3 11/12 5
The query should take TOP 3 Dates for each P(person), so the result should be like the following:
P: Date: CountOfMeetings:
1 01/12 4
1 02/12 0
1 03/12 2
2 01/12 3
2 02/12 2
2 07/12 3
3 3/12 2
3 4/12 4
3 5/12 0
(The CountOfMeetings field is just an example field)
I appriciate your time.
推荐答案
你会需要使用子查询。但即使在使用子查询之前，您也需要修复数据以支持它。首先，你需要一种独特的键，即使它是一个自动编号。第二，你的约会字段不是约会，我不知道它是什么格式。第三，你的前三名不是最重要的3，听起来更像是你在寻找前3而不是前3。
You''ll need to use a subquery. But even before you can use a subquery, you need to fix your data to support it. First off, you need a unique key of some sort, even if it''s an autonumber. Second, your "date" field isn''t a date, I can''t figure out what format it is. Third, your "top 3" isn''t top 3 of anything, it sounds more like you''re looking for first 3 rather than top 3.
我有一个独特的钥匙，我的日期是真实日期，我给出了一个简单的例子，以免过多细节。
我需要3个最低日期。
如何使用子查询为了得到结果？
I have a unique key, my date is a real date, I just gave a simplified example in order not to overload with too much detail.
I need the 3 lowest dates.
How can I use the subquery in order to get the result?
你所做的是对表进行别名，以便你可以从子查询中访问它。子查询只需要返回按日期排序的前3个ID，这样您就可以从主查询中选择这些ID。
What you do is alias the table so that you can access it from the subquery. The subquery just needs to return the top 3 IDs sorted by your date so you cam select those IDs from the main query.
这篇关于如何为另一个字段的每个值选择字段的TOP X结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
