中的代码
我有一组值,我正在通过PHP使用mysql_query将它们INSERT
放入一对列中,我在编写查询时遇到了问题。我希望实现与以下结果类似的结果,避免重复:
Column 1 || Column 2
John || John
John || Richard
John || Kim
Richard || Richard
Richard || John
Richard || Kim
Kim || Kim
Kim || John
Kim || Richard
注意:上面的例子只是我希望插入数据后列的外观的一个例子。我会在INSERT
查询中声明"John,Richard and Kim"为VALUES
,我只是不确定如何实现这一点。
我还没有接近成功,但如果我离成功更近了,我会更新(我的查询)。
提前感谢各位,Rich。
您需要一个交叉连接
select a.value as Column1, b.value as Column2
from yourTable a
cross join YourTable b
如果你把这个插入到另一个表中,你会做这样的事情:-
insert into NewTable (col1, col2)
select a.value as Column1, b.value as Column2
from yourTable a
cross join YourTable b
SELECT
a.a + CAST( b AS VARCHAR(2)) AS c
INTO Tablec
FROM tablea a
CROSS JOIN tableb AS b;
SELECT
CAST( b.b AS VARCHAR(2)) + a AS d
INTO Tabled
FROM tableb b
CROSS JOIN tablea AS a
;
SELECT c FROM tablec Union SELECT d FROM tabled
使用下面的sqlfiddleLink