如何计算'Like'计数并显示当前用户是否喜欢SQL中的这篇文章？

Question

我想在我的应用程序中实现'喜欢'(喜欢facebook)。这是我的表格设计:

CREATE TABLE [dbo].[Post]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[Content] NVARCHAR(500) NOT NULL,
[CreatedDate] datetime DEFAULT getdate()
)
CREATE TABLE [dbo].[Like]
(
[Id] INT NOT NULL PRIMARY KEY Identity(1,1),
[PostId] INT NOT NULL,
[UserId] INT NOT NULL,
FOREIGN KEY (PostId) REFERENCES Post(Id),
FOREIGN KEY (UserId) REFERENCES User(Id)
)
CREATE TABLE [dbo].[User] (
[Id] INT IDENTITY (1, 1) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

下面是通过页面

获取Posts的sql脚本

SELECT 
[Post].[Id],
[Post].[Content]
FROM [dbo].[Post]
ORDER BY [Post].[CreatedDate] DESC
OFFSET @Offset ROWS
FETCH NEXT @GetCount ROWS ONLY

我的问题是

1. 我怎样才能在上述查询中获得每个帖子的"喜欢计数"?
2. 我怎样才能知道当前用户是否喜欢每个帖子?

我希望在一次查询中得到如下结果:

<表类>Id内容LikeCountHadLikedtbody><<tr>11日发布50122日发布30033日发布101

将帖子连接到执行所需计数的派生表

SELECT [Post].[Id]
, [Post].[Content]
, coalesce(likes.likes,0) as likes
, coalesce(likes.cu_like,0) as cu_like
FROM [dbo].[Post]
LEFT JOIN (
SELECT PostId
, count(*) AS likes
, max(CASE WHEN UserId = current_user THEN 1 ELSE 0 END) AS cu_like
FROM [dbo].[Like]
GROUP BY PostId
) AS likes ON [Post].[Id] = [likes].[PostId]
ORDER BY [Post].[CreatedDate] DESC 
OFFSET @Offset ROWS
FETCH NEXT @GetCount ROWS ONLY