mysql交叉连接，但没有重复的对? [英] mysql cross join, but without duplicated pair?

问题描述

假设我的表格中有以下行

Let's say I have the following row in my table

表行

id
63
64
65
66
67
68

如果我运行以下查询，则会得到30行.

if I run the following query, I get 30 rows.

SELECT r1.id, r2,id 
  FROM rows AS r1 
    CROSS JOIN rows AS r2 
  WHERE r1.id!=r2.id

结果:

63  64
65  64
66  64
67  64
68  64
64  63
65  63
66  63
67  63
68  63
63  65
64  65
66  65
67  65
68  65
63  66
64  66
65  66
67  66
68  66
63  67
64  67
65  67
66  67
68  67
63  68
64  68
65  68
66  68
67  68

我如何获得以下结果而不是上面的结果?

how would I get the following result instead of the above?

63,64  
63,65   
63,66
63,67
63,68

64,65
64,66
64,67
64,68

65,66
65,67
65,68

66,67
66,68

67,68

例如，我不想同时获得63,64和64,63.

as you see, I don't want to get both 63,64 and 64,63, for example.

推荐答案

简单，仅以大于当前值的值连接.

Simple, only join with values higher than the current one.

select r1.id, r2,id 
from rows r1 
cross join rows r2 
where r1.id < r2.id

这篇关于mysql交叉连接，但没有重复的对?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！