如何在SQL中查找多行匹配？

Question

以下是一张表格：

CREATE TABLE T1 (
    A varchar(2),
    B varchar(2)
);

INSERT INTO T1 VALUES 
    ('aa', 'm'), ('aa', 'n'),
    ('bb', 'n'), ('bb', 'o'),
    ('cc', 'n'), ('cc', 'o'),
    ('dd', 'c'), ('dd', 'a'), ('dd', 'r'),
    ('ee', 'a'), ('ee', 'c'), ('ee', 'r')

A   | B
----+----
aa  | m
aa  | n
bb  | n
bb  | o
cc  | n
cc  | o
dd  | c
dd  | a
dd  | r
ee  | a
ee  | c
ee  | r

如何选择和分组与B中所有对应值匹配的A中的值。例如，bb和cc组成一组，因为它们都包含“n”和“o”。

因此结果将是：

Group | A
----------
1     | bb
1     | cc
2     | dd
2     | ee

- Josh

---

你的示例中，群组的最大大小是多少（例如2,3）？ - Code Magician

没有最大尺寸。 - Josh

Answer 1

这里有一种方法：首先计算匹配的“集合”，其中一个集合是两个匹配的A。然后计算“头部”，或者是同一组中集合的最低A。使用dense_rank可以对头部进行编号，然后再将集合列表连接回来，创建所有集合成员的列表。

查询在SE Data上。

; with  groups  as
        (
        select  distinct A
        from    @t
        )
,       vals as
        (
        select  distinct B
        from    @t
        )
,       sets as
        (
        select  g1.A as g1
        ,       g2.A as g2
        from    groups g1
        join    groups g2
        on      g1.A < g2.A
        cross join
                vals v
        left join
                @t v1
        on      g1.A = v1.A
                and v.B = v1.B
        left join
                @t v2
        on      g2.A = v2.A
                and v.B = v2.B
        group by
                g1.A
        ,       g2.A
        having  count(case when isnull(v1.B,'') <> isnull(v2.B,'') then 1 end) = 0
        )
,       heads as
        (
        select  s1.g1
        ,       s1.g2
        ,       head.head
        from    sets s1
        cross apply
                (
                select  min(g1) as head
                from    sets s2
                where   s1.g2 = s2.g2
                ) head
        )
select  distinct dense_rank() over (order by h.head)
,       g.g
from    (
        select  distinct head
        from    heads
        ) h
left join
        (
        select  g1 as g
        ,       head
        from    heads
        union all
        select  g2
        ,       head
        from    heads
        ) g
on      h.head = g.head

- Andomar

---

谢谢，这可行，但性能不如我所需。不过这将成为一个很好的起点。 - Josh

Answer 2

SQL Server 2008拥有EXCEPT和INTERSECT函数，可以使用。虽然这不完全符合您想要的格式，而且我无法保证对大数据集的性能，但也许它可以给您一个起点。

SELECT DISTINCT
    T1.A,
    T2.A
FROM
    T1 AS T1
INNER JOIN T1 AS T2 ON T2.A > T1.A
WHERE
    NOT EXISTS
    (
    SELECT
        B
    FROM
        T1 AS T3
    WHERE
        T3.A = T1.A
    EXCEPT
    SELECT
        B
    FROM
        T1 AS T4
    WHERE
        T4.A = T2.A
    ) AND
    NOT EXISTS
    (
    SELECT
        B
    FROM
        T1 AS T3
    WHERE
        T3.A = T2.A
    EXCEPT
    SELECT
        B
    FROM
        T1 AS T4
    WHERE
        T4.A = T1.A
    )

根据您的数据，您还可以使用分隔符和特定顺序生成一些连接字符串，然后进行比较。

- Tom H

---

+1 连接方法的性能更好，虽然集合方法更有趣 :) - Andomar

Answer 3

你需要的关系运算符是除法，通常被称为"提供所有零件的供应商"。

实际上，除法有大约八种不同的形式，而SQL语言并没有直接实现它们。然而，它们都可以使用现有的SQL结构进行重建：请参阅本文以了解更流行的方法。需要考虑的事项包括：精确除法还是带余数；如何处理空除数。

- onedaywhen