Question

I need alittle help with a mysql query. I have 3 tables

我有2人加入。

select distinct

x1.int1 as a,
x2.int1 as b,
x3.int1 as c

from 
x1
JOIN
x2
JOIN 
x3

but I would like to generate the cartesian product with the condition that the results should contain just the just the 3 numbers from x1 (1,2,2) in all orders and I don t know what condition to put in the query

it s a permutation simulation of three elements(1,2,2) result should be 1,2,2 2,1,2 2,2,1

增编

Answer 1

你们是否想要?

SELECT DISTINCT * FROM x1 A,x1 B,x1 C

Answer 2

鉴于每次变动总是由1 222人组成,因此,有几种办法可以取得你之后的结果。

简言之,是建立一个包含图形的表格:

create table perm ( `int1` int, `int2` int, `int3` int );
insert into perm values (1,2,2), (2,1,2), (2,2,1);

另一种做法是让你加入现有的行列,并限制对一系列有效变动的答复:

select distinct    
  x1.int1 as a,
  x2.int1 as b,
  x3.int1 as c
from x1
JOIN x2
JOIN x3
WHERE (a=1 and b=2 and c=2)
   OR (a=2 and b=1 and c=2)
   OR (a=2 and b=2 and c=1);

另一种做法是,在加入时增加变动表:

select distinct
  x1.int1 as a,
  x2.int1 as b,
  x3.int1 as c
from x1
JOIN x2
JOIN x3
JOIN perm p on p.`int1` = a and p.`int2` = b and p.`int3` = c

另一种做法是比表x1再增加两倍,确保x1的每行各出现一次:

select distinct
  c1.int1 as a,
  x2.int1 as b,
  x3.int1 as c
from x1 as c1
JOIN x2
JOIN x3
JOIN x1 as c2 on c2.`int1` = b and c2.`int1` != c1.`int1` and c2.`int1` != c3.`int1` 
JOIN x1 as c3 on c3.`int1` = c and c3.`int1` != c1.`int1` and c3.`int1` != c2.`int1`

......但鉴于价值2的数值是x1的两倍,这不会奏效。将需要一些独一无二的每行价值,以区分一个包含2的行文。

变动表最容易。

Answer 3

第二次尝试——在澄清问题之后。

create table p ( bit int not null, v int not null );
insert into p values (1,1), (2,2), (4,2);

select distinct p1.v, p2.v, p3.v 
from p as p1 join p as p2 join p as p3 
where p1.bit + p2.bit + p3.bit = 7;

Column诉持有你想要 per的价值观。 1,2,2。

重要部分是,对每一行必须赋予比额独一无二的价值,而一套数值必须如此,只有每行一次出现时才能得出总和。

达到这一目的的一套最简单的数值是2^0, 2^1, 2^31。仅限32英亩。表3行,数额为1+2+4=7。

结果是:

+---+---+---+
| v | v | v |
+---+---+---+
| 2 | 2 | 1 |
| 2 | 1 | 2 |
| 1 | 2 | 2 |
+---+---+---+

如果增加更多的浏览量,就必须在问询中增加更多的加入,并重新计算出借栏的总和。

友情链接