mysql并排查询结果
问题描述:
简单的问题,我不能得到我的头。 我只想将两个没有共同数据的表并列在一起。mysql并排查询结果
table1 table2 -> result
a b e f a b e f
c d g h c d g h
i j \n \n i j
答
你可以做到这一点使用RIGHT JOIN
并分配一个ID,每个表的使用ROW_NUMBER
来。看到我的查询如下:
SELECT A.column1,A.column2,B.column1,B.column2 FROM
(SELECT
@row_number1:[email protected]_number1+1 AS RowNumber1,
column1,
column2
FROM Table1, (SELECT @row_number1:=0)AS x ORDER BY column1) AS A
RIGHT JOIN
(SELECT
@row_number2:[email protected]_number2+1 AS RowNumber2,
column1,
column2
FROM Table2, (SELECT @row_number2:=0)AS y ORDER BY column1) AS B
ON A.RowNumber1=B.RowNumber2
答
SQL表本质上是无序的,所以排序行的关键是可取的。您可以使用变量创建一个。
您的表格具有不同的行数。这意味着full outer join
是可取的,但这在MySQL中不可用。所以,你可以做你想做采用聚集和union all
什么:
select max(col1) as col1, max(col2) as col2, max(col3) as col3, max(col4) as col4
from ((select col1, col2, null as col3, null as col4, @rn1 := @rn1 + 1 as rn
from table1 cross join (select @rn1 := 0) vars
) union all
(select null, null, col3, col4, @rn2 := @rn2 + 1 as rn
from table2 cross join (select @rn2 := 0) vars
)
) tt
group by rn
谢谢这工作得很好!我不得不将内部连接更改为正确连接,因为它们有不同的长度,但是那样起作用。比我想象的要复杂一点。 – ash 2015-02-24 00:31:05