sql-server - What-IF 视图比较器的 UNION NULLS 后续行动
问题描述
这个问题是对以下问题的跟进:
在前面的帖子中,我提到了一家有工作技能表和员工技能表的公司。对于这个讨论,让我们只使用其中两个员工......
jobskills + emplskills
job_code skill_desc skill_ID | empl_ID emplName job_code skill_ID
-------- ---------- -------- | ------- -------- -------- --------
APP Cut 10 | 396 Tom APP 10
APP Drill 20 | 396 Tom APP 30
APP Bend 30 | 426 Bob EXP 10
EXP Cut 10 | 426 Bob EXP 20
EXP Drill 20 | 426 Bob EXP 40
EXP Bend 30 | 426 Bob EXP 50
EXP Weld 40 |
EXP Turn 50 |
+
在jobskills
表格中,你会看到APP(学徒)职称需要3种技能——切割、钻孔和折弯(10、20、30)。同样,EXP(专家)职位需要 5 项技能(10 到 50)。
在emplskills
表格中,您将看到 Tom 是一名学徒 (APP),他的职称应该具备所有 3 项技能,但只有 2 项。他缺少钻孔技能 (20)。以同样的方式:作为专家 (EXP) 的 Bob 应该在其职位中拥有所有 5 项技能,但缺少 Bend 技能 (30)。
在上一篇文章中,@JuanCarlosOropeza 创建了这个查询(我重新定义了一些列),它通过外连接每个 empl_ID 的 NULL 来列出员工拥有和不拥有的所有技能。
WITH required_skills as
(
SELECT DISTINCT
e.empl_ID, e.job_code, j.skill_ID, j.skill_desc
FROM
emplskills e
JOIN
jobskills j ON e.job_code = j.job_code
)
SELECT
r.empl_ID, e.emplName, r.job_code, r.skill_ID, r.skill_desc
FROM
required_skills r
LEFT JOIN
emplskills e ON r.empl_ID = e.empl_ID
AND r.skill_ID = e.skill_ID;
上面的查询产生了我需要将员工的技能与他自己的职位进行比较的结果:
查询结果:
empl_ID emplName job_code skill_ID skill_desc
------- -------- -------- -------- ----------
396 Tom APP 10 Cut
396 Tom APP 20 Drill
396 {null} APP 30 Bend
426 Bob EXP 10 Cut
426 Bob EXP 20 Drill
426 {null} EXP 30 Bend
426 Bob EXP 40 Weld
426 Bob EXP 50 Turn
此查询的重点是使用此查询作为源视图的培训应用程序。如果用户根据 empl_ID 进行过滤,她可以查看 Tom (396) 并发现他缺少 Bend 技能 (30)
过滤汤姆(396)
empl_ID emplName job_code skill_ID skill_desc
------- -------- -------- -------- ----------
396 Tom APP 10 Cut
396 Tom APP 20 Drill
396 {null} APP 30 Bend
...现在是新问题:
在培训应用程序中,我需要将员工与任何职位进行比较。因此,我需要将每个可能的工作代码组合和每个可能的技能组合都与每个员工联系起来。
我真正需要的查询结果是这样的:
empl_ID emplName job_code skill_ID skill_desc
------- -------- -------- -------- ----------
396 Tom APP 10 Cut
396 Tom APP 20 Drill
396 {null} APP 30 Bend
396 Tom EXP 10 Cut
396 Tom EXP 20 Drill
396 {null} EXP 30 Bend
396 {null} EXP 40 Weld
396 {null} EXP 50 Turn
426 Bob APP 10 Cut
426 Bob APP 20 Drill
426 Bob APP 30 Bend
426 Bob EXP 10 Cut
426 Bob EXP 20 Drill
426 {null} EXP 30 Bend
426 Bob EXP 40 Weld
426 Bob EXP 50 Turn
现在,当用户过滤时,他们可以将员工与不同职位的要求进行比较。对于学徒汤姆来说,学徒头衔的原始过滤仍然有效:
Filter on Tom(396) and JOB CODE (APP)
empl_ID emplName job_code skill_ID skill_desc
------- -------- -------- -------- ----------
396 Tom APP 10 Cut
396 Tom APP 20 Drill
396 {null} APP 30 Bend
但是,如果正在考虑将 Tom 提升为 EXPERT,经理可以立即看到 Tom 缺乏哪些专家技能:
Filter on Tom(396) and JOB CODE (EXP)
empl_ID emplName job_code skill_ID skill_desc
------- -------- -------- -------- ----------
396 Tom EXP 10 Cut
396 Tom EXP 20 Drill
396 {null} EXP 30 Bend
396 {null} EXP 40 Weld
396 {null} EXP 50 Turn
有没有办法改变上面的查询来完成这个?到目前为止,我对你们提供的帮助非常感谢。这是我目前工作的现实挑战。我们有大约 150 名实际工厂工人,拥有 40 多个职称。其中一些职位有超过 3 打的相关技能 ID。
最后,我尝试使用上述查询 CREATE VIEW AS () 但无法获得正确的语法。在我们使新查询正常工作后,您能帮我将其转换为 CREATE VIEW 语句吗?
再次感谢,约翰
解决方案
您确实应该以可消耗的格式发布数据。幸运的是,Juan Carlos Opreza 在您之前的问题中为您做到了这一点。我拿走了他并从您的其他问题中删除了“额外”数据。
CREATE TABLE emplskills
([empl_ID] int, [emplName] varchar(3), [job_code] varchar(3), [skill_ID] int)
;
INSERT INTO emplskills
([empl_ID], [emplName], [job_code], [skill_ID])
VALUES
(396, 'Tom', 'APP', 10),
(396, 'Tom', 'APP', 20),
(426, 'Bob', 'EXP', 10),
(426, 'Bob', 'EXP', 20),
(426, 'Bob', 'EXP', 40),
(426, 'Bob', 'EXP', 50)
;
CREATE TABLE jobskills
([job_code] varchar(3), [skill_desc] varchar(5), [skill_ID] int)
;
INSERT INTO jobskills
([job_code], [skill_desc], [skill_ID])
VALUES
('APP', 'Cut', 10),
('APP', 'Drill', 20),
('APP', 'Bend', 30),
('EXP', 'Cut', 10),
('EXP', 'Drill', 20),
('EXP', 'Bend', 30),
('EXP', 'Weld', 40),
('EXP', 'Turn', 50)
;
现在我们有了一些具体而明确的东西可以使用,让我们看看它是如何工作的。由于左连接不起作用,因为您在没有匹配项时填充 empl_ID,因此您需要一些不同的方法。解决这个问题的方法不止一种。我使用交叉连接来生成所有员工和工作技能的列表。这为我们提供了您可以在左连接中使用的列表。
我不得不说你需要阅读规范化,因为这些结构相当痛苦。
select AllEmpSkills.empl_ID
, es.emplName
, js.job_code
, js.skill_ID
, js.skill_desc
from jobskills js
cross join
(
select distinct empl_ID from emplskills
) AllEmpSkills
left join emplskills es on es.empl_ID = AllEmpSkills.empl_ID
and es.skill_ID = js.skill_ID
order by AllEmpSkills.empl_ID
, js.job_code
, js.skill_ID
推荐阅读
- c++ - 在 std::function 中获取 std::future 的结果类型
- unix - stringtie 指南参考注释错误
- c++ - 如何以非阻塞、不可中断的方式从类中运行 c++ 函数
- drupal - DDEV Drupal 环境中的私有文件
- javascript - 从 C# DLL 调用 chrome 选项卡中的 javascript 函数
- python - Django Celery 在抛出自定义异常时出现酸洗错误
- python - 将 PHP 请求转换为 Python
- vba - VBA 的 selenium webdriver 中的过时元素引用异常
- .net - 在 VB.NET 中上传 FileNet 文件
- java - UI Automator java虚拟机Mac上的错误