不包括对称结果的 MySQL 叉积

Question

我想对表本身进行叉积，但我只想要不对称的结果。

例如，假设我有这些主题代码:

CH10001
CS21003
MA10001

条件为“不等于”的叉积将产生 6 行:

CH10001,CS21003
CH10001,MA10001
CS21003,MA10001
CS21003,CH10001
MA10001,CH10001
MA10001,CS21003

但我只希望输出中有 3 行。即:

(CH10001,CS21003) OR (CS21003,CH10001)
(CH10001,MA10001) OR (MA10001,CH10001)
(CS21003,MA10001) OR (MA10001,CS21003)

有人可以帮忙吗？

Answer 1

应该这样做

SELECT DISTINCT 
    case when a.courseID <= b.courseID 
      then a.courseID 
      else b.courseID 
     end id1,
    case when a.courseID <= b.courseID 
      then b.courseID 
      else a.courseID 
    end id2
  from course A cross join course B
HAVING id1 <> id2

返回第一个结果

[CH10001    CS21003]
[CH10001    MA10001]
[CS21003    MA10001]

Here's the SQL Fiddle