mysql - 如何编写sql查询来获取每个成员的最后一笔交易？

Question

我的查询是这样的:

SELECT
    FOB_Id, MM_Id, CONVERT(varchar(25),FOB_Date,103), FOB_Balance, FOB_B_CR_DR  
FROM
    FO_Opening_Balance
WHERE
    MM_Id IN (1,597)
        AND month(FOB_Date)=1 
        AND YEAR(fob_date) = 2014
GROUP BY
    MM_Id, FOB_Id, FOB_Date, FOB_Balance, FOB_B_CR_DR  
ORDER BY
    FOB_Date desc, FOB_Id DESC

会显示如下:

FOB_Id  | MM_Id | FOB_Date  |  Balance | cr/dr
-------------------------------------------------
625773  |  1    |31/01/2014 |   247.32 | Cr        
624391  | 597   |31/01/2014 |  1663.42 | Dr        
623999  |  1    |31/01/2014 |   447.32 | Cr        
622000  | 597   |31/01/2014 |  1551.42 | Dr        
621877  |  1    |31/01/2014 |   559.32 | Cr        
621473  |  1    |31/01/2014 |   865.32 | Cr        
620907  |  1    |24/01/2014 |   893.32 | Cr        
615844  | 597   |06/01/2014 |  1439.42 | Dr

但是我只想显示成员(member)id的最后一笔交易，如果我在select语句中给出top 1它只会显示一行，这里我给了两个成员(member)id 1和597，它应该显示只显示上表的前两行，这样如果给了很多成员(member)id，应该只显示该成员(member)交易，请帮助。

Answer 1

根据查询语法，我假设您正在使用 SQL Server。如果你想要最后一笔交易，你可以使用row_number():

select FOB_Id,  MM_Id, dte, FOB_Balance, FOB_B_CR_DR
from (select FOB_Id,  MM_Id , convert(varchar(25), FOB_Date,103) as dte, FOB_Balance, FOB_B_CR_DR, FOB_Date,
             row_number() over (partition by mm_id order by fob_date desc) as seqnum
      from FO_Opening_Balance
      where MM_Id in (1,597) and
            month(FOB_Date) = 1 and YEAR(fob_date) = 2014
     ) t
where seqnum = 1
order by FOB_Date desc, FOB_Id desc;