access中每组sql的前n条记录 | access中每组sql的前n条记录

本文介绍了access中每组sql的前n条记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在制作一些跟踪考试成绩的软件.有多个用户，其详细信息存储在用户表中.然后有一个进度表，它跟踪带有日期和得分的用户的分数.

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

任何帮助将不胜感激.

推荐答案

一年前我也遇到过类似的问题:每组前 3 名，包括 0 名

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]

这篇关于access中每组sql的前n条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！