存在多个记录时，仅显示最近的结帐日期一次 [英] Displaying the most recent checkout date only once when multiple records exist

问题描述

这是我的数据：

Here is my data:

BookISBN	LastCheckOutDate	PatronNumber
100004	11/14/2011	950101
100005	12/18/2013	950102
100005	1/18/2013	950103
100005	4/10/2013	950104
100005	10/22/2013	950105
100005	9/17/2014	950106
100005	1/11/2016	950107
100005	12/5/2011	950108

我需要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, QtyCheckedOut


ORDER BY INV.BookISBN

我的尝试：



SQL MSDN，Google搜索，基本sql查询MAX（）

What I have tried:

SQL MSDN, Google search, basic sql query MAX()

推荐答案

这是您需要的关键字：< a href =https://www.w3schools.com/sql/sql_func_first.asp> SQL FIRST（）函数 [ ^ ]

Here is the keyword that you require: SQL FIRST() Function[^]

你能使用表格常用表达式CTE吗？

Can you use table common expression CTE?

;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:

myrownum	BookISBN	LastCheckOutDate	PatronNumber
1	        100004	        2011-11-14	        950101
1	        100005	        2016-01-11	        950107

现在你可以更新查询以加入LibraryInventory表，假设LibraryInventory中的所有记录都是唯一的



示例：

Now you can update the query to join LibraryInventory table, assuming all record in LibraryInventory are unique

Example:

;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.BookISBN
WHERE myrownum = 1

select bookisbn，to_date（substr（tempcol，1,8），'yyyymmdd'） lastcheckoutdate，substr（tempcol，9）patronnumber

来自

（

select bookisbn，max（to_char（lastcheckoutdate， YYYYMMDD）|| patronnumber）tempcol

来自libraryinventory

group by bookisbn

）

按bookisbn订购



- 这适用于Oracle

select bookisbn, to_date(substr(tempcol, 1, 8), 'yyyymmdd') lastcheckoutdate, substr(tempcol, 9) patronnumber
from
(
select bookisbn, max(to_char(lastcheckoutdate, 'yyyymmdd') || patronnumber) tempcol
from libraryinventory
group by bookisbn
)
order by bookisbn

-- this will work in Oracle

这篇关于存在多个记录时，仅显示最近的结帐日期一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！