T-SQL：如何从一个表中获取其值与另一个表中的值完全匹配的行？

Given the following:

鉴于以下内容:

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)

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

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

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

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

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

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

Refactoring tables can be an option.

重构表可以是一种选择。

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

编辑:我在James和Tom H.的答案中取得了成功。

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

当我在@b中添加另一个案例时,它们会有点短暂。

insert into @b values (-2, 1000)

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.

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

12 个解决方案

#1

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

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.

你可以看到,基本上我正在为每个表创建一个新的结果集,表示每个表中每个Id的值集合的一个值,并且只在它们匹配的地方加入。

#2

This is more efficient (it uses TOP 1 instead of COUNT), and works with (-2, 1000):

这更有效(它使用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

#3

The following query gives you the requested results:

以下查询为您提供了请求的结果:

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)

#4

Works for your example, and I think it will work for all cases, but I haven't tested it thoroughly:

适用于您的示例,我认为它适用于所有情况,但我没有彻底测试它:

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

#5

-- 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

#6

I've added a few extra test cases. You can change your duplicate handling by changing the way you use distinct keywords in your aggregates. Basically, I'm getting a count of matches and comparing it to a count of required matches in each @a and @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

#7

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

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

I'm afraid this definition is not quite perfectly clear. It seems from your additional example that you want all pairs of a.pkid, b.otherID for which every b.value for the given b.otherID is also an a.value for the given a.pkid.

我担心这个定义不太清楚。从您的附加示例中可以看出,您希望所有a.pkid,b.otherID对,给定b.otherID的每个b.value也是给定a.pkid的a.value。

In other words, you want the pkids in @a that have at least all the values for otherIDs in b. Extra values in @a appear to be okay. Again, this is reasoning based on your additional example, and the assumption that (1, -2) and (2, -2) would be valid results. In both of those cases, the a.value values for the given pkid are more than the b.value values for the given otherID.

换句话说,您希望@a中的pkids至少包含b中otherID的所有值。 @a中的额外值似乎没问题。同样,这是基于您的附加示例的推理,并假设(1,-2)和(2,-2)将是有效结果。在这两种情况下,给定pkid的a.value值都大于给定otherID的b.value值。

So, with that in mind:

所以,考虑到这一点:

    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

#8

To iterate the point further:

进一步迭代这一点:

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

This will return all the values in @a that match @b

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

#9

If you are trying to return only complete sets of records, you could try this. I would definitely recommend using meaningful aliases, though ...

如果您尝试仅返回完整的记录集,则可以尝试此操作。我肯定会建议使用有意义的别名,但......

Cervo is right, we need an additional check to ensure that a is an exact match of b and not a superset of b. This is more of an unwieldy solution at this point, so this would only be reasonable in contexts where analytical functions in the other solutions do not work.

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
    )

#10

1) i assume that you don't have duplicate id

1)我假设你没有重复的身份证

2) get the key with the same number of value

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

3) the row with the number of key value equal to the number of equal value is the target

3)具有等于等数值的键值的行是目标

I hope it's what you searched for (you don't search performance don't you ?)

我希望这是你搜索的内容(你不搜索性能不是吗?)

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

#11

Several ways of doing this, but a simple one is to create a union view as

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

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

be careful to use union all, not a simple union as that will omit the duplicates

小心使用union all,而不是简单的union,因为它会省略重复

then do this

然后这样做

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

the Union and Having statements tend to be the most overlooked part of standard SQL, but they can solve a lot of tricky issues that otherwise require procedural code

Union和Having语句往往是标准SQL中最容易被忽视的部分,但它们可以解决许多棘手的问题,否则需要程序代码

#12

As CQ says, a simple inner join is all you need.

正如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