代码之家 › 专栏 › 技术社区 › AdmSteck

为每个组选择前1名

greatest-n-per-group query-optimization ms-access sql

AdmSteck · 技术社区 · 14 年前

我有一个Access数据库,其中包含一个表,其中包含有关我们排序的部件的信息。此表有一个自动编号ID字段和一个110ID,它链接到另一个带有零件信息的表。它还包含sortDate、sortShift、sorted、scraped和repaired。我需要找出自从最后一个缺陷(没有报废或修理)被发现后,每个110ID有多少零件被分类。

问题是我不能保证这些信息将按时间顺序输入数据库。因此,我需要求和“sortDate”大于最后一个缺陷的任何记录的“sortDate”字段,或者“sortDate”与最后一个缺陷相同但“sortShift”更大的记录,或者在“sortDate”和“sortShift”都匹配的情况下使用autonumber id作为最后的手段。

这是我当前使用的查询:

SELECT SortInfo.[110ID], Sum(SortInfo.Sorted) AS SumOfSorted
FROM SortInfo
WHERE (
    ((SortInfo.sortdate)>(select top 1 dupe.sortdate from sortinfo as dupe where     (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc)))
    OR (((SortInfo.sortdate)=(select top 1 dupe.sortdate from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc)) 
        AND ((SortInfo.sortshift)>(select top 1 dupe.sortshift from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc))) 
    OR (((SortInfo.sortdate)=(select top 1 dupe.sortdate from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc)) 
        AND ((SortInfo.sortshift)=(select top 1 dupe.sortshift from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc)) 
        AND ((SortInfo.ID)>(select top 1 dupe.id from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc))
)
GROUP BY SortInfo.[110ID];

问题是这是非常缓慢的。有没有更好的方法来实现这一点,从而产生更好的性能?

1 回复 | 直到 14 年前

Tom H zenazn 14 年前

与使用所有这些子查询不同,您可以使用左外部联接或不存在子查询来执行此操作。我不怎么使用Access,所以如果访问不符合ANSI标准,那么您可能需要对这些进行调整。

SELECT
    SI.[110ID],
    SUM(SI.Sorted) AS SumOfSorted
FROM
    SortInfo SI
LEFT OUTER JOIN SortInfo SI2 ON
    SI2.Repaired <> 0 AND
    SI2.Scrapped <> 0 AND
    (
        SI2.SortDate > SI.SortDate OR
        (SI2.SortDate = SI.SortDate AND SI2.SortShift > SI.SortShift) OR
        (SI2.SortDate = SI.SortDate AND SI2.SortShift = SI.SortShift AND SI2.ID > SI.ID)
    )
WHERE
    SI2.ID IS NULL
GROUP BY
    SI.[110ID]

SELECT
    SI.[110ID],
    SUM(SI.Sorted) AS SumOfSorted
FROM
    SortInfo SI
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM
            SortInfo SI2
        WHERE
            SI2.Repaired <> 0 AND
            SI2.Scrapped <> 0 AND
            (
                SI2.SortDate > SI.SortDate OR
                (SI2.SortDate = SI.SortDate AND SI2.SortShift > SI.SortShift) OR
                (SI2.SortDate = SI.SortDate AND SI2.SortShift = SI.SortShift AND SI2.ID > SI.ID)
            )    
    GROUP BY
        SI.[110ID]
    )