访问 2013 - 查询未返回正确的结果数

Question

我正在尝试获取下面的查询，以返回每个PlayerID的两个最低PlayedTo结果。

select
    x1.PlayerID, x1.RoundID, x1.PlayedTo
from P_7to8Calcs as x1
where
    (
    select count(*)
    from P_7to8Calcs as x2
    where x2.PlayerID = x1.PlayerID
    and x2.PlayedTo <= x1.PlayedTo
    ) <3
order by PlayerID, PlayedTo, RoundID;

不幸的是，目前当最低分数之一并列时，它不会返回结果。此处 http://sqlfiddle.com/#!3/4a9fc/13 数据集和代码的副本。

PlayerID 47 只返回一个结果，因为有两个不同的RoundID并列第二低的PlayedTo。对于我试图计算的内容，它返回这两个中的哪一个并不重要，因为我只需要知道数字是什么，但对于报告，理想情况下我需要知道具有最新日期的那个。

查询的另一个小问题是运行所需的时间。在 Access 中运行 83 条记录大约需要 2 分钟，但在数据库完全启动并运行时，它需要在大约 1000 条记录上运行。

任何帮助将不胜感激。

Answer 1

通过将DatePlayed添加到内部排序来解决平局（无论如何，您都想要日期最新的排序）：

select
    x1.PlayerID, x1.RoundID
    , x1.PlayedTo
from P_7to8Calcs as x1
where
    (
    select count(*)
    from P_7to8Calcs as x2
    where x2.PlayerID = x1.PlayerID
    and (x2.PlayedTo < x1.PlayedTo
         or x2.PlayedTo = x1.PlayedTo
            and x2.DatePlayed >= x1.DatePlayed
         )
    ) <3
order by PlayerID, PlayedTo, RoundID;

为了提高性能，请创建支持连接条件的索引。像这样：

create index P_7to8Calcs__PlayerID_RoundID on P_7to8Calcs(PlayerId, PlayedTo);

注意：我使用了您的 SQLFiddle，因为我在这里没有可用的 Acess。

---

编辑：如果索引不能充分提高性能，您可能需要使用窗口函数尝试以下查询（避免嵌套子查询）。它适用于您的 SQLFiddle，但我不确定 Access 是否支持此功能。

select x1.PlayerID, x1.RoundID, x1.PlayedTo
from (
  select PlayerID, RoundID, PlayedTo
  , RANK() OVER (PARTITION BY PlayerId ORDER BY PlayedTo, DatePlayed DESC) AS Rank
  from P_7to8Calcs
  ) as x1
where x1.RANK < 3
order by PlayerID, PlayedTo, RoundID;

有关文档，请参阅 OVER 子句和排名函数。