我想取一个表的叉积,但我只想要不对称的结果。
例如,假设我有这些主题代码:
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)
有人能帮忙吗?
最佳答案
这样就可以了
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
关于mysql - MySQL跨产品(不包括对称结果),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/35098455/