MS Access中获取分组内第一条数据的SQL语句需求

Hey there! Based on your example, it looks like you want the record with the smallest ID for each unique Name group in your tblGroup table. Here are two straightforward ways to achieve this in MS Access:

方法1：使用子查询和内连接 #

This approach first finds the minimum ID for each group, then joins back to the original table to get the full record:

SELECT t.ID, t.Name
FROM tblGroup t
INNER JOIN (
    -- 子查询：获取每个Name分组的最小ID
    SELECT Name, MIN(ID) AS MinID
    FROM tblGroup
    GROUP BY Name
) subGroups ON t.ID = subGroups.MinID AND t.Name = subGroups.Name
ORDER BY t.ID;

方法2：使用WHERE子查询 #

You can also use a correlated subquery in the WHERE clause to filter only records where the ID is the smallest for its group:

SELECT DISTINCTROW t.ID, t.Name
FROM tblGroup t
WHERE t.ID = (
    SELECT MIN(ID)
    FROM tblGroup
    WHERE Name = t.Name
)
ORDER BY t.ID;

预期结果 #

Both queries will return exactly what you're looking for:

Just a quick note: If your definition of "first record" ever changes (like based on a creation date instead of ID), you can replace MIN(ID) with MIN(CreationDate) (or whatever field defines the order) and adjust the join/where clause accordingly.

内容的提问来源于stack exchange，提问作者Christian Phillippi