Question

我有两个表格:

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

我用下文(布赖恩建议):

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

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.

任何想法? 感谢。

Answer 1

你们需要加入Streams。不幸的是,当你注意到这并非易事时,因为没有亲爱的友谊和生活障碍,而且你需要利用亲友在生活障碍中的朋友,都属于放任或接受者一栏。

SELECT id, 
       CASE 
         WHEN f.sender = @UserName THEN f.recipient 
         ELSE f.sender 
       END AS friend, 
       f.ispending, 
       f.datetime 
FROM   friendships  f
       LEFT JOIN LiveStreams l
       ON f.sender = l.sender
          or f.sender = l.recipient
          or f.recipient = l.sender
          or f.recipient = l.recipient

WHERE  ( f.sender = @UserName 
          OR f.recipient = @UserName ) 
       AND f.ispending = @IsPending

You may want to consider making the LEFT JOIN and INNER JOIN

Answer 2

缩略语:

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”作为主流的发送者或接收者,那么这种询问会变得更加简单(和faster<>em>)。

我也怀疑在座桌设计上存在严重缺陷。溪流可能只有一个自有,你和你可以使用一个单独的表格处理诸如向其他用户发送流、支持流等特征。

友情链接