SQL Server 将 Table1 中的所有行与 Table2 中的所有行进行匹配

Question

请有人帮我解决这个问题，我有2张 table

员工

EmployeeID   LanguageID
1            1
1            2
1            3
2            1
2            3
3            1
3            2
4            1
4            2
4            3

任务

TaskID   LanguageID   LangaugeRequired
1        1            1
1        2            0
2        1            1
2        2            1
2        3            1
3        2            0
3        3            1

LangaugeID与表langauge相连(此表仅作说明)

   LangaugeID   LanguageName
   1            English
   2            French
   3            Italian

有没有一种可能的方法来进行查询，使员工可以说每项任务所需的所有语言？

例如:

1. Task ID 1只需要LanguageID = 1，所以结果应该是EmployeeID 1,2,3,4
2. 任务 ID 2 需要所有 3 种语言，因此结果应为 EmployeeID 1,4
3. 任务 ID 3 只需要 LanguageID = 3，所以结果应该是 EmployeeID 1,2,4

Answer 1

这是另一种变体:

select t1.taskid, t2.employeeid from
(
    select a.taskid, count(distinct a.languageid) as lang_cnt
    from
    task as a
    where a.LangaugeRequired=1
    group by a.taskid
) as t1
left outer join
(
    select a.taskid, b.employeeid, count(distinct b.languageid) as lang_cnt
    from
    task as a
    inner join
    employee as b
    on (a.LangaugeRequired=1 and a.languageid=b.languageid)
    group by a.taskid, b.employeeid
) as t2
on (t1.taskid=t2.taskid and t1.lang_cnt=t2.lang_cnt)
###
here you can insert where statement, like:
where t1.taskid=1 and t2.employeeid=1
if such query returns row - this employee can work with this task, if no rows - no
###
order by t1.taskid, t2.employeeid

如您所见，此查询创建了两个临时表，然后将它们连接起来。

第一个表(t1)计算每个任务需要多少种语言

第二个表 (t2) 查找所有至少具有任务所需语言的所有员工，按任务/员工分组以查找该员工可以使用多少种语言

主查询执行 LEFT JOIN，因为可能会出现没有员工可以执行任务的情况

这是输出:

task    employee
1       1
1       2
1       3
1       4
2       1
2       4
3       1
3       2
3       4

更新:更简单，但不太正确的变体，因为它不会返回没有可能雇员的任务

select a.taskid, b.employeeid, count(distinct b.languageid) as lang_cnt
from
task as a
inner join
employee as b
on (a.LangaugeRequired=1 and a.languageid=b.languageid)
group by a.taskid, b.employeeid
having count(distinct b.languageid) = (select count(distinct c.languageid) from task as c where c.LangaugeRequired=1 and c.taskid=a.taskid)