MYSQL: 自连接时避免重复记录的笛卡尔积

Question

有两个表：表A和表B。它们具有相同的列，数据实际上是相同的。它们都有自增ID，唯一的区别在于它们对于相同记录具有不同的ID。

在这些列中，有一个标识符列不是唯一的，即在两个表中存在（非常少量的）具有相同标识符的记录。

现在，为了找到表A和表B之间ID的对应关系，我必须在标识符列上将这两个表连接起来（在所有目的上它是一个自连接），类似于：

SELECT A.ID, B.ID
FROM A INNER JOIN B ON A.IDENTIFIER = B.IDENTIFIER

然而，由于标识符不唯一，这会生成标识符重复值的所有可能组合，我不想要那样。

理想情况下，我希望能够基于它们的顺序，在具有重复标识符值的ID之间建立一对一的关联。例如，假设在表A（因此也在表B中）中有六个记录具有不同的ID和相同的标识符值：

A                                 B
IDENTIFIER:'ident105', ID:10  ->  IDENTIFIER:'ident105', ID:3
IDENTIFIER:'ident105', ID:20  ->  IDENTIFIER:'ident105', ID:400
IDENTIFIER:'ident105', ID:23  ->  IDENTIFIER:'ident105', ID:420
IDENTIFIER:'ident105', ID:100 ->  IDENTIFIER:'ident105', ID:512
IDENTIFIER:'ident105', ID:120 ->  IDENTIFIER:'ident105', ID:513
IDENTIFIER:'ident105', ID:300 ->  IDENTIFIER:'ident105', ID:798

那将是理想的。

无论ID的顺序如何，生成一对一关联的方法仍然可以（但不是首选）。

感谢您的时间，

Silvio

- Silvio Donnini

---

+1 因为你让我感到困惑了... - ircmaxell

Answer 1

select a_numbered.id, a_numbered.identifier, b_numbered.id from 
(
select a.*,
       case 
          when @identifier = a.identifier then @rownum := @rownum + 1
          else @rownum := 1
       end as rn,
       @identifier := a.identifier
  from a
  join (select @rownum := 0, @identifier := null) r
order by a.identifier

) a_numbered join (
select b.*,
       case 
          when @identifier = b.identifier then @rownum := @rownum + 1
          else @rownum := 1
       end as rn,
       @identifier := b.identifier
  from b
  join (select @rownum := 0, @identifier := null) r
order by b.identifier

) b_numbered 
on a_numbered.rn=b_numbered.rn and a_numbered.identifier=b_numbered.identifier

- Martin Smith