SQL Server：从分组依据中选择最后一个结果

admin

SQL Server：从分组依据中选择最后一个结果

sql

我正在尝试从2个相似的结果（最旧的结果）中检索最后一个结果

FromUser | ToUser | Message | Date
User1    | User2  | hi      | 01/01/2013 20:00
User1    | User2  | hi later| 01/01/2013 21:00
User5    | User2  | hi      | 01/01/2013 20:01
User5    | User2  | hi later| 01/01/2013 21:01

SELECT        
    CM.FromUser, CM.ToUser, CM.Message, CM.Date, 
    U.UserId, U.UserFullName, U.UserName, U.UserPhoto
FROM
    ConversationMessages AS CM 
INNER JOIN
    Users AS U ON U.UserName = CM.FromUser
WHERE
    CM.ToUser = @user
ORDER BY 
    CM.Date DESC

它应先列出用户5嗨，然后再列出用户1嗨，然后再列出（用户5在日期上较新）。只有最后一行基本上按FromUser日期分组，然后按日期排序。我对sql没用，在这里尝试了许多建议，但没有任何工作

阅读 426

2021-07-01

共1个答案

admin

您想为此使用窗口函数，而不是group by：

select FromUser, ToUser, Message, [Date], UserId, UserFullName, UserName, UserPhoto
from (SELECT CM.FromUser, CM.ToUser, CM.Message, CM.Date, U.UserId,
             U.UserFullName, U.UserName, U.UserPhoto,
             row_number() over (partition by CM.FromUser, CM.ToUser order by [Date] desc) as seqnum
      FROM ConversationMessages CM INNER JOIN
           Users U
           ON U.UserName = CM.FromUser
      WHERE  CM.ToUser = @user
     ) s
WHERE seqnum = 1
ORDER BY CM.Date DESC ;

2021-07-01