GroupBy-带日期参数的SQL查询GroupBy

Question

假设我有一个表TeamRatings，看起来像这个

|---Team----|--ValuationDate--|-Rating-|
|--Saints---|---10/15/2012----|---81.1-|  
|--Broncos--|---10/15/2012----|---91.1-|  
|--Ravens---|---10/16/2012----|--101.1-|  
|--Broncos--|---10/22/2012----|---82.1-|  
|--Ravens---|---10/22/2012----|---83.1-|  
|--Saints---|---10/29/2012----|---84.1-|  
|--Broncos--|---10/28/2012----|---85.1-|
|--Ravens---|---10/29/2012----|---86.1-|

此外，假设一支球队的评分保持不变，直到他们打了一场新的比赛（代表一项新纪录）。例如，野马队在2012年10月21日的评级假设为102.8

我想要一个带有日期参数的查询，它将为每支球队返回一条记录，代表该球队在指定日期之前的最近一场比赛。例如，

如果我输入2012年10月23日作为我的日期参数，查询应该返回

|---Team---|-ValuationDate---|-Rating-|
|--Saints--|---10/15/2012----|---81.1-|  
|--Broncos-|---10/22/2012----|---82.1-|  
|--Ravens--|---10/22/2012----|---83.1-|

非常感谢您的帮助。谢谢

Answer 1

在MS SQL Server 2005或更高版本上，您可以使用带有ROW_NUMBER函数的cte：

WITH x 
     AS (SELECT team, 
                valuationdate, 
                rating, 
                rn = Row_number() 
                       OVER( 
                         partition BY team 
                         ORDER BY valuationdate DESC) 
         FROM   teamratings 
         WHERE  valuationdate < @DateParam) 
SELECT team, 
       valuationdate, 
       rating 
FROM   x 
WHERE  rn = 1 

Answer 2

您可以使用这样一个更通用的查询：

select Team, x.ValuationDate, Rating
from TeamRatings inner join
(
 select Team, max(ValuationDate) as ValuationDate
 from TeamRatings 
 where ValuationDate < @dateParameter
 group by Team
) x on TeamRatings.Team = x.Team and TeamRatings.ValuationDate = x.ValuationDate