访问中每个组sql的前n条记录 [英] Top n records per group sql in access
问题描述
我正在开发一些可以跟踪考试成绩的软件.有多个用户,其详细信息存储在用户表中.然后有一个进度表,该进度表跟踪带有日期和分数的用户的分数.
I am making some software that tracks the scores of a test. There are multiple users, the details of which are stored in a user table. There is then a progress table which tracks a score with the date and the user who's score it is.
我已经可以为所选的用户ID选择3条最新记录
I can already select the 3 most recent records for a chosen userID
SELECT TOP 3 Progress.LoginID, Progress.Score, Progress.[Date Taken]
FROM Progress
WHERE (((Progress.LoginID)=[Enter LoginID:]))
ORDER BY Progress.[Date Taken] DESC;
我可以显示按LoginID分组的所有记录
And I can show all the records grouped by LoginID
SELECT Progress.LoginID, Progress.Score, Progress.[Date Taken]
FROM Progress
GROUP BY Progress.LoginID, Progress.Score, Progress.[Date Taken];
我希望能够在一个查询中显示每个用户的3条最新记录,但我不确定如何使用嵌套查询/子查询来执行此操作.
I want to be able to show the 3 most recent records for each user in one query and I'm unsure of how to use nested queries/subqueries to do so.
用户表的字段名称为:
LoginID
Forename
Surname
DOB
Guardian Forename
Guardian Surname
Telephone Number
进度表的字段名称为:
ProgressID
LoginID
Score
Date Taken
任何帮助将不胜感激.
Any help would be appreciated.
推荐答案
I had a similar problem a year ago: Top 3 per group including 0
使用相同的方法,这将为每个LoginID返回最近的三个日期-如果相同的LoginID有附加日期,则您可能会获得三个以上的记录.
Using the same approach, this will return the latest three dates for each LoginID - you may get more than three records if there are tied dates for the same LoginID.
SELECT PR1.LogInID, PR1.Score, PR1.[Date Taken]
FROM Progress AS PR1
WHERE PR1.[Date Taken] IN (
SELECT TOP 3 PR2.[Date Taken]
FROM Progress PR2
WHERE PR2.LoginID = PR1.LoginID
ORDER BY PR2.[Date Taken] DESC
)
ORDER BY LoginID, [Date Taken]
这篇关于访问中每个组sql的前n条记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!