查询每个人参加的最后一次事件

Question

我有一个名为CampRegistration的表，其结构如下：

[EID] INT, [CampName] VARCHAR(100), [StartDt] DATETIME

EID是参加夏令营的人的标识符。我希望选择CampRegistration表中每个人在给定日期之前参加的最后一个夏令营。

这是我的第一次尝试：

@DECLARE @currentCampDt DATETIME = '2012-8-4';
SELECT [EID], [CampName], MAX([StartDt]) [StartDt]
FROM [CampRegistration]
WHERE [StartDt] < @currentCampDt 
GROUP BY [EID],[CampName]
order by [EID]

这里的问题是，如果有人以不同的名字参加了多个夏令营，我会得到该人的多个结果(每个夏令营名称的最后一次参加)。我只想为每个人拿回一张记录，他们参加的最后一个夏令营。我最终确实需要为每条记录获取这三条信息(EID、CampName和营地的StartDt)，所以我不确定是否真的可以从Group By中删除CampName。

我正在使用SQL Server 2012，如果有任何关于如何实现这种类型的查询结果的建议，我将不胜感激。

谢谢。

Answer 1

一种方法是使用CTE (公用表表达式)。

使用这个CTE，你可以用一些标准来划分你的数据--比如你的EID --并且让SQL Server对你所有的行进行编号，每个“分区”都是从1开始的，按照一些标准排序。

所以试着这样做：

;WITH CampEvents AS
(
   SELECT 
       EID, CampName, StartDt,
       RowNum = ROW_NUMBER() OVER(PARTITION BY EID ORDER BY StartDt DESC)
   FROM 
       dbo.CampRegistration
)
SELECT 
   EID, CampName, StartDt
FROM 
   CampEvents
WHERE
   RowNum = 1

在这里，我只为每个“分区”(即每个EID)选择“第一个”条目(按降序的StartDt排序)，因此最新、最新的事件具有RowNum = 1。

这接近你要找的东西了吗？

Answer 2

这不会很快，但可以做到：

@DECLARE @currentCampDt DATETIME = '2012-8-4';

SELECT o.[EID], o.[CampName], o.[StartDt]
FROM [CampRegistration] o
WHERE o.[StartDt] = ( 
                      SELECT MAX(i.[StartDt]) 
                      FROM [CampRegistration] i WHERE i.[StartDt] < @currentCampDt
                      AND
                      i.[EID] = o.[EID]
                    )
order by o.[EID]

Answer 3

您可以这样做：

WITH LastCampsAttended
As
(
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY EID ORDER BY StartDt DESC) AS rownum
  FROM CampRegistration
  WHERE [StartDt] < @currentCampDt 
)
SELECT 
  EID,
  CampName,
  StartDt
FROM LastCampsAttended
WHERE rownum = 1;

SQL Fiddle Demo

或：

SELECT
  camps.*
FROM CampRegistration camps
INNER JOIN 
(
  SELECT EID, MAX(StartDt) LatestDate
  FROM CampRegistration
  GROUP BY EID
) LatestCamps  ON camps.EID = LatestCamps.EID
              AND camps.StartDt = LatestCamps.LatestDate
WHERE camps.StartDt < @currentCampDt ;

Updated SQL fiddle Demo