使用MS Access获取行的第一个实例 [英] Get the first instance of a row using MS Access

问题描述

已编辑

我有这个查询，我想从表 petTable 中选择一个记录的第一个实例.

I have this query wherein I want to SELECT the first instance of a record from the table petTable.

SELECT id, 
    pet_ID, 
    FIRST(petName), 
    First(Description) 
FROM petTable 
GROUP BY pet_ID;

问题是我有大量记录，并且此查询太慢.我发现 GROUP BY 降低了查询速度.您是否有任何想法可以使此查询更快?还是更好的查询，其中我不需要使用 GROUP BY ?

The problem is I have huge number of records and this query is too slow. I discovered that GROUP BY slows down the query. Do you have any idea that could make this query faster? or better, a query wherein I don't need to use GROUP BY?

推荐答案

问题是我有大量记录，并且此查询太慢.我发现 GROUP BY 会降低速度查询下.您有什么想法可以使查询速度更快吗?"

"The problem is I have huge number of records and this query is too slow. I discovered that GROUP BY slows down the query. Do you have any idea that could make this query faster?"

以及pet_ID 上的索引，然后创建并测试此查询:

And an index on pet_ID, then create and test this query:

SELECT pet_ID, Min(id) AS MinOfid
FROM petTable
GROUP BY pet_ID;

一旦该查询生效，您可以将其重新连接到原始表---然后它将仅选择基于id匹配的原始行，并且您可以从那些匹配的行中检索所需的其他字段

Once you have that query working, you can join it back to the original table --- then it will select only the original rows which match based on id and you can retrieve the other fields you want from those matching rows.

SELECT pt.id, pt.pet_ID, pt.petName, pt.Description
FROM
    petTable AS pt
    INNER JOIN
    ( 
        SELECT pet_ID, Min(id) AS MinOfid
        FROM petTable
        GROUP BY pet_ID
    ) AS sub
    ON pt.id = sub.MinOfid;

这篇关于使用MS Access获取行的第一个实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！