两个列的SQL唯一组合

Hi I'm stumped as to this problem as I need a unique combination of two instances of the same column.

你好，我被这个问题难住了，因为我需要同一列的两个实例的唯一组合。

Let's say I have a table Animal with a column Name. I want all "unique" combinations of the animal name and not a repeat of it self or another already existing row.

假设有一个具有列名的表动物。我想要所有动物名字的“唯一”组合，而不是重复它本身或其他已经存在的行。

Sample data:

样本数据:

Name
-------
Mouse
Cat
Dog

The result I want is:

我想要的结果是:

Name1   Name2
-----   -----
Mouse   Cat
Mouse   Dog
Cat     Dog

I wrote the following query:

我写了以下查询:

SELECT DISTINCT A1.name AS Name1, A2.name as Name2
FROM Animal A1, Animal A2
WHERE A1.name <> A2.name;

Which netted me the following results instead:

这让我得到了以下结果:

Name1  Name2
-----  -----
Mouse  Cat
Mouse  Dog
Cat    Mouse
Cat    Dog
Dog    Mouse
Dog    Cat

I hope I'm making sense. I don't want a duplicate of both combination, whether it appears on the left or on the right. As far as "Distinct" is concern the rows are distinct. So is there some way to eliminate the double up?

我希望我讲得通。我不想要两个组合的重复，无论它出现在左边还是右边。就“不同”而言，行是不同的。那么有没有什么办法消除这种双重效应呢?

1 个解决方案

#1

To find unique combinations, just change <> to <:

要找到唯一的组合，只需将<>改为<:

WHERE A1.name < A2.name;
             ^^^