SQL:仅使用每行一次自我联接 [英] SQL: self join using each rows only once

问题描述

可能重复:
来自sql交叉连接的组合(不是排列)

Possible Duplicate:
combinations (not permutations) from cross join in sql

我目前有一张包含以下记录的表:

I've currently got a table with the following records:

A1
A2
A3
B1
B2
C1
C2

同一字母表示某些共同的条件(例如，字母"列的共同值).我按照以下标准进行自我加入:

Where the same letter denotes some criteria in common (e.g. a common value for the column 'letter'). I do a self join on the criteria as follows:

SELECT mytable.*, self.* FROM mytable INNER JOIN mytable AS self 
   ON (mytable.letter = self.letter and mytable.number != self.number);

此联接提供如下内容:

A1 A2
A2 A1
A1 A3
A3 A1
A2 A3
A3 A2
B1 B2
B2 B1
C1 C2
C2 C1

但是，我只想将每对包含一次(组合而不是排列). 我将如何获得以下信息:

However, I only want to include each pair once (a combination instead of a permutation). How would I get the following:

A1 A2
A1 A3
A2 A3
B1 B2
C1 C2

推荐答案

稍微更改JOIN条件将实现您想要的.

Changing the JOIN condition slightly will achieve what you want..

代替:

ON (mytable.letter = self.letter and mytable.number != self.number)

使用

ON (mytable.letter = self.letter and mytable.number > self.number)

这仅包括self.number大于mytable.number的组合，这实际上将结果限制为每种组合的一种有效顺序...

This will only include combinations where self.number is greater than mytable.number which in effect restricts the results to one valid ordering of each combination...

这篇关于SQL:仅使用每行一次自我联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！