清除“重复”数据，同时保留最新条目

Question

我想显示每个机组成员、基本信息以及他们合同中的最新开始日期。使用我的基本查询，它会为每个合同返回一行，复制具有不同开始和结束日期的基本信息。我只需要每个人一行，带有最新的开始日期（如果他们还没有开始日期，则为null）。

我对分组和分区函数的理解有限。我为类似日期逆向工程的查询使用分区并创建临时表进行选择。最终我可以重用它，但它似乎比我们需要的更复杂。

    select
Case when P01.EMPLOYMENTENDDATE < getdate() then 'Y'
else ''
end as "Deactivate",
concat(p01.FIRSTNAME,' ',p01.MIDDLENAME) as "First and Middle",
      p01.LASTNAME, 
p01.PIN,
(select top 1 TELENO FROM PW001P0T WHERE PIN = P01.PIN and TELETYPE = 6 ORDER BY TELEPRIORITY) as "EmailAddress",
      org.NAME AS Vessel, 
case 
       WHEN c02.CODECATEGORY= '20' then 'MARINE'
       WHEN c02.CODECATEGORY= '10' then 'MARINE'
       ELSE 'HOTEL' end as "Department",
         c02.name as RankName,
               c02.Alternative RankCode, 
convert(varchar, ACT.DATEFROM,101) EmbarkDate,
convert(varchar,(case when ACT.DATEFROM is null then p03.TODATEESTIMATED else ACT.DATEFROM end),101) DebarkDate

FROM   PW001P01 p01 
      JOIN PW001P03 p03 
           ON  p03.PIN = p01.PIN
      LEFT JOIN PW001C02 c02 
           ON  c02.CODE = p03.RANK 
         /*LEFT JOIN PW001C02 CCIRankTbl 
           ON  CCIRankTbl.CODE = p01.RANK*/
      LEFT JOIN PWORG org 
           ON  org.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3) 
     LEFT JOIN PWORGVESACT ACT
     ON  ACT.numorgid=dbo.ad_scanorgtree(p03.numorgid,3) 

where P01.EMPLOYMENTENDDATE > getdate()-10 or P01.EMPLOYMENTENDDATE is null

我只需要每列显示一行。前5列将始终相同。最后一列取决于合同，我们只需要最新一列的数据。

<table><tbody><tr><th>Deactivate</th><th>First and Middle</th><th>Lastname</th><th>PIN</th><th>Email</th><th>Vessel</th><th>Department</th><th>Rank</th><th>RankCode</th><th>Embark</th><th>Debark</th></tr><tr><td> </td><td>Martin</td><td>Smith</td><td>123</td><td>[e-mail]</td><td>Ship1</td><td>Marine</td><td>ViceCaptain</td><td>VICE</td><td>9/1/2008</td><td>9/20/2008</td></tr><tr><td> </td><td>Matin</td><td>Smith</td><td>123</td><td>[e-mail]</td><td>Ship2</td><td>Marine</td><td>Captain</td><td>CAP</td><td>12/1/2008</td><td>12/20/2008</td></tr><tr><td> </td><td>Steve Mark</td><td>Dude</td><td>98765</td><td>[e-mail]</td><td>Ship1</td><td>Hotel</td><td>Chef</td><td>CHEF</td><td>5/1/2009</td><td>8/1/2009</td></tr><tr><td> </td><td>Steve Mark</td><td>Dude</td><td>98765</td><td>[e-mail]</td><td>Ship3</td><td>Hotel</td><td>Chef</td><td>CHEF</td><td>10/1/2010</td><td>12/20/2010</td></tr></tbody></table>

Answer 1

在主查询中将您的查询更改为选择DISTINCT，并为DebarkDate列使用子选择：

您可以根据该子查询的结果在需要的日期进行任何转换。