UNION NULLS 到 View 后面可以查询 [英] UNION NULLS into View that can be queried later
问题描述
微软 SQL 服务器:
Microsoft SQL SERVER:
我正在研究技能矩阵问题.下面的例子是一个简化的场景.一家公司有一个工厂，有两个职位:学徒 (APP) 和专家 (EXP).您可以在工作技能表中看到学徒必须能够切割、钻孔和弯曲(10、20、30).专家应该能够切割、钻孔、弯曲、焊接和打开车床(10 到 50).
I am working on a skills matrix problem. The example below is a simplified scenario. A company has a factory with two job titles: Apprentice (APP) and Expert (EXP). You can see in the jobskills table that Apprentices must be able to Cut, Drill, and Bend (10, 20, 30). Experts should be able to Cut, Drill, Bend, Weld, and Turn on a lathe (10 thru 50).
工作技能表:
job_code skill_desc skill_ID
-------- ---------- --------
APP Cut 10
APP Drill 20
APP Bend 30
EXP Cut 10
EXP Drill 20
EXP Bend 30
EXP Weld 40
EXP Turn 50
同样，该公司的三名员工 Al、Tom 和 Bob 并不完全具备应有的所有技能.目标案例是专家 - 鲍勃，他错过了他作为学徒应该发展但没有发展的关键弯曲技能 (30).
Likewise, the company has three employees, Al, Tom, and Bob, who don't exactly have all of the skills they are supposed to have. The target case is the Expert- Bob who is missing the critical Bend skill (30) he should have developed as an Apprentice but didn't.
员工技能表:
empl_ID emplName job_code skill_ID
------- -------- -------- --------
307 Al APP 10
307 Al APP 20
307 Al APP 30
396 Tom APP 10
396 Tom APP 20
426 Bob EXP 10
426 Bob EXP 20
426 Bob EXP 40
426 Bob EXP 50
我正在尝试将外连接空值推送到具有匹配记录的视图中，以便查询技能数据的应用程序可以通过 empl_ID=426 查找 Bob 并查看他当前的技能和他缺少的技能.最终，我需要进入下面的结果视图:
I'm trying to push the outer join nulls into a view with the matching records so that an application that queries skill data can look up Bob by empl_ID=426 and see his current skills AND his missing skills. Ultimately, I need to get to the result view below:
预期结果:
empl_ID emplName job_code skill_ID
------- -------- -------- --------
426 Bob EXP 10
426 Bob EXP 20
426 {null} {null} 30
426 Bob EXP 40
426 Bob EXP 50
我尝试过这样的事情:
(select t1.empl_ID, t1.emplName, t2.job_code, t1.skill_ID
from emplskills t1, jobskills t2
where t1.skill_ID = t2.skill_ID AND t1.job_code = t2.job_code)
UNION
(select t1.empl_ID, t1.emplName, t2.job_code, t2.skill_ID
from jobskills t2 left outer join emplskills t1
on t2.skill_ID = t1.skill_ID AND t2.job_code = t1.job_code
where t1.empl_ID is null);
我使用 NULL (30) 技能行获得了预期的笛卡尔连接.
I get the expected cartesian join with the NULL (30) skill row.
UNION 连接结果:
UNION join result:
empl_ID emplName job_code skill_ID
------- -------- -------- --------
{null} {null} EXP 30
307 Al APP 10
307 Al APP 20
307 Al APP 30
396 Tom APP 10
396 Tom APP 20
426 Bob EXP 10
426 Bob EXP 20
426 Bob EXP 40
426 Bob EXP 50
但是这里有两个问题:(a) 当我查询视图以查看 Bob 的技能时(选择 where empl_ID=426)，我不会得到我需要查看的 NULL (30) 行.(b) 您会注意到学徒汤姆也缺少弯曲技能 (30).那么 NULL (30) 行属于谁?
But there are two problems here: (a) When I query the view to see Bob's skills (select where empl_ID=426) I'm not going to get the NULL (30) row I need to see. (b) You'll notice that the Apprentice- Tom is also missing the Bending skill (30). So who does the NULL (30) row belong to?
是否有可能在 UNION 中建立一个虚拟列来传播这些与 empl_ID 关联的缺失 NULL，就像上面期望的结果一样?
Is it even possible to stand up one dummy column in the UNION to propagate these missing NULLs associated with the empl_ID like in the desired result above?
TIA，约翰
推荐答案
你想使用JOIN来获得每个工作所需的技能，然后使用LEFT JOIN找到找出缺少哪一个.
You want use JOIN to get required skills for each job, then use the LEFT JOIN to found out which one are missing.
WITH required_skills as (
SELECT DISTINCT e.empl_ID, e.job_code, j.skill_ID
FROM emplskills e
JOIN jobskills j
ON e.job_code = j.job_code
)
SELECT *
FROM required_skills r
LEFT JOIN emplskills e
ON r.empl_ID = e.empl_ID
AND r.skill_ID = e.skill_ID
这篇关于UNION NULLS 到 View 后面可以查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
