sql - sql查询多层表

Question

我正在尝试检索带有 UserId=10004 的 UserInfo 以及他的所有 friend 和他 friend 的所有帖子以及这些帖子上的所有点赞。为此，我正在使用查询:

select *,
    (select * ,
        (select * ,
            (select * from PostLikes where PostId=UserPosts.PostId) as likes
        from UserPosts where UserId=FriendsRelation.PersonId1 or UserId=FriendsRelation.PersonId2) as posts
    from FriendsRelation where PersonId1=UserId or PersonId2=UserId) as friends
from UserInfo 
where UserId=10004

但是它返回错误

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

我该如何解决？

这是我正在使用的表格:

CREATE TABLE [dbo].[UserInfo]
(
    [UserId] [bigint] IDENTITY(1,1) NOT NULL,
    [Username] [nvarchar](max) NULL,
    [Email] [nvarchar](max) NOT NULL,
    [UserPassword] [nvarchar](max) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Gender] [nchar](10) NOT NULL,
    [ContactNo] [bigint] NOT NULL,
    [DateOfBirth] [date] NOT NULL,
    [RelationshipStatus] [nchar](10) NULL,
    [InterestedIn] [nchar](10) NULL,
    [Address] [nvarchar](max) NULL,
    [Country] [nchar](10) NOT NULL,
    [FavouriteQuote] [nvarchar](max) NULL,
    [DisplayPhoto] [nvarchar](max) NULL,
    [Guid] [nvarchar](max) NOT NULL,
    [Status] [tinyint] NOT NULL CONSTRAINT [DF_UserInfo_Status]  DEFAULT ((1)),
    [CreatedDate] [datetime] NOT NULL,
    [LastLogIn] [datetime] NULL,

    CONSTRAINT [PK_UserInfo] 
    PRIMARY KEY CLUSTERED ([UserId] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

UserPosts 表:

CREATE TABLE [dbo].[UserPosts]
(
    [PostId] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint] NOT NULL,
    [PostText] [nvarchar](max) NULL,
    [PostPicture] [nvarchar](max) NULL,
    [Time] [time](7) NOT NULL,
    [Date] [date] NOT NULL,
    [LikeCount] [int] NULL CONSTRAINT [DF_UserPosts_LikeCount]  DEFAULT ((0)),

    CONSTRAINT [PK_UserPosts] 
    PRIMARY KEY CLUSTERED ([PostId] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserPosts]  WITH CHECK 
  ADD CONSTRAINT [FK_UserPosts_UserInfo] 
  FOREIGN KEY([UserId]) REFERENCES [dbo].[UserInfo] ([UserId])
     ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UserPosts] CHECK CONSTRAINT [FK_UserPosts_UserInfo]
GO

和PostLikes表:

CREATE TABLE [dbo].[PostLikes]
(
    [LikeId] [bigint] IDENTITY(1,1) NOT NULL,
    [PostId] [bigint] NOT NULL,
    [UserId] [bigint] NOT NULL,

    CONSTRAINT [PK_PostLike] 
    PRIMARY KEY CLUSTERED ([PostId] ASC, [UserId] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PostLikes]  WITH CHECK 
  ADD CONSTRAINT [FK_PostLikes_UserInfo] 
    FOREIGN KEY([UserId]) REFERENCES [dbo].[UserInfo] ([UserId])
GO

ALTER TABLE [dbo].[PostLikes] CHECK CONSTRAINT [FK_PostLikes_UserInfo]
GO

ALTER TABLE [dbo].[PostLikes]  WITH CHECK 
  ADD CONSTRAINT [FK_PostLikes_UserPosts] 
    FOREIGN KEY([PostId]) REFERENCES [dbo].[UserPosts] ([PostId])
       ON DELETE CASCADE
GO

ALTER TABLE [dbo].[PostLikes] CHECK CONSTRAINT [FK_PostLikes_UserPosts]
GO

Answer 1

与其他人的答案类似，但对所有内容都使用左连接(以防用户没有 friend )，并且对连接逻辑进行了一些更改。

declare @userID int = 10004 --For easy swapping out if you want to query someone else
Select * from UserInfo a
left join FriendsRelation b
    on b.PersonID1 = @userID or b.PersonID2 = @userID --Faster than joining and then filtering, if you're only looking for one person at a time
left join UserPosts c
    on (c.UserID = b.PersonID1 or c.UserID = b.PersonID2) 
    and c.UserID <> @userID --returns only friends' posts, not user's posts, as specified in original question
left join postLikes d
    on d.PostID = c.PostID
where a.UserID = @userID