在两个结果集中查找“缺失”行

I've been struggling with this for days so apologies if I've over-confused things...

几天来我一直在苦苦挣扎,如果我过度混淆了事情就道歉......

I have a series of tables that define:

我有一系列表定义:

1. Groups of skills

技能组

2. Skills
3. Skills within the above groups (many-to-many)

上述群体中的技能(多对多)

4. Job roles
5. The skills with those job roles (many-to-many)

这些工作角色的技能(多对多)

The structure of the database looks like this:

数据库的结构如下所示:

I need to create a result set that displays the following data:

我需要创建一个显示以下数据的结果集:

For all of the job roles, show all of the skills within those job roles. However, also include skills within the respective groups that were not included in each role (indicated with NULL or by any other method).

对于所有工作角色,显示这些工作角色中的所有技能。但是,还包括各个组中未包含在每个角色中的技能(用NULL或任何其他方法表示)。

Please see this working code to create the tables and data. SQL Fiddle
Apologies for the length, I did lots of inserts to create a realistic example.

请参阅此工作代码以创建表和数据。 SQL Fiddle Apologies的长度,我做了很多插入来创建一个现实的例子。

Notice that the PowerPoint skill is not added to the HR Manager role, but other skills from that same group are added. Also notice that the Recruitment Policy skill is not added to the Software Manager role, but I do not need to see this gap because no other skills in that group exist in the role.

请注意,PowerPoint技能未添加到HR Manager角色,但会添加来自同一组的其他技能。另请注意,Recruitment Policy技能未添加到Software Manager角色,但我不需要看到此差距,因为该角色中不存在该组中的其他技能。

The results I would aim for would resemble this (excludes Super Star role for brevity):

我想要的结果将类似于此(为简洁起见,不包括超级明星角色):

RoleTitle               GroupTitle                 SkillTitle               SkillIsInRole
----------------------- -------------------------- --------------------------------------
Software Manager        Microsoft Office           Excel                    1
Software Manager        Microsoft Office           Word                     1
Software Manager        Microsoft Office           PowerPoint               1
Software Manager        Microsoft SQL Server       Query Design             1
Software Manager        Microsoft SQL Server       Stored Procedures        1
Software Manager        Microsoft SQL Server       Failover Clustering      1
HR Manager              Microsoft Office           Excel                    1
HR Manager              Microsoft Office           Word                     1
HR Manager              Microsoft Office           PowerPoint               NULL <-- not added to role but exists in same group as other used skills
HR Manager              HR                         Recruitment Policy       1

2 个解决方案

#1

Getting all skills for a group related to a role is somewhat simple and is handled in the relatively self explanatory roles cte below. From this the only way I can think of obtaining whether the skill was related 'directly' to the role is by OUTER APPLYING the result set to the result set of actual skills for role.

获得与角色相关的小组的所有技能有点简单,并在下面相对自我解释的角色中处理。从中我可以想到获得技能是否与“角色”直接相关的唯一方法是将结果集应用于角色的实际技能结果集。

;WITH skills AS
(
  SELECT g.GroupId, g.GroupTitle, s.SkillId, s.SkillTitle
  FROM @tbl_GroupsSkills gs
  INNER JOIN @tbl_Groups g ON g.GroupId = gs.GroupId
  INNER JOIN @tbl_Skills s ON s.SkillId = gs.SkillId
)
, roles AS
(
  SELECT DISTINCT jr.Id RoleId, jr.RoleTitle, gs.GroupId
  FROM @tbl_jobroles jr
  INNER JOIN @tbl_rolesskills rs ON rs.RoleId = jr.ID
  INNER JOIN @tbl_GroupsSkills gs ON gs.LinkId = rs.LinkId
)
SELECT 
    roles.RoleTitle, 
    skills.GroupTitle, 
    skills.SkillTitle,
    t.SkillIsInRole
FROM skills
JOIN roles ON roles.GroupId = skills.GroupId
OUTER APPLY
(
  SELECT 1 SkillIsInRole
  FROM @tbl_rolesskills rs 
  INNER JOIN @tbl_jobroles r ON rs.RoleID = r.ID 
  INNER JOIN @tbl_groupsskills gs ON gs.LinkID = rs.LinkID 
  INNER JOIN @tbl_groups g ON g.groupID = gs.GroupID 
  INNER JOIN @tbl_skills s ON s.skillID = gs.SkillID
  WHERE s.SkillId = skills.SkillId
  AND g.GroupId = skills.GroupId
  AND r.Id = roles.RoleId
) t
ORDER BY roles.RoleTitle, skills.GroupTitle, skills.SkillTitle

Edit: the OUTER APPLY could be handled with a LEFT JOIN

编辑:可以使用LEFT JOIN处理OUTER APPLY

LEFT JOIN (
  SELECT s.SkillId, g.GroupId, r.Id RoleId, 1 SkillIsInRole
  FROM @tbl_rolesskills rs 
  INNER JOIN @tbl_jobroles r ON rs.RoleID = r.ID 
  INNER JOIN @tbl_groupsskills gs ON gs.LinkID = rs.LinkID 
  INNER JOIN @tbl_groups g ON g.groupID = gs.GroupID 
  INNER JOIN @tbl_skills s ON s.skillID = gs.SkillID
) t ON t.SkillId = skills.SkillId
   AND t.GroupId = skills.GroupId
   AND t.RoleId = roles.RoleId

demo

#2

I think you have to prepare a set of all possible combinations Roles from one side and Group-skills from the other. This is done by making Decart multiplication (usually done by CROSS JOIN). As a result you will have a list of each role combined with a list of all possible Group-skills combinations. After that you can LEFT JOIN this result with table tbl_RolesSkills. This will give what you need. You can do this by using CTE or sub query.

我认为你必须从一方准备一组所有可能的组合角色,从另一方面准备组技能。这是通过进行Decart乘法(通常由CROSS JOIN完成)来完成的。因此,您将获得每个角色的列表以及所有可能的群组技能组合的列表。之后,您可以使用表tbl_RolesSkills LEFT JOIN此结果。这将满足您的需求。您可以使用CTE或子查询来完成此操作。

Have to looks like this example

必须看起来像这个例子

Actually CROSS JOIN is not needed, I missed part with "specific roles have only a specific group sets". Only one sub-query, can be done also with CTE (Common table expression).

实际上不需要CROSS JOIN,我错过了“特定角色只有一个特定的组集”。只有一个子查询,也可以用CTE(公用表表达式)完成。

I also exclude "Super Star" role. If you want to add it just remove WHERE section.

我也排除了“超级明星”的角色。如果要添加它,只需删除WHERE部分。