如何在SQL中仅显示关系中的最新记录？

雷·朗尼

我有一个动物许可证数据库，我正在尝试以一个子表单编写一个查询，该查询将向我显示一个人拥有的每只宠物以及他们所购买的最新标签。现在，我的查询返回了每只动物的标签，并且每年都有标签。

SELECT tblPets.NAMEOFPET, 
       tblPets.SPECIES, 
       tblePetTags.TagYear, 
       tblePetTags.TagNumber, 
       tblPets.ID, 
       tblPets.PETOWNER, 
       tblPetOwners.ID AS ID_tblPetOwners, 
       tblPetOwners.ID AS ID_tblPetOwners1, 
       tblPets.DESIGNATION 
FROM   tblPetOwners 
       INNER JOIN (tblPets 
                   LEFT JOIN tblePetTags 
                          ON tblPets.[ID] = tblePetTags.[Pet]) 
               ON tblPetOwners.ID = tblPets.PETOWNER 
WHERE  (( ( tblPets.DESIGNATION ) NOT IN ( "Deceased", "Disowned", "Moved" ) ));

如何获取仅显示最新标签而不显示所有标签的查询？

戈登·利诺夫

通过在WHERE子句中使用子查询来匹配最近的年份，可以获取每个宠物的最新标签：

SELECT . . .
FROM tblPetOwners INNER JOIN
     (tblPets LEFT JOIN
      tblePetTags 
      ON tblPets.[ID] = tblePetTags.[Pet]
     ) ON tblPetOwners.ID = tblPets.PETOWNER 
WHERE tblPets.DESIGNATION NOT IN ( "Deceased", "Disowned", "Moved" ) AND
      tblePetTags.TagYear = (SELECT MAX(pt2.TagYear)
                             FROM tblePetTags as pt2
                             WHERE pt2.Pet = tblePetTags.Pet
                            );

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。