sql-server - T-SQL : How do I get the rows from one table whose values completely match up with values in another table?

Question

鉴于以下情况:

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 个匹配)

重构表是一种选择。

编辑:我已经从 James 和 Tom H 的答案中获得了成功。

当我在 @b 中添加另一个案例时，它们有点不足。

insert into @b values (-2, 1000)

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

Answer 1

这样效率更高(它使用 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