给定以下内容：

declare @a table
(
    pkid int,
    value int
)

declare @b table
(
    otherID int,
    value int
)

insert into @a values (1, 1000)
insert into @a values (1, 1001)
insert into @a values (2, 1000)
insert into @a values (2, 1001)
insert into @a values (2, 1002)

insert into @b values (-1, 1000)
insert into @b values (-1, 1001)
insert into @b values (-1, 1002)

如何查询@a中与@b完全匹配的所有值？

{{a.pkid = 1，@ b.otherID = -1}将不会返回(3个值中只有2个匹配)

将返回{{a.pkid = 2，@ b.otherID = -1}(3个值中的3个匹配)

重构表可以是一种选择。

编辑：我已经从詹姆斯和汤姆H的答案中获得成功。

当我在@b中添加另一种情况时，它们有些不足。

insert into @b values (-2, 1000)

假设这应该返回另外两行({@a.pkid = 1，@ b.otherID = -2}和{@a.pkid = 2，@ b.otherID = -2}，则不会工作，但是，对于我的项目，这不是问题。

解决方案

有几种方法可以做到这一点，但一个简单的方法就是将联合视图创建为

create view qryMyUinion as
select * from table1 
union all
select * from table2

小心使用全部并集，而不是简单的并集，因为这样会忽略重复项

然后做这个

select count( * ), [field list here] 
from qryMyUnion
group by [field list here]
having count( * ) > 1

Union和Haveing语句通常是标准SQL中最容易被忽略的部分，但是它们可以解决很多棘手的问题，这些问题否则需要过程代码

可能不是最便宜的方法：

SELECT a.pkId,b.otherId FROM
    (SELECT a.pkId,CHECKSUM_AGG(DISTINCT a.value) as 'ValueHash' FROM @a a GROUP BY a.pkId) a
    INNER JOIN (SELECT b.otherId,CHECKSUM_AGG(DISTINCT b.value) as 'ValueHash' FROM @b b GROUP BY b.otherId) b
ON a.ValueHash = b.ValueHash

我们可以看到，基本上，我正在为每个表创建一个新的结果集，这些结果集代表每个表中每个ID的一组值的一个值，并仅在它们匹配的地方加入。

如果我们尝试仅返回完整的记录集，则可以尝试此操作。我绝对会建议使用有意义的别名，不过...

Cervo是正确的，我们需要进行额外的检查以确保a是b的精确匹配，而不是b的超集。在这一点上，这更是一个笨拙的解决方案，因此这仅在其他解决方案中的分析功能不起作用的情况下才是合理的。

select 
    a.pkid,
    a.value
from
    @a a
where
    a.pkid in
    (
    select
        pkid
    from
        (
        select 
            c.pkid,
            c.otherid,
            count(*) matching_count
        from 
            (
            select 
                a.pkid,
                a.value,
                b.otherid
            from 
                @a a inner join @b b 
                on a.value = b.value
            ) c
        group by 
            c.pkid,
            c.otherid
        ) d
        inner join
        (
        select 
            b.otherid,
            count(*) b_record_count
        from
            @b b
        group by
            b.otherid
        ) e
        on d.otherid = e.otherid
        and d.matching_count = e.b_record_count
        inner join
        (
        select 
            a.pkid match_pkid,
            count(*) a_record_count
        from
            @a a
        group by
            a.pkid
        ) f
        on d.pkid = f.match_pkid
        and d.matching_count = f.a_record_count
    )

适用于示例，我认为它适用于所有情况，但我尚未对其进行全面测试：

SELECT
    SQ1.pkid
FROM
    (
        SELECT
            a.pkid, COUNT(*) AS cnt
        FROM
            @a AS a
        GROUP BY
            a.pkid
    ) SQ1
INNER JOIN
    (
        SELECT
            a1.pkid, b1.otherID, COUNT(*) AS cnt
        FROM
            @a AS a1
        INNER JOIN @b AS b1 ON b1.value = a1.value
        GROUP BY
            a1.pkid, b1.otherID
    ) SQ2 ON
        SQ2.pkid = SQ1.pkid AND
        SQ2.cnt = SQ1.cnt
INNER JOIN
    (
        SELECT
            b2.otherID, COUNT(*) AS cnt
        FROM
            @b AS b2
        GROUP BY
            b2.otherID
    ) SQ3 ON
        SQ3.otherID = SQ2.otherID AND
        SQ3.cnt = SQ1.cnt

正如CQ所说的，我们只需要一个简单的内部联接。

Select * -- all columns but only from #a
from #a 
inner join #b 
on #a.value = #b.value -- only return matching rows
where #a.pkid  = 2

-- Note, only works as long as no duplicate values are allowed in either table
DECLARE @validcomparisons TABLE (
    pkid    INT,
    otherid INT,
    num INT
)

INSERT INTO @validcomparisons (pkid, otherid, num)
SELECT  a.pkid, b.otherid, A.cnt
FROM    (select pkid, count(*) as cnt FROM @a group by pkid) a
INNER JOIN  (select otherid, count(*) as cnt from @b group by otherid) b 
    ON  b.cnt = a.cnt

DECLARE @comparison TABLE (
    pkid    INT,
    otherid INT,
    same    INT)

insert into @comparison(pkid, otherid, same)
SELECT a.pkid, b.otherid, count(*)
FROM    @a a
INNER JOIN  @b b
    ON  a.value = b.value
GROUP BY    a.pkid, b.otherid

SELECT  COMP.PKID, COMP.OTHERID
FROM    @comparison comp
INNER JOIN  @validcomparisons val
    ON  comp.pkid = val.pkid
    AND comp.otherid = val.otherid
    AND comp.same = val.num

以下查询为我们提供所需的结果：

select A.pkid, B.otherId
    from @a A, @b B 
    where A.value = B.value
    group by A.pkid, B.otherId
    having count(B.value) = (
        select count(*) from @b BB where B.otherId = BB.otherId)

要进一步迭代这一点：

select a.*
from @a a 
inner join @b b on a.value = b.value

这将返回@a中与@b匹配的所有值

我添加了一些额外的测试用例。我们可以通过更改我们在使用聚集不同的关键字的方式更改重复的处理。基本上，我得到了一个匹配项的计数，并将其与每个@a和@b中所需匹配项的计数进行比较。

declare @a table
(
    pkid int,
    value int
)

declare @b table
(
    otherID int,
    value int
)

insert into @a values (1, 1000)
insert into @a values (1, 1001)

insert into @a values (2, 1000)
insert into @a values (2, 1001)
insert into @a values (2, 1002)

insert into @a values (3, 1000)
insert into @a values (3, 1001)
insert into @a values (3, 1001)

insert into @a values (4, 1000)
insert into @a values (4, 1000)
insert into @a values (4, 1001)

insert into @b values (-1, 1000)
insert into @b values (-1, 1001)
insert into @b values (-1, 1002)

insert into @b values (-2, 1001)
insert into @b values (-2, 1002)

insert into @b values (-3, 1000)
insert into @b values (-3, 1001)
insert into @b values (-3, 1001)

SELECT Matches.pkid, Matches.otherId
FROM
(
    SELECT a.pkid, b.otherId, n = COUNT(*)
    FROM @a a
    INNER JOIN @b b
        ON a.Value = b.Value
    GROUP BY a.pkid, b.otherId
) AS Matches

INNER JOIN 
(
    SELECT
        pkid,
        n = COUNT(DISTINCT value)
    FROM @a
    GROUP BY pkid
) AS ACount
ON Matches.pkid = ACount.pkid

INNER JOIN
(
    SELECT
        otherId,
        n = COUNT(DISTINCT value)
    FROM @b
    GROUP BY otherId
) AS BCount
    ON Matches.otherId = BCount.otherId

WHERE Matches.n = ACount.n AND Matches.n = BCount.n

1)我假设你没有重复的ID

2)获取具有相同数量值的密钥

3)键值数量等于值相等数量的行是目标

我希望这就是我们要搜索的内容(我们不是搜索性能，不是吗？)

declare @a table(    pkid int,    value int)
declare @b table(    otherID int,    value int)

insert into @a values (1, 1000)
insert into @a values (1, 1001)
insert into @a values (2, 1000)
insert into @a values (2, 1001)
insert into @a values (2, 1002)
insert into @a values (3, 1000)  
insert into @a values (3, 1001)
insert into @a values (4, 1000)
insert into @a values (4, 1001)
insert into @b values (-1, 1000)
insert into @b values (-1, 1001)
insert into @b values (-1, 1002)
insert into @b values (-2, 1001)
insert into @b values (-2, 1002)
insert into @b values (-3, 1000)
insert into @b values (-3, 1001)

  select cntok.cntid1 as cntid1, cntok.cntid2 as cntid2
  from
 (select cnt.cnt, cnt.cntid1, cnt.cntid2 from
    (select acnt.cnt as cnt, acnt.cntid as cntid1, bcnt.cntid as cntid2 from
          (select count(pkid) as cnt, pkid as cntid from @a group by pkid)
           as acnt
                full join 
               (select count(otherID) as cnt, otherID as cntid from @b group by otherID)
                as bcnt
                   on  acnt.cnt = bcnt.cnt)
     as cnt
     where cntid1 is not null and cntid2 is not null)
   as cntok 
inner join 
(select count(1) as cnt, cnta.cntid1 as cntid1, cnta.cntid2 as cntid2
from
    (select cnt, cntid1, cntid2, a.value as value1 
     from
         (select cnt.cnt, cnt.cntid1, cnt.cntid2 from
            (select acnt.cnt as cnt, acnt.cntid as cntid1, bcnt.cntid as cntid2 from
                  (select count(pkid) as cnt, pkid as cntid from @a group by pkid)
                   as acnt
                        full join 
                       (select count(otherID) as cnt, otherID as cntid from @b group by otherID)
                        as bcnt
                           on  acnt.cnt = bcnt.cnt)
             as cnt
             where cntid1 is not null and cntid2 is not null)
         as cntok 
             inner join @a as a on a.pkid = cntok.cntid1)
      as cnta
         inner join

             (select cnt, cntid1, cntid2, b.value as value2 
             from
             (select cnt.cnt, cnt.cntid1, cnt.cntid2 from
                    (select acnt.cnt as cnt, acnt.cntid as cntid1, bcnt.cntid as cntid2 from
                          (select count(pkid) as cnt, pkid as cntid from @a group by pkid)
                           as acnt
                                full join 
                               (select count(otherID) as cnt, otherID as cntid from @b group by otherID)
                                as bcnt
                                   on  acnt.cnt = bcnt.cnt)
                     as cnt
                     where cntid1 is not null and cntid2 is not null)
                 as cntok 
                     inner join @b as b on b.otherid = cntok.cntid2)
               as cntb
               on cnta.cntid1 = cntb.cntid1 and cnta.cntid2 = cntb.cntid2 and cnta.value1 = cntb.value2
      group by cnta.cntid1, cnta.cntid2) 
   as cntequals
   on cntok.cnt = cntequals.cnt and cntok.cntid1 = cntequals.cntid1 and cntok.cntid2 = cntequals.cntid2

How do I query for all the values in @a that completely match up with @b?

恐怕这个定义还不太清楚。从其他示例看来，我们想要所有对a.pkid，b.otherID，其给定b.otherID的每个b.value也是给定a.pkid的a.value。

换句话说，我们希望@a中的pkid至少具有b中otherID的所有值。 @a中的额外值似乎还可以。同样，这是根据其他示例进行的推理，并假设(1，-2)和(2，-2)将是有效的结果。在这两种情况下，给定pkid的a.value值都大于给定otherID的b.value值。

因此，请记住：

select
    matches.pkid
    ,matches.otherID
from
(
    select 
        a.pkid
        ,b.otherID
        ,count(1) as cnt
    from @a a
    inner join @b b
        on b.value = a.value
    group by 
        a.pkid
        ,b.otherID
) as matches
inner join
(
    select
        otherID
        ,count(1) as cnt
    from @b
    group by otherID
) as b_counts
on b_counts.otherID = matches.otherID
where matches.cnt = b_counts.cnt

这样效率更高(它使用TOP 1而不是COUNT)，并与((-2，1000)`一起使用：

SELECT  *
FROM    (
        SELECT  ab.pkid, ab.otherID,
                (
                SELECT  TOP 1 COALESCE(ai.value, bi.value)
                FROM    (
                        SELECT  *
                        FROM    @a aii
                        WHERE   aii.pkid = ab.pkid
                        ) ai
                FULL OUTER JOIN
                        (
                        SELECT  *
                        FROM    @b bii
                        WHERE   bii.otherID = ab.otherID
                        ) bi
                ON      ai.value = bi.value
                WHERE   ai.pkid IS NULL OR bi.otherID IS NULL
                ) unmatch
        FROM
                (
                SELECT  DISTINCT pkid, otherid
                FROM    @a a , @b b
                ) ab
        ) q
WHERE   unmatch IS NOT NULL