sql - 按 A 列分组但比较 B 列

Question

这个问题在过去的几个小时里一直困扰着我，在这个阶段我想我需要一些帮助......

我需要比较单个表中的多个组，并确定 B 列中列出的项目匹配的位置。例如:-

Col A...............Col B
John................Apple
John................Orange
John................Banana
Mary................Orange
Mary................Strawberry
David...............Apple
David...............Orange
David...............Banana

我希望返回“John”和“David”，因为他们在 B 列中的项目匹配。希望这是有道理的!提前致谢!G

Answer 1

这是SQL Fiddle获取此解决方案，以便您可以自己使用它。

 select A.ColA Person1, B.ColA Person2
    from (select ColA, count(ColB) CountBs
          from tbl
          group by ColA) G1
    join (select ColA, count(ColB) CountBs
          from tbl
          group by ColA) G2 on G1.ColA < G2.ColA
                           and G1.CountBs = G2.CountBs
    join tbl A on A.ColA = G1.ColA
    join tbl B on B.ColA = G2.ColA and A.ColB = B.ColB
group by A.ColA, B.ColA, G1.CountBs
having count(distinct A.ColB) = G1.CountBs

-- subqueries G1 and G2 are the same and count the expected colB's per colA
-- G1 and G2 are joined together to get the candidate matches
--    of ColA with the same number of ColB's
-- we then use G1 and G2 to join into tbl, and further join
--    between A and B where the ColB's match
-- finally, we count the matches between A and B and make sure the counts match
--    the expected count of B's for the pairing