SQL Server 2012根据动态位置比较记录和查找缺失记录

Question

我有以下问题，我知道如何在同一张表中找到丢失的记录，但我不知道从哪里丢失的记录：

这是主桌

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 

我已经创建了两个临时表

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 (这个表有数百个位置)。

Answer 1

交叉连接的一个常见用法是在这种类型的问题中，您需要显示所有可能的组合。查询的前半部分创建该值集，然后左联接将数据值与跨多个维度的复合连接附加在一起。

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, '!@#');

http://rextester.com/MNSU54881