SQL SELECT从两个表（友谊）语句 [英] SQL SELECT From two tables (friendship) statement

问题描述

我有以下表中创建：

表1：tbl_Connections

 使用[taaraf_db] 
 GO 
 
 SET ANSI_NULLS ON 
 GO 
 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
创建TABLE [DBO]。[tbl_Connections（
 [uc_Id] [INT] IDENTITY（1,1）NOT NULL，
 [uc_User] [为nvarchar（50）NOT NULL，
 [uc_Connection] [为nvarchar（50）NOT NULL，
 [uc_IsPending] [INT] NOT NULL，
 [uc_DateTime] [日期时间] NOT NULL，
约束[PK_tbl_Connections] PRIMARY KEY CLUSTERED 
（
 [uc_Id] ASC 
）和（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]。[tbl_Connections] ADD CONSTRAINT [DF_tbl_Connections_uc_IsPending] DEFAULT（（1））[uc_IsPending] 
 GO 
  

表2：tbl_LiveStream

 使用[taaraf_db] 
 GO 
 
 SET ANSI_NULLS ON 
 GO 
 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
创建TABLE [DBO]。[tbl_LiveStreams（
 [LS_ID] [INT] IDENTITY（1,1）NOT NULL，
 [ls_Story] ​​[为nvarchar（最大）NOT NULL，
 [ls_User] [为nvarchar（50）NOT NULL，
 [ls_Connection] [为nvarchar（50）NOT NULL，
 [ls_DateTime] [日期时间] NOT NULL，
约束[PK_tbl_LiveStreams] PRIMARY KEY CLUSTERED 
（
 [LS_ID] ASC 
）和（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [ PRIMARY] 
）ON [PRIMARY] 
 
 GO 
  

现在uc_User在tbl_Connections代表是谁发起的朋友请求的用户，并uc_Connection
代表另一方。
相同的概念上第二个表（tbl_LiveStreams）应用

我想选择一个cetrain用户的所有新的直播活动，直播应该是一个特定的检索只有当用户连接到对方（uc_IsPending = 0）

我有一个C＃函数，采用下列参数用户：

 公共静态数据表GetAsyncLiveStream（字符串的currentUser，诠释startAt，诠释的howmany）{...} 
  

和以上所述，一个DataTable应该返回所有新项目的直播对于给定的currentUser（ls_User）如果指定了上述要求是正确的。

这是有点像Facebook的新闻提要，什么是与朋友等等......发生
噢，tbl_LiveStream是填充每当有人（ls_User）initates某种特定的事件来（ls_Connection）。

我不知道如果我在这里复杂了我的生活，但是这就是我和所有帮助表示赞赏。

我要指出，我已经通过从tbl_LiveStream所有返回的记录循环做到了这一点，并使用自定义类的函数.IsFriends做了一些验证（ ），将去检查数据库中的友谊状态和编程填充一个DataTable ...这是有点傻，我承认。但我不知道如何做到这一点。

最后一件事，
我使用下面的查询返回有限的结果：

  SELECT * FROM（
选择ROW_NUMBER）OVER（ORDER（BY）AS ROWNUM，LS_ID 
 FROM）
，为
，其中A.rownum BETWEEN（@启动）AND（@启动+ @rowsperpage）
  

请帮助，感谢您的时间。

修改
我想指出，这是怎么了我我取谁是朋友，谁从tbl_Connections表。

 常量字符串的sql =SELECT REPLACE（（uc_Connection + uc_User），@的currentUser，''）作为连接+ 
FROM tbl_Connections连接+ 
WHERE（@CurrentUser = uc_Connection）OR（@CurrentUser = uc_User）; 
  

解决方案

  SELECT * FROM（
选择ROW_NUMBER）OVER（ORDER（BY）AS ROWNUM，LS_ID 
 FROM）
，为
，其中A.rownum BETWEEN（@启动）AND（@启动+ @ rowsperpage）
  

这语法是错误的。你不能那样做。使用联盟，或使用LINQ到SQL。

从批判的角度来看，没有得到数据表，得到实体。你似乎有点过这里相当复杂，具有技术，你可能并不熟悉。我建议把所有现有数据，并将其倾倒到CSV文件，然后从头开始的东西，如夏普架构启动应用程序，它可以抽象掉其中的一些细节给你。

I have the following tables created:

Table #1: tbl_Connections

USE [taaraf_db]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_Connections](
    [uc_Id] [int] IDENTITY(1,1) NOT NULL,
    [uc_User] [nvarchar](50) NOT NULL,
    [uc_Connection] [nvarchar](50) NOT NULL,
    [uc_IsPending] [int] NOT NULL,
    [uc_DateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_tbl_Connections] PRIMARY KEY CLUSTERED 
(
    [uc_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].[tbl_Connections] ADD  CONSTRAINT [DF_tbl_Connections_uc_IsPending]  DEFAULT ((1)) FOR [uc_IsPending]
GO

Table #2: tbl_LiveStream

USE [taaraf_db]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_LiveStreams](
    [ls_Id] [int] IDENTITY(1,1) NOT NULL,
    [ls_Story] [nvarchar](max) NOT NULL,
    [ls_User] [nvarchar](50) NOT NULL,
    [ls_Connection] [nvarchar](50) NOT NULL,
    [ls_DateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_tbl_LiveStreams] PRIMARY KEY CLUSTERED 
(
    [ls_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

Now uc_User in tbl_Connections represents the user who initiated a friend request, and uc_Connection represents the other party. Same concept is applied on the second table (tbl_LiveStreams)

I am trying to select all new LiveStreams for a cetrain user, LiveStream should be retrieved for a specific user only if that user is connected to the other party (uc_IsPending = 0)

I have a C# function that takes the following arguments:

public static DataTable GetAsyncLiveStream(string CurrentUser, int startAt, int howMany) { ... }

And with the above, a DataTable should return all new items in LiveStream for the given CurrentUser (ls_User) if the requirements specified above are true.

This is sort of like the Facebook news feed, what's happening with friends etc... Oh yeah, tbl_LiveStream is populated whenever someone (ls_User) initates some sort of specific event to (ls_Connection).

I'm not sure if i'm complicating my life here but that's what I have and all help is appreciated.

I should mention that I've done this by looping through all returned records from tbl_LiveStream and done some validation using a custom class function .IsFriends() that will go and check for friendship status in the database and programatically populates a DataTable... Which is sort of stupid, I admit. But I'm not sure how to do this.

One last thing, I'm using the following query to return limited results:

SELECT * FROM (
SELECT row_number() OVER (ORDER BY ) AS rownum, ls_Id
FROM )
AS A
WHERE A.rownum BETWEEN (@start) AND (@start + @rowsperpage) 

Please help and thank you for your time.

EDIT I'd like to note that this is how I am fetching who's friends with who from the tbl_Connections Table.

    const string sql = "SELECT REPLACE((uc_Connection + uc_User), @CurrentUser, '') AS Connection " +
                       "FROM tbl_Connections Connection " +
                       "WHERE (@CurrentUser = uc_Connection) OR (@CurrentUser = uc_User)";

解决方案

SELECT * FROM (
SELECT row_number() OVER (ORDER BY ) AS rownum, ls_Id
FROM )
AS A
WHERE A.rownum BETWEEN (@start) AND (@start + @rowsperpage) 

This syntax is wrong. You can't do it like that. Use a Union, or use LINQ to SQL.

From a critique perspective, don't get DataTables, get Entities. You seem to be over complicating quite a bit here, with technologies you may not be familiar with. I'd advise taking all currently existing data and dumping it to a CSV file, then starting the application from scratch with something like Sharp Architecture, which can abstract away some of these details for you.

这篇关于SQL SELECT从两个表（友谊）语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！