mysql - SQL交叉连接中的重复对？

Question

我知道 SO 上有很多与此类似的问题，我查看了其中的一些问题，即 this ，但这还不足以解决我的问题

我有一张 table 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 FIDDLE )

我这样做了:

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

您可以以不同于 CIS 的方式加入那些 p2。一种方法如下。

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