寻找“丢失”在两个结果集行

我一直在挣扎与这几天如此道歉,如果我有过困惑的事情...

我有一系列的表定义:

  1. 技能组
  2. 技能
  3. 上述基团中的技能(多到多)
  4. 工作角色
  5. 这些工作角色的技能(许多到许多)

数据库的结构是这样的: 寻找“丢失”在两个结果集行


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

对于所有的工作角色,显示这些工作角色中的所有技能。 然而,还包括不包括在各角色各自的组内的技能(与NULL或通过任何其它方法表示)。

请参阅此工作代码来创建表和数据。 SQL小提琴
对于长道歉,我做了很多插入创建一个现实的例子。

注意, PowerPoint技术人员不被添加到该HR Manager的作用,但是从同一组中的其它技能相加。 还要注意, Recruitment Policy技能不会被添加到Software Manager的角色,但我并不需要看到这种差距因为角色存在该组中没有其他技能。

我会瞄准的结果将类似这样(不包括为简洁起见超级明星角色):

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

--------------解决方案-------------

让所有技能相关的角色的组是有点简单,相对自明处理roles CTE下方。 从这个我能想到获得的技能是否与“直接”的角色的唯一方法是OUTER APPLY ING结果集到结果集的实际技能的作用。

;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

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

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

演示

我认为你必须准备一套从一个侧面所有可能的组合角色和集团技能与其他的。 这是通过使Decart乘法(一般是由交叉完成JOIN)完成。 因此,您将有每个角色与所有可能的集团技能组合的列表组合列表。 之后,你可以LEFT JOIN这个结果与表tbl_RolesSkills。 这会给你所需要的。 您可以通过使用CTE或子查询做到这一点。

有看起来像这样的例子

其实不需要CROSS JOIN,我错过了部分与“特定角色只有特定组套”。 只有一个子查询,可与CTE(公共表表达式)也做了。

我也排除“超级明星”的角色。 如果你想添加它只是删除WHERE部分。

分类:SQL服务器 时间:2015-03-15 人气:0
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 1.823 (s). 10 q(s)