【SQL Server】取出群組中最後一筆資料 (雙鍵值)

2013-05-30

20408
0
SQL Server

【SQL Server】取出群組中最後一筆資料 (雙鍵值)
這個問題，我大約想了三四天了，今天終於讓我找的一個好的方法去解決這個問題，案例是某個客戶訂了某些產品，他最後一次交易的金額，所以要群組客戶跟產品，跟上一篇的筆記：Group中的最新一筆資料不太依樣，上一篇是依照一個單一的情況，來擷取最後一筆資料！而今天是依照兩個欄位來取得最後一筆

這個問題，我大約想了三四天了，今天終於讓我找的一個好的方法去解決這個問題，案例是某個客戶訂了某些產品，他最後一次交易的金額，所以要群組客戶跟產品，跟上一篇的筆記：Group中的最新一筆資料不太依樣，上一篇是依照一個單一的情況，來擷取最後一筆資料！而今天是依照兩個欄位來取得最後一筆

declare @table table
(
 code int IDENTITY (1, 1) ,
 Productid varchar(10),
 Customerid varchar(10),
 CreateTime date,
 Value int
)

insert into @table (Customerid,Productid,CreateTime,Value) values ('X' ,'A','2013/1/1 ' ,1000)
insert into @table (Customerid,Productid,CreateTime,Value) values ('X' ,'B','2013/1/1 ' ,1000)
insert into @table (Customerid,Productid,CreateTime,Value) values ('X' ,'C','2013/1/5 ' ,500)
insert into @table (Customerid,Productid,CreateTime,Value) values ('X' ,'A','2013/5/1 ' ,500)
insert into @table (Customerid,Productid,CreateTime,Value) values ('X' ,'B','2013/6/1 ' ,500)
insert into @table (Customerid,Productid,CreateTime,Value) values ('Y', 'A','2013/1/1 ' ,1000)
insert into @table (Customerid,Productid,CreateTime,Value) values ('Y', 'B','2013/1/1 ' ,1000)
insert into @table (Customerid,Productid,CreateTime,Value) values ('Y', 'C','2013/1/1 ' ,1000)
insert into @table (Customerid,Productid,CreateTime,Value) values ('Y' ,'C','2013/7/1 ' ,500)
insert into @table (Customerid,Productid,CreateTime,Value) values ('Y' ,'B','2013/9/1 ' ,500)
insert into @table (Customerid,Productid,CreateTime,Value) values ('Y' ,'A','2013/9/1 ' ,500)
insert into @table (Customerid,Productid,CreateTime,Value) values ('Z', 'A','2013/1/1 ' ,1000)
insert into @table (Customerid,Productid,CreateTime,Value) values ('Z', 'B','2013/1/1 ' ,1000)
insert into @table (Customerid,Productid,CreateTime,Value) values ('Z', 'C','2013/1/1 ' ,1000)
insert into @table (Customerid,Productid,CreateTime,Value) values ('Z' ,'C','2013/7/1 ' ,500)
insert into @table (Customerid,Productid,CreateTime,Value) values ('Z' ,'B','2013/9/1 ' ,500)
insert into @table (Customerid,Productid,CreateTime,Value) values ('Z' ,'A','2013/9/1 ' ,500)

Select * from @table

select Customerid,Productid,CreateTime,Value from @table as a
where EXISTS (select * from (
select Productid,Customerid,ROW_NUMBER() Over (Partition By Productid,Customerid Order By CreateTime Desc,code desc) As Sort,code from @table
group by Customerid,Productid,CreateTime,code)as b
where b.Sort=1 and a.code=b.code)
order by a.Customerid,a.Productid

感謝 James大哥提供更好的方法：

with maxdata as
(
  select Customerid,Productid, MAX( CreateTime ) CreateTime 
  from @table group by Customerid,Productid
)
select B.* 
from maxdata A
join @table B on A.Customerid = B.Customerid AND A.Productid = B.Productid AND A.CreateTime=B.CreateTime
order by B.Customerid,B.Productid

結果：

大家好我是饅頭，希望大家喜歡我的文章

如果有錯誤的地方請不吝指教 ^_^

回首頁