Mysql选择互惠的记录对，没有重复

I have a table with two columns:

我有一个有两列的表:

person_id
person_id with whom 1st field id is in cooperation

person_id与第一场id合作

I need to select all cooperation pairs, it is easy but in what is the problem: table have data like: 987 - 102, 103 - 104, 104 - 103, 21 - 102. As the result with such data i should have 3 cooperation pairs 987 - 102, 103-104, 21-102, as you see 103 - 104 and 104 - 103 records have the same logic, how can I avoid duplicating of them. Any idea?

我需要选择所有合作对,这很容易,但问题是什么:表有数据如:987 - 102,103 - 104,104 - 103,21 - 102.由于这样的数据我应该有3个合作对987 - 102,103-104,21-102,如你所见103 - 104和104 - 103记录具有相同的逻辑,我怎样才能避免重复它们。任何想法?

Thanks, and best regards. Anton.

谢谢,还有最好的问候。安东。

2 个解决方案

#1

You could use MySQL's LEAST() and GREATEST() functions, together with DISTINCT:

你可以使用MySQL的LEAST()和GREATEST()函数以及DISTINCT:

SELECT DISTINCT LEAST(a, b), GREATEST(a, b) FROM mytable

#2

If preserving the order of the elements in each "pair" is not important, see the answer from eggyal. That query returns a result set that is slightly different than the one you specified, it returns pair 102-987 instead of 987-102. It also eliminates any "duplicate" pairs that appear in the table.

如果保留每个“对”中元素的顺序并不重要,请参阅eggyal的答案。该查询返回的结果集与您指定的结果集略有不同,它返回对102-987而不是987-102。它还消除了表中出现的任何“重复”对。

If preserving the order of the elements in each pair is important, and you want to return the "smaller - larger" rather than "larger - smaller" when both of those "matching" pairs are present, the you can use something like this:

如果保留每对中元素的顺序很重要,并且想要返回“较小 - 较大”而不是“较大 - 较小”,当存在这两个“匹配”对时,您可以使用如下内容:

SELECT c.col1, c.col2
  FROM mytable c
  LEFT
  JOIN mytable d ON d.col1 = c.col2 AND d.col2 = c.col1 AND d.col1 <> d.col2
 WHERE (d.col1 IS NULL OR d.col1 > c.col1)

To eliminate all duplicate pairs AND "matching" pairs, add a GROUP BY clause or the DISTINCT keyword, e.g.

要消除所有重复对和“匹配”对,请添加GROUP BY子句或DISTINCT关键字,例如

SELECT c.col1, c.col2
  FROM mytable c
  LEFT
  JOIN mytable d ON d.col1 = c.col2 AND d.col2 = c.col1 AND d.col1 <> d.col2
 WHERE (d.col1 IS NULL OR d.col1 > c.col1)
 GROUP BY c.col1, c.col2

NOTES:

SQL Fiddle here: http://sqlfiddle.com/#!2/1d9e7/1 and here: http://sqlfiddle.com/#!2/1d9e7/2

这里的SQL小提琴:http://sqlfiddle.com/#!2 / 9e7 / 1和http://sqlfiddle.com/#!2/1d9e7/2

The comparison operators are not null-safe, they may not return the resultset you want when either col1 or col2 contains a NULL value. (The query could be modified to handle NULL values for col1 and/or col2.) As written, both queries would return, for example, both (1,NULL) and (NULL,1) if those "matching" "pairs" are in the table. (It boils down to the question of whether you want to consider NULL values to match or not.)

比较运算符不是空安全的,当col1或col2包含NULL值时,它们可能不会返回所需的结果集。 (可以修改查询以处理col1和/或col2的NULL值。)如上所述,如果那些“匹配”“对”是,则两个查询都将返回(例如,(1,NULL)和(NULL,1))在表中。 (归结为是否要考虑NULL值是否匹配的问题。)

Also note, both queries will return rows where col1=col2.

另请注意,两个查询都将返回col1 = col2的行。

Note the first query does NOT eliminate "duplicate" rows that exist in the table. That is, if a duplicate "pair" e.g (202,101) appears in two different rows, then both will be returned (unless the query returns at least one row with a "matching" pair: (101,202).)

请注意,第一个查询不会消除表中存在的“重复”行。也就是说,如果重复的“对”例如(202,101)出现在两个不同的行中,则两者都将被返回(除非查询返回至少一行具有“匹配”对的行:(101,202)。)

It wasn't clear what result set you wanted returned in those cases, so the first query shows the pattern for eliminating ONLY rows (larger,smaller) when a matching (smaller,larger) pair is in the resultset.

在这些情况下,您想要返回的结果集尚不清楚,因此第一个查询显示当匹配(较小,较大)对位于结果集中时,仅消除行(较大,较小)的模式。

The second query eliminates ALL duplicates and "matching" pairs.

第二个查询消除了所有重复和“匹配”对。

#1