SQL交叉连接中的重复对？

Question

我知道这里有很多类似的问题，我看了其中一些问题，即这，但这还不足以解决我的问题。

我有一个桌子proposals，它看起来像这样：

proposalNum | PI        | department | investigator | investigatorDepartment|
---------------------------------------------------------------------------- 
FP00003521  | Bush,Raj  | GIS        | Amm,Anna     | CIS                   |
FP00003521  | Bush,Raj  | GIS        | Milton,Ross  | CIS                   |
FP00003521  | Bush,Raj  | GIS        | Landis, Amy  | SEB                   |
FP00069606  | Mill, Ash | DIA        | Keller, Bill | FAA                   |

基本上，我想查看investigator字段并创建：

Amm,Anna     | Milton, Ross 
Amm,Anna     | Landis, Amy
Milton,Ross  | Landis, Amy 

对于所有拥有CIS的调查人员，以及给定的proposalNum(在本例中为FP00003521)

(这是SQL小提琴)

我做了这个：

SELECT p1.investigator,p2.investigator
FROM proposals AS p1
CROSS JOIN proposals AS p2 
WHERE p1.investigatorDepartment='CIS' 
AND p1.investigator!=p2.investigator
AND p1.proposalNum=p2.proposalNum

并得到：

Milton,Ross  | Amm,Anna 
Amm,Anna     | Milton,Ross
Amm,Anna     | Landis, Amy
Milton,Ross  | Landis,Amy

但还有一个重复..。

依循有关链接的逻辑，我亦尝试：

SELECT p1.investigator,p2.investigator
FROM proposals AS p1
CROSS JOIN proposals AS p2 
ON p1.investigator < p2.investigator
WHERE p1.investigatorDepartment='CIS' 
AND p1.investigator!=p2.investigator
AND p1.proposalNum=p2.proposalNum

我得到了这个：

Amm,Anna     | Milton,Ross
Amm,Anna     | Landis, Amy

问题是当我添加ON p1.investigator < p2.investigator条件时，它也不会添加Milton,Ross和Landis, Amy，因为M > L

我将如何重写我的查询来解决这个问题？

任何帮助都将不胜感激，谢谢！

Answer 1

你可以加入那些独立于非独联体的p2's，一种方法如下。

select
  p1.investigator,
  p2.investigator
from
  proposals AS p1
    cross join
  proposals AS p2 
where
  p1.investigatorDepartment = 'CIS' and ((
      p2.investigatorDepartment = 'CIS' and 
      p1.investigator < p2.investigator
  ) or (
      p2.investigatorDepartment != 'CIS' or
      p2.investigatorDepartment is null
  )) and
  p1.proposalNum = p2.proposalNum;

SQL Fiddle