如何在表中存储向后的ForeignKey关系COUNT ?

Question

我有一个表Post(id,date,title,content,author)
和一个表Like(id,date,author,post->ForeignKey(Post))
所以我想在表Post中添加一个字段'likes'来存储喜欢的计数，以便方便地获得每个Post -object拥有的喜欢的计数。
有可能实现吗?

Answer 1

首先，在post表中添加列:

ALTER TABLE Post
ADD num_likes int;

然后使用likes表更新post表(SO question for ref):

with likes as {
Select post, Count(1) as num_likes
FROM Like
GROUP BY post
}
UPDATE Post 
SET Post.num_likes = likes.num_likes 
FROM Post INNER JOIN likes ON Post.post = likes.post