SELECT NewSWeiBo.ID,UserWeiBo.UID, NewSWeiBo.UserName, UserWeiBo.UserPic, NewSWeiBo.Content, NewSWeiBo.IsRe, NewSWeiBo.ShenHe,NewSWeiBo.CreateTime FROM NewSWeiBo ,GuanZhu,UserWeiBo Where NewSWeiBo.UserID = GuanZhu.UserID and GuanZhu.GuanZhuID = UserWeiBo.ID and NewSWeiBo.UserID=4查询之后结果却出现重复的记录.....
SELECT NewSWeiBo.ID,UserWeiBo.UID, NewSWeiBo.UserName, UserWeiBo.UserPic, NewSWeiBo.Content,
NewSWeiBo.IsRe, NewSWeiBo.ShenHe,NewSWeiBo.CreateTime
FROM NewSWeiBo
LEFT JOIN GuanZhu ON NewSWeiBo.UserID = GuanZhu.UserID
LEFT JOIN UserWeiBo ON GuanZhu.GuanZhuID = UserWeiBo.ID
Where NewSWeiBo.UserID=4
表1:NewSWeiBo是微博帖子表,当中是用UserID来做发布者的关联字段;
表2:UserWeiBo是微博用户信息表,当中是通过UID来和表1中的UserID进行关联;
表3:GuanZhu是用于记录UserWeiBo表中用户所关注的对象表(所关注的用户,多的关系)也就是说,我要查询某用户(GuanZhu.UserID=4)所关注的那些人(UserWeiBo.UID = NewSWeiBo.UserID )发的帖子(UserWeiBo.ID = GuanZhu.GuanZhuID)。SELECT NewSWeiBo.ID, NewSWeiBo.Title,
NewSWeiBo.Content, NewSWeiBo.UserName,
NewSWeiBo.UserID, UserWeiBo.UserPic
FROM GuanZhu LEFT JOIN
UserWeiBo ON GuanZhu.GuanZhuID = UserWeiBo.ID LEFT JOIN
NewSWeiBo ON UserWeiBo.UID = NewSWeiBo.UserID
WHERE (GuanZhu.UserID = '4')上面这句查询结果是出来两条,但数据都是空的,为何?
因为 用户4关注 的人都没有 发帖,所以数据为空,可以看看 left join 的用法