SQL:比较列以获取精确匹配的集合

Question

我有一个看起来像这样的表:

RN    ID1     ID2     Name            Source
1   W76544  945297  1_W_HO          HO
2   W76544  945297  1_W_INT         Int
1   W76547  945299  3_W_HO          HO
2   W76547  945678  3_W_INT         Int
1   W76561  NULL    Dev_U_W         AD
2   W76561  207283  Dev_W_HO        HO
3   W76561  207283  Dev_W_INT       Int
1   W76562  207284  Dev_R_HO        HO
2   W76562  207284  Dev_R_INT       Int
3   W76562  NULL    Dev_U_R         AD
1   W76563  NULL    Prd_U_W         AD
2   W76563  NULL    Prd_W_HO        HO
3   W76563  NULL    Prd_W_INT       Int

我试图弄清楚我们如何确定 ID1 和 ID2 集之间的精确匹配。例如，这个例子是一个完全匹配:

RN    ID1     ID2     Name            Source
1   *W76544 945297* 1_W_HO          HO
2   *W76544 945297* 1_W_INT         Int

我希望结果如下所示:

RN    ID1     ID2  Matched   Name            Source
1   W76544  945297   Yes    1_W_HO          HO
2   W76544  945297   Yes    1_W_INT         Int
1   W76547  945299   No     3_W_HO          HO
2   W76547  945678   No     3_W_INT         Int
1   W76561  NULL     No     Dev_U_W         AD
2   W76561  207283   No     Dev_W_HO        HO
3   W76561  207283   No     Dev_W_INT       Int
1   W76562  207284   No     Dev_R_HO        HO
2   W76562  207284   No     Dev_R_INT       Int
3   W76562  NULL     No     Dev_U_R         AD
1   W76563  NULL     Empty  Prd_U_W         AD
2   W76563  NULL     Empty  Prd_W_HO        HO
3   W76563  NULL     Empty  Prd_W_INT       Int

澄清... Match = 'Yes' 当具有相同 ID1 的所有组与具有相同 ID2 的组匹配时。 Match = 'No' 当具有相同 ID1 的组不全部匹配到类似的 ID2 或某些 ID1 的匹配但集合中的其他组根本不匹配 ID2 时。当具有相同 ID1 的所有组与 ID2 完全不匹配时，匹配 = 'Empty'。

附言RN 是由 ID1 分区和排序的 row_number

谢谢!!!

@BaconBits ，这似乎符合您的查询:

1   W10151820   NULL    No  DEV_U_W         AD
2   W10151820   212405  Yes DEV_W_HO    HO
3   W10151820   212405  Yes DEV_W_INTL  Int

Answer 1

试试这个:

Create table t(id int, c char(1))

Insert into t values
(1, 'a'),
(1, 'a'),
(2, 'b'),
(2, null),
(3, null),
(3, null),
(4, 'c'),
(4, 'd')

;with cte as(
select id, count(*) c1, count(c) c2, count(distinct c) c3 from t
group by id)
select t.id, t.c, ca.m from t
Cross apply(select case when c2 = 0 and c3 = 0 then 'empty'
            when c1 = c2 and c3 = 1 then 'yes'
            else 'no' end as m
            from cte where cte.id = t.id) ca

输出:

id  c       m
1   a       yes
1   a       yes
2   b       no
2   (null)  no
3   (null)  empty
3   (null)  empty
4   c       no
4   d       no