SQL为每个用户获取最近修改的帖子

Question

这是我的 table :

CREATE TABLE [dbo].[posts]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [user_id] [int] NOT NULL,
    [date_posted] [datetime] NOT NULL,
    [date_modified] [datetime] NOT NULL,
    [content] [text] NOT NULL,

    CONSTRAINT [PK_posts] PRIMARY KEY CLUSTERED ( [id] ASC )
)

我的公司需要一个查询来获取每个用户最近修改的帖子的帖子 ID。谁能帮帮我吗？谢谢，

Answer 1

rank() 函数应该可以解决这个问题:

SELECT user_id, id AS most_recent_post_id
FROM   (SELECT user_id, 
               id, 
               RANK() OVER (PARTITION BY user_id ORDER BY date_posted DESC) AS rk
        FROM   [posts]) p
WHERE  rk = 1