mysql 数据表多重计算_MySQL查询：单表多重比较

这是一个找到整个表的匹配对的解决方案 - 您可以根据需要添加where子句进行过滤。基本上它基于相等的“成员”和不相等的“id”进行自我加入。然后，它比较由2个ID分组的结果计数，并将它们与原始表中的那些ID的总计数进行比较。如果它们都匹配，则意味着它们具有相同的确切成员。

select

t1.id, t2.id

from

table t1

inner join table t2

on t1.member = t2.member

and t1.id < t2.id

inner join (select id, count(1) as cnt from table group by id) c1

on t1.id = c1.id

inner join (select id, count(1) as cnt from table group by id) c2

on t2.id = c2.id

group by

t1.id, t2.id, c1.cnt, c2.cnt

having

count(1) = c1.cnt

and count(1) = c2.cnt

order by

t1.id, t2.id这是我使用的一些样本数据，返回(1,3)和(6,7)的匹配

insert into table

values

(1, 'abc'), (1, 'pqr'), (2, 'xyz'), (3, 'pqr'), (3, 'abc'), (4, 'abc'), (5, 'pqr'),

(6, 'abc'), (6, 'def'), (6, 'ghi'), (7, 'abc'), (7, 'def'), (7, 'ghi')