时间戳

首页 > 解决方案 > 如何获得不同组合的最大日期

sql-server - 如何获得不同组合的最大日期

问题描述

我正在尝试查找与不同数据组合的最大数据相关的数据，而不是所有数据的最大日期。我搜索了类似的问题，但似乎我的问题有所不同。我检查了

如何在 SQL Get Max Date - For Every Transaction中选择具有 MAX（列值）、DISTINCT by MULTIPLE 列的行

这是我返回所有数据的代码：

select top.name as [familyName], s.sportName, p.position, bottom.firstName as [firstName], a.awardAmount, a.awardDate
from familyRelation r
inner join family bottom on r.firstNameID = bottom.NameID  
inner join family top on r.familyID = top.NameID
inner join familyPosition p on p.NameID = bottom.NameID
inner join sport s on s.sportID = p.sportID 
left join awards a on a.awardID = p.familyPositionID 


familyName     sport     position   firstName   award  awardDate

Smith         tennis     umpire      mary       null     1/1/2011       
Smith         tennis     umpire      mary       100     10/20/2011       
Smith         swim       diver       mary       null     1/1/2019       
Smith         diving     diver       susan      300     1/1/2011       
Jones         rugby      player      henry      100     1/1/2016       
Jones         rugby      player      henry      150     1/1/2011       
Jones         rugby      forward     henry      190     1/1/2008       
Jones         rugby      forward     henry      100     1/1/2011
Adams         hockey     goalkeeper  grant      null     1/1/2011       
Adams         hockey     goalkeeper  grant      null     12/12/2018       
Adams         hockey     goalkeeper  grant      null     1/1/2011       
Adams         hockey     goalkeeper  grant      5750     1/1/2011

但我需要返回与 (family, sport, position, firstName) 组合的最大日期相关的数据，如下所示：

familyName    sport     position   firstName   award   awardDate
Smith         tennis     umpire      mary       100     10/20/2011       
Smith         swim       diver       mary       150     1/1/2019       
Smith         diving     diver       susan      300     1/1/2011       
Jones         rugby      player      henry      190     1/1/2016       
Jones         rugby      forward     henry      100     1/1/2011
Adams         hockey     goalkeeper  grant      null    12/12/2018

这些是family和firstname表之间的关系familyRelation表

familyId  firstNameID
100          10
100          20
200          30
300          40

家庭表

NameID     name
10         mary
20         susan 
30         henry
40         grant
100        smith
200        jones
300        adams

我试过了，但它对所有行使用相同的最大日期，这不是我想要的，因为（家庭、运动、职位、名字）的不同组合具有不同的最大日期： 

select top.name as [familyName], s.sportName, p.position, bottom.firstName as [firstName], a.awardAmount, a.awardDate
from familyRelation r
inner join family bottom on r.firstNameID = bottom.NameID  
inner join family top on r.familyID = top.NameID
inner join familyPosition p on p.NameID = bottom.NameID
inner join sport s on s.sportID = p.sportID 
left join ( select awardAmount, max(awardDate) 
            from awards
            group by awardAmount) a
            on a.aID = p.p.familyPositionID

谢谢你的帮助。我似乎无法弄清楚这一点。

更新：我尝试使用 row_number() 但代码仍然没有返回我需要的结果。我需要包括 a.awardAmount 但是一旦我添加它，就没有最大日期，因为奖励金额是不同的

select top.name as [familyName], s.sportName, p.position, bottom.firstName as [firstName], a.awardAmount, a.awardDate
, row_number() over partition by top.name, s.sportName, p.position, bottom.firstName
order by top.name, s.sportName, p.position, bottom.firstName)  
from familyRelation r
inner join family bottom on r.firstNameID = bottom.NameID  
inner join family top on r.familyID = top.NameID
inner join familyPosition p on p.NameID = bottom.NameID
inner join sport s on s.sportID = p.sportID 
left join awards a on a.awardID = p.familyPositionID

标签： sql-serverdatemaxaggregate-functions

解决方案

听起来您正在寻找一个简单的组 -

SELECT
      top.name as [familyName], 
      s.sportName,
      p.position, 
      bottom.firstName as [firstName], 
      a.awardAmount, 
      MAX(a.awardDate) as maxDatePerGroup
from familyRelation r
    inner join family bottom on r.firstNameID = bottom.NameID  
    inner join family top on r.familyID = top.NameID
    inner join familyPosition p on p.NameID = bottom.NameID
    inner join sport s on s.sportID = p.sportID 
    left join awards a on a.awardID = p.familyPositionID 
GROUP BY top.name, s.sportName, p.position,bottom.firstName, a.awardAmount

推荐阅读