SQL Server:如何选择一级,二级和三级联系人 [英] SQL Server: how to select First, Second and Third degree contacts
问题描述
我正在一个社交网站项目上,我需要列出我的联系人的一级,二级和三级联系人。我正在使用SQL Server AND C#
I'm working on a social web-site project and I need to list "First, Second and Third Degree Contacts" of my contacts. I'm using SQL Server AND C#
假设一个 contact
表是这样的:
一级学位联系人:
- 如果
gulsah
是我,那么我的第一个学位联系人是burak,sennur
- If
gulsah
is me then my first degree contacts areburak,sennur
查询我用来选择此选项:
Query I use to select this:
SELECT contact_2 FROM Contacts_Table WHERE contact_1 like 'gulsah'
第二学位联系人:
如果 gulsah
又是我那么我的第二级联系人是:马里
If gulsah
is me again then my second degree contacts are: mali
很难选择不属于我的联系人我的第一学位联系人。
What makes it difficult is to select contacts of my contacts who are not my first degree contact.
我可以选择相互联系,但我猜这不是正确的方法。
I can select mutual contacts but I guess it is not the right approach.
例如,选择我( gulsah
)和 burak
的相互接触:
For example, to select mutual contacts of me (gulsah
) and burak
:
SELECT contact_1 FROM (SELECT * FROM Contact_Test
WHERE contact_2 like 'burak') a
INNER JOIN (SELECT contact_1 FROM Contact_Test
WHERE (contact_2 = 'gulsah')) b
ON a.contact_1 = b.contact_1
此查询有效,但如我所说,这不是适合此工作的方法。
This query works but as I said, it's not the right approach for this job.
对于三级联系人:
如果 gulsah
又是我,然后我的三级联系人是_ mehmet,ahmet
If gulsah
is me again then my third degree contacts are_ mehmet,ahmet
我需要选择不是我的一级和二级学位联系人的联系人中的联系人:)
I need to select contacts of my contacts' contacts who are not my first and second degree contact :)
感谢您的回复。
推荐答案
这是我的方法:
-
将我的联系人添加到特别收集的联系人列表中。
Add my contact to a special collected contact list.
对于作为联系人表中 Contact_1
的收集列表中的每个联系人,添加其对应的 Contact_2
,除非该联系人已经在收集的列表中。
For every contact in the collected list as Contact_1
of the contact table, add its corresponding Contact_2
unless that contact is already in the collected list.
重复步骤2,将目标学位次数减一。
Repeat step #2 the number of times that is the target degree number minus one.
再次重复步骤2中的查询,但这一次只需返回结果集(不要将行添加到收集的列表中)。
Repeat the query in step #2 once more, but this time simply return the result set (do not add the rows to the collected list).
脚本:
DECLARE @MyContact varchar(50), @DegreeNumber int;
SET @MyContact = 'gulsah';
SET @DegreeNumber = 3;
DECLARE @CollectedContacts TABLE (Contact varchar(50));
INSERT INTO @CollectedContacts (Contact) VALUES (@MyContact);
WHILE @DegreeNumber > 1 BEGIN
INSERT INTO @CollectedContacts (Contact)
SELECT ct.Contact_2
FROM Contacts_Table ct
INNER JOIN @CollectedContacts cc ON ct.Contact_1 = cc.Contact
LEFT JOIN @CollectedContacts cc2 ON ct.Contact_2 = cc2.Contact
WHERE cc2.Contact IS NULL;
SET @DegreeNumber = @DegreeNumber - 1;
END;
SELECT ct.Contact_2
FROM Contacts_Table ct
INNER JOIN @CollectedContacts cc ON ct.Contact_1 = cc.Contact
LEFT JOIN @CollectedContacts cc2 ON ct.Contact_2 = cc2.Contact
WHERE cc2.Contact IS NULL;
如您所见,学位编号和我的联系人都是可参数化的。我正在使用 varchar
类型的联系人,但是如果需要,当然可以轻松地将其替换为 int
。
As you can see, both the degree number and 'my' contact are parametrisable. I'm using the varchar
type for contacts, but that of course can easily be replaced with int
, if needed.
这篇关于SQL Server:如何选择一级,二级和三级联系人的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!