存在多个记录时，仅显示最近的结帐日期一次

本文介绍了存在多个记录时，仅显示最近的结帐日期一次的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 这是我的数据： Here is my data:BookISBNLastCheckOutDatePatronNumber10000411/14/201195010110000512/18/20139501021000051/18/20139501031000054/10/201395010410000510/22/20139501051000059/17/20149501061000051/11/201695010710000512/5/2011950108 我需要BookISBN才能显示最近一次结账日期只有一次。 所有这些字段都放在一张桌子上。 即使我查询MAX（LastCheckOutDate），我仍然可以获得BookISBN的多个条目。 如何每个BookISBN只获得一个条目> 我的查询是： I need the BookISBN to display only once for the most recent last checkout date.All these fields are in one table.Even when I query the MAX(LastCheckOutDate), I still get the multiple entries for the BookISBN.How can I get just one entry per BookISBN>My query is: <pre> SELECT INV.BookISBN,MAX(LastCheckoutDate) AS LastDate,PatronNumber, BookDesc, BookPrice, QtyCheckedOut FROM LibraryInventory INV LEFT JOIN Books BKS ON INV.BookISBN = B.BookISBN WHERE B.BookISBN IS NULL AND INV.BookISBN IS NOT NULL AND LastCheckoutDate < DATEADD("d", -365, CONVERT(VARCHAR(10),Getdate(),101)) GROUP BY INV.BookISBN,PatronNumber, BookDesc, BookPrice, QtyCheckedOutORDER BY INV.BookISBN 我的尝试： SQL MSDN，Google搜索，基本sql查询MAX（）What I have tried:SQL MSDN, Google search, basic sql query MAX()推荐答案 ;WITH temp AS (SELECT ROW_NUMBER() OVER (PARTITION BY BookISBN ORDER BY LastCheckOutDate DESC ) AS myrownum ,* from book )SELECT * FROM temp WHERE myrownum = 1 输出： Output:myrownumBookISBNLastCheckOutDatePatronNumber1 100004 2011-11-14 9501011 100005 2016-01-11 950107 现在你可以更新查询以加入LibraryInventory表，假设LibraryInventory中的所有记录都是唯一的 示例： Now you can update the query to join LibraryInventory table, assuming all record in LibraryInventory are uniqueExample:;WITH temp AS (SELECT ROW_NUMBER() OVER (PARTITION BY BookISBN ORDER BY LastCheckOutDate DESC ) AS myrownum ,* from book )SELECT * FROM temp B JOIN LibraryInventory INV ON B.BookISBN = INV.BookISBNWHERE myrownum = 1 这篇关于存在多个记录时，仅显示最近的结帐日期一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！