我有以下问题，我知道如何从同一张表中找到丢失的记录，但我不知道哪里丢失了记录：
这是主表
Location | Role | Subrole
A | R1 | SR1
A | R1 | SR2
A | R1 | SR3
B | R1 | SR1
B | R1 | SR2
B | R1 | SR3
C | R1 | SR1
C | R1 | SR2
D | R1 | SR1
位置A是主要位置，所有其他位置都应与A进行比较。我的最终目标是：
MasterLocation | MasterRole | MasterSubrole | Location | Role | Subrole
A | R1 | SR1 | B | R1 | SR1
A | R1 | SR2 | B | R1 | SR2
A | R1 | SR3 | B | R1 | SR3
A | R1 | SR1 | C | R1 | SR1
A | R1 | SR2 | C | R1 | SR2
A | R1 | SR3 | C | R1 | MISSING OR NULL
A | R1 | SR1 | D | R1 | SR1
A | R1 | SR2 | D | R1 | MISSING OR NULL
A | R1 | SR3 | D | R1 | MISSING OR NULL
我已经创建了2个临时表
CREATE TABLE #LocA
(
Location Varchar(1),
Role Varchar(2),
SubRole VARCHAR(20)
)
CREATE TABLE #AllOthers
(
Location VARCHAR(1),
Role VARCHAR(2),
SubRole VARCHAR(20)
)
INSERT INTO #LocA
SELECT
Location, Role, SubRole
FROM
TABLE
WHERE
Location = 'A'
INSERT INTO #AllOthers
SELECT
Location, Role, SubRole
FROM
TABLE
WHERE
Location != 'A'
SELECT
A.Location AS MasterLocation,
A.Role AS MasterRole,
A.SubRole AS MasterSubrole
L.Location,
L.Role,
L.Subrole
FROM
#LocA AS A
LEFT JOIN
#Allothers AS L ON A.Role = L.Role
AND A.SubRole = L.Subrole
我正进入（状态
MasterLocation | MasterRole | MasterSubrole | Location | Role|Subrole
A | R1 | SR1 | B | R1 | SR1
A | R1 | SR2 | B | R1 | SR2
A | R1 | SR3 | B | R1 | SR3
A | R1 | SR1 | C | R1 | SR1
A | R1 | SR2 | C | R1 | SR2
A | R1 | SR3 | NULL | NULL | NULL
A | R1 | SR1 | D | R1 | SR1
A | R1 | SR2 | NULL | NULL | NULL
A | R1 | SR3 | NULL | NULL | NULL
因此，我实际上不确定C或D缺少子角色。（此表有数百个位置。）
交叉连接的常见用法是这种类型的问题，您需要显示所有可能的组合。查询的前半部分将创建该组值，然后左联接将数据值附加为跨多个维度的复合联接。
select
m.Location as MasterLocation,
m.Role as MasterRole,
m.Subrole as MasterSubrole,
l.Location,
coalesce(t.Role, 'Missing role') as Role,
coalesce(t.Subrole, 'Missing subrole') as Subrole
from
T as m
cross join
(select distinct Location from T where Location <> 'A') as l
left outer join T as t
on l.Location = t.Location
and m.Role = t.Role
and m.Subrole = t.SubRole
where
m.Location = 'A';
您可能希望避免A在查询中多次提及文字值，因此这是一种替代方法。
select
m.Location as MasterLocation,
m.Role as MasterRole,
m.Subrole as MasterSubrole,
l.Location,
coalesce(t.Role, 'Missing role') as Role,
coalesce(t.Subrole, 'Missing subrole') as Subrole
from
(select * from T where Location = 'A') as m
cross apply
(select distinct Location from T where Location <> m.Location) as l
left outer join T as t
on l.Location = t.Location
and m.Role = t.Role
and m.Subrole = t.SubRole;
如果数据中包含空值，并且想要将其与之匹配，则可能需要以这种方式加入：
on l.Location = t.Location
and coalesce(m.Role, '!@') = coalesce(t.Role, '!@')
and coalesce(m.Subrole, '!@#') = coalesce(t.SubRole, '!@#');
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句