如何在SQL Server中查询？

I have the ff schema:

我有ff架构:

Faculty

FacultyID             
Name

Tags

TagID
TagValue

tags_faculty

TagID
FacultyID

The question is how to find out the faculty that have the any TagValue that provided in the List<tags>. (please note that due the design of some DBA, the tagvalue was not unique, user has the same tagvalue, but the tagid is different)

问题是如何找出具有List 中提供的任何TagValue的教师。 (请注意,由于某些DBA的设计,tagvalue不是唯一的,用户具有相同的tagvalue,但tagid是不同的)

I am using SQL Server and Linq in C#. But I am open to queries, procedure, linq expression as long as the result is correct.

我在C#中使用SQL Server和Linq。但只要结果正确,我就可以查询,程序,linq表达式。

4 个解决方案

#1

I would do that as an INNER JOIN.

我会这样做作为内部联接。

SELECT f.FacultyID, f.Name
FROM faculty f
INNER JOIN tags_faculty tf ON tf.FacultyID = f.FacultyID
INNER JOIN tags t ON t.tagID = tf.tagID
WHERE t.tagID IN (<list of tagID:s>) 
GROUP BY f.FacultyID

#2

Try the following with LINQ to SQL where TagsToMatch is your in memory list of strings.

使用LINQ to SQL尝试以下操作,其中TagsToMatch是您的内存字符串列表。

List<int> TagsToMatch = tags.Select(tag => TagID);
var matchingFaculty =
     from facultyTag in dc.FacultyTags
     where TagsToMatch.Contains(facultyTag.TagID)
     select facultyTag.Faculty;

If you're using EF 4, it should optomize out your M-M relationship so you could change it to the following. EF 1 did not support the Contains syntax.

如果您使用的是EF 4,它应该优化您的M-M关系,以便您可以将其更改为以下内容。 EF 1不支持Contains语法。

var matchingFaculty = 
     from tag in dc.Faculties
     where !TagsToMatch.Contains(tag.TagID)
     from faculty in tag.Faculties
     select faculty;

Either way, you may want to use a Distinct clause at the end in cases where faculty members are in multiple tags that match.

无论哪种方式,如果教职员工在多个匹配的标签中,您可能希望在最后使用Distinct子句。

#3

SELECT
  *
FROM
  faculty
WHERE
  EXISTS (
    SELECT
      *
    FROM
      tags_faculty
    WHERE
      TagID IN (SELECT TagID FROM tags WHERE TagValue IN (<your list of tag values>))
      AND FacultyID = faculty.FacultyID
  )

There are many options on how to achieve the list of tag values.

有关如何实现标记值列表的许多选项。

You may just generate as the query as a string, and shove the tags in yourself. But you need to be careful of injection attacks, etc.

您可以将查询生成为字符串,然后将标记推送给自己。但你需要小心注射攻击等。

Or you may generate a single string with all your tags separated by , and then use a function to split that out into a table of values. (There are Many such functions available on SO and other sources.)

或者,您可以生成一个单独的字符串,其中所有标记都被分隔,然后使用函数将其拆分为值表。 (在SO和其他来源上有许多这样的功能。)

#4

If I understood your question correctly, then I think this query should help you. May be a bit inefficient query, but check if this gives the result you are looking for -

如果我正确地理解了您的问题,那么我认为这个查询可以帮助您。可能是一个有点低效的查询,但检查这是否给出了您正在寻找的结果 -

select FacultyID, Name
from faculty 
where facultyID in
( select tf.FacultyID from tags_faculty tf
  inner join tags t
  on t.tagID = tf.tagID 
  where t.tagValue in ( <your tag value list> )
)

Maybe an additional DISTINCT clause might help too.

也许额外的DISTINCT子句也可能有所帮助。

#1