我正在写类似于Facebook时间线的内容，用户可以看到所有帖子，如果用户之前喜欢过该帖子，也会看到蓝色拇指。
这是两张桌子，
Posts PostID | USERID | MORE COLS.... 1 | 1 | .... 2 | 1 | .... 3 | 3 | .... 4 | 5 | .... Likes LikeID | POSTID | USERID | MORE COLS.... 1 | 1 | 1 | .... 2 | 1 | 5 | .... 3 | 2 | 1 | .... 4 | 3 | 6 | ....我的问题是，我如何编写一个SQL查询来获取所有帖子，并知道用户以前是否喜欢这些帖子？
因此，例如，查询的理想结果将是（如果此用户的id为1）
All Posts PostID | LikedByMeBefore | .... 1 | true | .... 2 | false | .... 3 | true | .... 4 | true | .... 5 | false | .... 6 | false | .... . | . | .... . | . | ....I'm writing something similar to facebook timeline that user can see all the post and also see a blue thumb if the user has liked that post before.
Here are the two tables,
Posts PostID | USERID | MORE COLS.... 1 | 1 | .... 2 | 1 | .... 3 | 3 | .... 4 | 5 | .... Likes LikeID | POSTID | USERID | MORE COLS.... 1 | 1 | 1 | .... 2 | 1 | 5 | .... 3 | 2 | 1 | .... 4 | 3 | 6 | ....My question is that how can I write a sql query to get all the posts and also know if the user has liked these posts before?
So for example the ideal result of the query would be like (if this user's id is 1)
All Posts PostID | LikedByMeBefore | .... 1 | true | .... 2 | false | .... 3 | true | .... 4 | true | .... 5 | false | .... 6 | false | .... . | . | .... . | . | ....最满意答案
目前还不清楚Posts表中的UserID列是什么，但我可以假设它是创建它的用户。 但是Likes表中的UserID更加明显。 你需要使用LEFT JOIN结合ISNULL ：
DECLARE @UserID INT = 5 SELECT Posts.PostID, ISNULL(Likes.LikeID, 0) AS LikedByMeBefore, ...more columns... FROM Posts LEFT JOIN Likes ON Posts.PostID = Likes.PostID AND (Likes.UserID = @UserID OR Likes.UserID IS NULL)It's unclear what the UserID column is in the Posts table, but I can assume it's the user who created it. The UserID in the Likes table is more obvious though. You need to use a LEFT JOIN combined with ISNULL:
DECLARE @UserID INT = 5 SELECT Posts.PostID, ISNULL(Likes.LikeID, 0) AS LikedByMeBefore, ...more columns... FROM Posts LEFT JOIN Likes ON Posts.PostID = Likes.PostID AND (Likes.UserID = @UserID OR Likes.UserID IS NULL)更多推荐
发布评论