T-SQL:如何从一个表中获取其值与另一个表中的值完全匹配的行? [英] T-SQL: How do I get the rows from one table whose values completely match up with values in another table?

问题描述

鉴于以下内容:

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 完全匹配的所有值?

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

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

{@a.pkid = 1, @b.otherID = -1} would not be returned (only 2 of 3 values match)

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

{@a.pkid = 2, @b.otherID = -1} would be returned (3 of 3 values match)

重构表是一种选择.

我从 James 和 Tom H 的回答中获得了成功.

I've had success with the answers from James and Tom H.

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

When I add another case in @b, they fall a little short.

insert into @b values (-2, 1000)

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

Assuming this should return two additional rows ({@a.pkid = 1, @b.otherID = -2} and {@a.pkid = 2, @b.otherID = -2}, it doesn't work. However, for my project this is not an issue.

推荐答案

可能不是最便宜的方法:

Probably not the cheapest way to do it:

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 值集的一个值，并仅在匹配的地方加入.

You can see, basically I'm creating a new result set for each representing one value for each Id's set of values in each table and joining only where they match.

这篇关于T-SQL:如何从一个表中获取其值与另一个表中的值完全匹配的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！