SQL Server中，加入一个表+检索数据，如果用户朋友参数

问题描述

我有两个表：

CREATE TABLE [dbo].[Friendships](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Sender] [nvarchar](50) NOT NULL,
    [Recipient] [nvarchar](50) NOT NULL,
    [IsPending] [int] NOT NULL,
    [DateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_tbl_Connections] PRIMARY KEY CLUSTERED 
(
    [Id] 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].[Friendships] ADD  CONSTRAINT [DF_tbl_Connections_uc_IsPending]  DEFAULT ((1)) FOR [IsPending]
GO

和

CREATE TABLE [dbo].[LiveStreams](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Value] [nvarchar](max) NOT NULL,
    [Sender] [nvarchar](50) NOT NULL,
    [Recipient] [nvarchar](50) NOT NULL,
    [DateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_LiveStreams] PRIMARY KEY CLUSTERED 
(
    [Id] 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

我通过以下（由Brian的建议）正从友谊的好友列表成功地：

I'm getting the friends list from Friendships sucessfully by using the following (as suggested by Brian):

        "SELECT Id, CASE " +
        "WHEN Sender = @UserName THEN Recipient " +
        "ELSE Sender " +
        "END AS Friend, IsPending, DateTime " +
        "FROM Friendships " +
        "WHERE (Sender = @UserName " +
        "OR Recipient = @UserName) " + 
        "AND IsPending = @IsPending;";

不过，我想从串流只有当SQL语句返回匹配的行。这意味着我想有将返回所有串流的@SomeUser的所有朋友的方法。
获取朋友是没有问题的（见上面的查询），但如果无论是在直播的朋友是发件人或收件人栏下获得的@SomeUser各位朋友进行直播。

However, I would like to return the rows from LiveStreams only if the sql statement matches. Meaning I want to have a method that will return all LiveStreams for all friends of @SomeUser.Getting the friends is not the problem (see query above), but getting the LiveStreams of all friends of @SomeUser if either the friend in LiveStream is under the Sender or Recipient column.

有什么想法？谢谢你。

推荐答案

这是一个简单的INNER JOIN：

It's a simple INNER JOIN:

SELECT <column list>
FROM LiveStreams l
INNER JOIN Friendships f ON f.Sender = l.Sender OR f.Sender = l.Recipient OR f.Recipient = l.Sender OR f.Recipient = l.Recipient
WHERE l.Sender = @SomeUser OR l.Recipient = @SomeUser

此查询可以得到很多简单（的更快的），如果你可以缩小你的@SomeUser作为发送者或流的收件人。

This query can get a lot simpler (and faster) if you can narrow down your @SomeUser as either the sender or the Recipient of the stream.

我在你的表设计怀疑有严重的缺陷在这里，以及。甲流可能应该有一个的所有者的，你，你可以使用一个单独的表（S）来处理像发送数据流给其他用户，收藏流等功能。

I suspect a serious flaw in your table design here, as well. A stream should probably have a single Owner, you and you can use a separate table(s) to handle features like sending the streams to other users, favoriting streams, etc.

这篇关于SQL Server中，加入一个表+检索数据，如果用户朋友参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！