访问:查询崩溃

Question

我有以下查询（我们称其为query1）（在此处由erik von asmuth创建）：

SELECT PARTNERID
    ,NAME
    ,FIRST_NAME
    ,UID
    ,DATA_R
FROM MY_TABLE
WHERE MY_TABLE.[DATA_R] = (
        SELECT MAX(t.[DATA_R])
        FROM MY_TABLE AS t
        WHERE t.PARTNERID = MY_TABLE.PARTNERID
        )
ORDER BY PARTNERID;

MY_TABLE具有20000记录，并且是一个查询（即使名称可能建议相反），以下表格：

SELECT [MYTABLE_O].PARTNERID, [MYTABLE_O].NAME, [MYTABLE_O].FIRST_NAME, [MYTABLE_O].[Codice fiscale] AS CF, [MYTABLE_O].Date AS DATA_R
FROM [MYTABLE_O] LEFT JOIN [TO_EXCLUDE] ON [MYTABLE_O].[PARTNERID] = [TO_EXCLUDE].[PARTNERID]
WHERE ((([TO_EXCLUDE].PARTNERID) Is Null));

（我想排除表TO_EXCLUDE中的一些已经考虑的元素）。

当我运行查询时（query1）MS访问冻结。我如何避免/使其更高效和稳定？

我尝试在MYTABLE_O中索引PARTNERID和DATA_R

Answer 1

您可能必须编写子查询的结果：

SELECT PARTNERID, MAX([DATA_R]) AS MAXDATAR
FROM YourQuery
GROUP BY PARTNERID

到临时表，然后在查询中替换

FROM MY_TABLE AS t

FROM TempTable AS t