在SELECT查询中防止重复计数

Question

我有以下查询，如您所见，它执行多个Count(CompetitorID)调用。这是一个性能问题，还是SQL Server2008“缓存”了Count？如果这是一个性能问题，是否可以存储Count以防止多次查找？

SELECT EventID,Count(CompetitorID) AS NumberRunners,
    CASE WHEN Count(CompetitorID)<5 THEN 1
         WHEN Count(CompetitorID)>=5 AND Count(CompetitorID)<=7 THEN 2
         ELSE 3 END AS NumberPlacings
FROM Comps
GROUP BY EventID Order By EventID;

Answer 1

更好的做法是只获取一次值，然后在可能的情况下使用它。在您的示例中，您始终可以使用Inner来仅获取一次计数，并根据其值计算其他(派生)列，如下所示：

SELECT  EventID, NumberRunners,
        CASE WHEN NumberRunners <5 THEN 1
             WHEN NumberRunners >=5 AND NumberRunners <=7 THEN 2
             ELSE 3 
        END AS NumberPlacings
  FROM  (
            SELECT  EventID,
                    NumberRunners = Count(CompetitorID)
              FROM  Comps
            GROUP BY EventID
        ) t
Order By EventID;

Answer 2

最简单的方法是：

SELECT EventID，Count(distinct CompetitorID)作为NumberRunners，CASE WHEN Count(distinct CompetitorID)<5然后1 WHEN Count(distinct CompetitorID)>=5和Count(distinct CompetitorID)<=7然后2否则3结束为NumberPlacings FROM Comps GROUP BY EventID Order By EventID；