sql - 在多对多查询中选择 TOP 1

Question

我有一个失败的简单多对多模式，如下所示:alt text http://img406.imageshack.us/img406/8207/partialschema.png

我想做的是选择具有任意条件的所有玩家，并且我还想选择他们最近的比赛(如果他们参加过一场比赛)。

我成功做到的是:

SELECT tblPlayer.PlayerId, tblPlayer.Surname, tblPlayer.Forename,
(SELECT TOP 1 tblMatch.HomeClub + ' v ' + tblMatch.OpponentClub + ' ' + tblMatch.AgeGroup + ' (' + CONVERT(VARCHAR, tblMatch.MatchDateTime, 103) + ')'
    FROM tblAppearance
    INNER JOIN tblMatch ON tblAppearance.MatchID = tblMatch.MatchID
    WHERE tblAppearance.PlayerID = tblPlayer.PlayerID 
    ORDER BY MatchDateTime DESC) AS Match
FROM tblPlayer
LEFT JOIN tblAppearance ON tblAppearance.PlayerId = tblPlayer.PlayerId
LEFT JOIN tblMatch ON tblMatch.MatchId = tblAppearance.MatchId
WHERE tblPlayer.Forename LIKE '%rob%' AND tblPlayer.Surname LIKE '%white%'
ORDER BY tblPlayer.Surname, tblPlayer.Forename, tblPlayer.DOB, tblMatch.MatchDateTime DESC

问题在于，这会选择玩家参加过的所有比赛，而不仅仅是他们最近的一场比赛。我知道这应该很简单，但我似乎无法获得正确的语法。

此外，我宁愿将匹配表中的单独列作为单独的列返回，而不是作为格式化的 block 返回。

对更多信息请求的答复:

是的，有一个 MatchDateTime 列，我打算用它来排序。

是的，我确实想要尚未参加任何比赛的玩家，左连接是故意的。

Answer 1

对于这些类型的问题(在一组中获得顶行)，我在性能和可维护性方面都取得了最大的成功，使用 ROW_NUMBER()和一个CTE 。该模式很简单:CTE 选择您想要的列，并为每个组中的 ROW_NUMBER() 添加一个附加列(当然，按您想要的顺序排序)。然后，查询的后 CTE 部分将结果限制为 ROW_NUMBER() 为 1 的结果。

像这样:

WITH cte AS
(
    SELECT tblPlayer.PlayerId, Surname, Forename, HomeClub, OpponentClub, AgeGroup, MatchDateTime, DOB,
        ROW_NUMBER () OVER (PARTITION BY tblPlayer.PlayerId ORDER BY MatchDateTime DESC) AS RowNum
    FROM tblPlayer
        LEFT JOIN tblAppearance ON tblAppearance.PlayerId = tblPlayer.PlayerId
        LEFT JOIN tblMatch ON tblMatch.MatchId = tblAppearance.MatchId
    WHERE Forename LIKE '%rob%' AND Surname LIKE '%white%'
)
SELECT PlayerId, Surname, Forename, HomeClub, OpponentClub, AgeGroup, MatchDateTime, DOB
FROM cte
WHERE RowNum = 1
ORDER BY Surname, Forename, DOB, MatchDateTime

请注意，我并不假设任何 ID 的排序方式与 MatchDateTime 的排序方式相同 - 有很多原因(例如提前调度)导致该假设可能不成立。但是，如果外观 ID 的排序与日期相同，则上面的查询会更加高效，因为您无需执行任何连接即可找到您要查找的 MatchID。

请注意，如果您有大量玩家(100,000+)并且您经常运行此查询，则您需要在此处进行优化，因为每次运行此查询时您都会进行表扫描玩家表的列表以支持您的 LIKE 过滤器。如果是这种情况，您可能需要在姓氏、名字上创建覆盖索引，并让 SQL 分阶段运行查询:首先使用覆盖索引过滤玩家记录，然后进行连接，最后拉出其他玩家聚集索引中的列。通常很难让 SQL 执行这样的计划(您可能需要一个临时表来存储中间结果)，但对于非常大的表来说，性能的胜利是值得的。如果您的玩家人数较少，请忽略上一段。 :-)