如何匹配/比较SQL Server 2008中两个结果集中的值？

I'm working on a employee booking application. I've got two different entities Projects and Users that are both assigned a variable number of Skills.

我正在做一份员工预订申请。我有两个不同的实体项目和用户都被分配了可变数量的技能。

I've got a Skills table with the various skills (columns: id, name) I register the user skills in a table called UserSkills (with two foreign key columns: fk_user and fk_skill) I register the project skills in another table called ProjectSkills (with two foreign key columns: fk_project and fk_skill).

我有一个具有各种技能的技能表(列:id,名称)我在一个名为UserSkills的表中注册用户技能(有两个外键列:fk_user和fk_skill)我在另一个名为ProjectSkills的表中注册项目技能(有两个外键列:fk_project和fk_skill)。

A project can require maybe 6 different skills and users when registering sets up their Skills aswell.

在注册设置技能时,项目可能需要6种不同的技能和用户。

The tricky part is when I have to find users for my Projects based on their skills. I'm only interested in users that meet that have ALL the skills required by the project. Users are ofcause allowed to have more skilled then required.

棘手的部分是我必须根据他们的技能为我的项目找到用户。我只对符合项目要求的所有技能的用户感兴趣。用户被允许拥有更多技能,然后需要。

The following code will not work, (and even if it did, would not be very performance friendly), but it illustrates my idea:

以下代码不起作用(即使它确实如此,也不会非常友好),但它说明了我的想法:

SELECT * FROM Users u WHERE 
    ( SELECT us.fk_skill FROM UserSkills us WHERE us.fk_user = u.id ) 
        >= 
    ( SELECT ps.fk_skill FROM ProjectSkills ps WHERE ps.fk_project = [some_id] )

I'm thinking about making my own function that takes two TABLE-variables, and then working out the comparisson in that (kind of a modified IN-function), but I'd rather find a solution that's more performance friendly.

我正在考虑制作我自己的函数,该函数需要两个TABLE变量,然后计算出comparisson(一种修改后的IN函数),但我宁愿找到一个性能更友好的解决方案。

I'm developing on SQL Server 2008.

我正在开发SQL Server 2008。

I really appreciate any ideas or suggestions on this. Thanks!

我真的很感激任何想法或建议。谢谢!

2 个解决方案

#1

SELECT  *
FROM    Users u
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    ProjectSkill ps
        WHERE   ps.pk_project = @someid
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    UserSkills us
                WHERE   us.fk_user = u.id
                        AND us.fk_skill = ps.fk_skill
                )
        )

#2

--  Assumes existance of variable @ProjectId, specifying
--  which project to analyze
SELECT us.UserId
 from UserSkills us
  inner join ProjectSkills ps
   on ps.SkillId = us.SkillId
    and ps.ProjectId = @ProjectId
 group by us.UserId
 having count(*) = (select count(*)
                     from ProjectSkills
                     where ProjectId = @ProjectId)

You'd want to test an debug this, as I have no test data to run it through. Ditto for indexing to optimize it.

你想测试这个调试,因为我没有测试数据来运行它。同样用于索引以优化它。

(Now to post, and see if someone's come up with a better way--there should be something more subtle and effective than this.)

(现在发布,看看有人提出了更好的方法 - 应该有更微妙和有效的东西。)

#1

SELECT  *
FROM    Users u
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    ProjectSkill ps
        WHERE   ps.pk_project = @someid
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    UserSkills us
                WHERE   us.fk_user = u.id
                        AND us.fk_skill = ps.fk_skill
                )
        )

#2

--  Assumes existance of variable @ProjectId, specifying
--  which project to analyze
SELECT us.UserId
 from UserSkills us
  inner join ProjectSkills ps
   on ps.SkillId = us.SkillId
    and ps.ProjectId = @ProjectId
 group by us.UserId
 having count(*) = (select count(*)
                     from ProjectSkills
                     where ProjectId = @ProjectId)