sql将两个不相关的表合并为一个

I have tables

我有桌子

table1

表格1

col1    col2    
a       b
c       d

and table2

和table2

mycol1  mycol2
e           f
g           h
i           j
k           l

I want to combine the two tables, which have no common field into one table looking like:

我想将两个没有共同字段的表组合成一个表，如下所示：

table 3

表3

col1    col2    mycol1  mycol2
a           b   e   f
c           d   g   h
null    null    i   j
null    null    k   l

ie, it is like putting the two tables side by side.

也就是说，就像把两张桌子并排放在一起。

I'm stuck! Please help!

我被卡住了！请帮忙！

3 个解决方案

#1

Get a row number for each row in each table, then do a full join using those row numbers:

获取每个表中每行的行号，然后使用这些行号进行完全连接：

WITH CTE1 AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY col1) AS ROWNUM, * FROM Table1
),
CTE2 AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY mycol1) AS ROWNUM, * FROM Table2
)
SELECT col1, col2, mycol1, mycol2
FROM CTE1 FULL JOIN CTE2 ON CTE1.ROWNUM = CTE2.ROWNUM

This is assuming SQL Server >= 2005.

这假设SQL Server> = 2005。

#2

Option 1: Single Query

选项1：单个查询

You have to join the two tables, and if you want each row in table1 to match to only one row in table2, you have to restrict the join somehow. Calculate row numbers in each table and join on that column. Row numbers are database-specific; here is a solution for mysql:

您必须连接这两个表，如果您希望table1中的每一行只匹配table2中的一行，则必须以某种方式限制连接。计算每个表中的行号并加入该列。行号是特定于数据库的;这是mysql的解决方案：

SELECT
    t1.col1, t1.col2, t2.mycol1, t2.mycol2
FROM
    (SELECT col1, col2, @t1_row := t1_row + 1 AS rownum FROM table1, (SELECT @t1_row := 0) AS r1) AS t1
    LEFT JOIN
    (SELECT mycol1, mycol2, @t2_row := t2_row + 1 AS rownum FROM table2, (SELECT @t2_row := 0) AS r2) AS t2
    ON t1.rownum = t2.rownum;

This assumes table1 is longer than table2; if table2 is longer, either use RIGHT JOIN or switch the order of the t1 and t2 sub-selects. Also note that you can specify the order of each table separately using an ORDER BY clause in the sub-selects.

这假定table1比table2长;如果table2更长，则使用RIGHT JOIN或切换t1和t2子选择的顺序。另请注意，您可以使用子选择中的ORDER BY子句分别指定每个表的顺序。

(See select increment counter in mysql)

（参见mysql中选择增量计数器）

Option 2: Post-processing

选项2：后处理

Consider making two selects, and then concatenating the results with your favorite scripting language. This is a much more reasonable approach.

考虑进行两次选择，然后将结果与您喜欢的脚本语言连接起来。这是一种更合理的方法。

#3

It's really good if you put in a description of why this problem needs to be solved. I'm guessing it is just to practice sql syntax?

如果你描述为什么需要解决这个问题，这真的很好。我猜它只是练习sql语法？

Anyway, since the rows don't have anything connecting them, we have to create a connection. I chose the ordering of their values. Also since they have nothing connecting them that also begs the question on why you would want to put them next to each other in the first place.

无论如何，由于行没有连接它们的任何东西，我们必须创建一个连接。我选择了他们的价值观的顺序。此外，因为他们没有任何连接他们，这也引出了一个问题，为什么你想要首先将它们放在一起。

Here is the complete solution: http://sqlfiddle.com/#!6/67e4c/1

这是完整的解决方案：http：//sqlfiddle.com/#！6/67e4c/1

The select code looks like this:

选择代码如下所示：

WITH rankedt1 AS
(
  SELECT col1
  ,col2
  ,row_number() OVER (order by col1,col2) AS rn1
  FROM table1
  )
,rankedt2 AS 
(
  SELECT mycol1
  ,mycol2
  ,row_number() OVER (order by mycol1,mycol2) AS rn2
  FROM table2
  )

SELECT
col1,col2,mycol1,mycol2
FROM rankedt1
FULL OUTER JOIN rankedt2
  ON rn1=rn2

#1