SQL 查找相同的组

Question

给定一个表格:

id    key   val
----  ----  -----
bob   hair  red
bob   eyes  green

还有另一个表:

id    key   val
----  ----  -----
fred  hair  red
fred  eyes  green
fred  shoe  42
joe   hair  red 
joe   eyes  green
greg  eyes  blue
greg  hair  brown

我想在表 b 中找到与表 a 中的人完全匹配的人，在本例中是 Bob 和 Joe。弗雷德不算在内，因为他也有鞋号。这是在 Sybase 中，因此没有完整的外部连接。我想出了一个 select of select with a union 返回肯定不相同的人，但我不确定如何有效地选择相同的人。

或者，如果更简单，我如何检查 a 中的哪些组在 b 中出现不止一次？

Answer 1

试试这个

select a.id,b.id
from a 
join b on a.[key] = b.[key] and a.val = b.val -- match all rows
join (select id,count(*) total from a group by id) a2 on a.id = a2.id -- get the total keys for table a per id
join (select id,count(*) total from b group by id) b2 on b.id = b2.id -- get the total keys for table b per id
group by a.id,b.id,a2.total,b2.total
having count(*) = a2.total AND count(*) = b2.total -- the matching row's total should be equal with each tables keys per id

在@t-clausen.dk 评论之后我对原来的sql代码进行了修改。在这种情况下，我计算在两个表上匹配的每个不同的对/值，每个表都有不同的对/值。

select td.aid,td.bid
from (
select a.id as aid,b.id as bid, count(distinct a.[key]+' '+a.val) total
from a 
join b on a.[kry] = b.[key] and a.val = b.val
group by a.id,b.id
) td -- match all distinct attribute rows
join (select id,count(distinct [key]+' '+val) total from a group by id) a2 on td.aid = a2.id -- get the total distinct keys for table a per id
join (select id,count(distinct [key]+' '+val) total from b group by id) b2 on td.bid = b2.id -- get the total keys for table b per id
where td.total = a2.total AND td.total = b2.total -- the matching distinct attribute total should be equal with each tables distinct key-val pair

Tested on

Table a

bob     hair    red
bob     eyes    green
nick    hair    red
nick    eyes    green
nick    shoe    45

Table b

fred    hair    red
fred    eyes    green
joe     hair    red
joe     eyes    green
fred    shoe    42