为每组选择前 1 个

Question

我有一个 Access 数据库，其中包含一个表，其中包含有关我们排序的零件的信息。该表有一个自动编号 ID 字段和一个链接到另一个包含零件信息的表的 110ID。它还包含 sortDate、sortShift、排序、废弃和修复。我需要找出自上次发现每个 110ID 缺陷（未报废或修复）以来已对多少零件进行了分类。

问题是我不能保证信息会按时间顺序输入数据库。因此，我需要对“sortDate”大于最后一个缺陷的任何记录的“sorted”字段求和，或者“sortDate”与最后一个缺陷相同但更大的“sortShift”，或者使用自动编号 id 作为最后的手段如果“sortDate”和“sortShift”都匹配。

这是我当前正在使用的查询：

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];

问题是这非常慢。有没有更好的方法来实现这一点并产生更好的性能？

Answer 1

您可以使用 LEFT OUTER JOIN 或 NOT EXISTS 子查询来执行此操作，而不是使用所有这些子查询。我不经常使用 Access，因此如果这是 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]
    )