如何在具有传递性的MySQL连接(同表)中选择不同的对?

I'm facing a very poorly designed database with a non-normalized table X. This table X should have a N:M relationship with another table Y.

我面对的是一个设计很糟糕的数据库，它有一个非规范化的表X。这个表X应该与另一个表Y有N:M的关系。

The problem is that this relationship is currently 1:N and the jerry-rigged solution until now was to duplicate the entries when there was various registries to be related.

问题是，这种关系目前是1:N，而到目前为止，jerry被操纵的解决方案是在有各种要关联的注册中心时复制条目。

Simplifying, I have this:

简化,我有这个:

| ID | TEXT | LOCATION_ID |
| 1  | foo  |      1      |
| 2  | foo  |      2      |
| 3  | bar  |      1      |
| 4  | bar  |      4      |
| 5  | bar  |      3      |

I have to normalize this table. So, my first idea was try to obtain pairs of similar registries. Something like this:

我得把这张表规范化。所以，我的第一个想法是尝试获得一对相似的注册表。是这样的:

| a.ID | b.ID | 
|   1  |  2   |
|   3  |  4   |
|   3  |  5   |

Experimenting a little bit:

尝试一下:

SELECT a.id, b.id 
FROM mytable AS a 
INNER JOIN mytable AS b 
   ON a.text = b.text AND a.id != b.id 
GROUP BY a.id, b.id

This lead to a problem like this:

这导致了这样一个问题:

| a.ID | b.ID | 
|   1  |  2   |
|   2  |  1   |
|   3  |  4   |
|   3  |  5   |
|   4  |  3   |
|   4  |  5   |
|   5  |  3   |
|   5  |  4   |

The pairs were duplicated.

对重复。

After some digging, I realized that this was more efficient:

经过一番挖掘之后，我意识到这样做更有效:

SELECT a.id, b.id 
FROM mytable AS a 
INNER JOIN mytable AS b 
        ON a.text = b.text AND a.id < b.id 
GROUP BY a.id, b.id

So, I got this:

所以,我得到了这个:

| a.ID | b.ID | 
|   1  |  2   |
|   3  |  4   |
|   3  |  5   |
|   4  |  5   |

But I still need to get rid of that last register.

但我仍然需要去掉最后一个寄存器。

1 个解决方案

#1

Group on only one side and take the MIN() of the other:

组只在一边，取另一边的MIN():

SELECT   MIN(a.ID) a, b.ID b
FROM     mytable a JOIN mytable b ON b.text = a.text AND b.ID > a.ID
GROUP BY b.ID

See it on sqlfiddle.

sqlfiddle上看到它。

#1