我有两个不相关的表,我需要按照下面的预期输出加入一行。我尝试了以下查询,但不起作用。如何连接这些表??
表1
| col1 | amount|
| a | 200 |
| b | 100 |
| c | 300 |
| d | 500 |
表2
| col2 | amount|
| e | 900 |
| f | 800 |
预期输出:
| col1 | Amount | col2 | Amount
| a | 200 | e | 900 |
| b | 100 | f | 800 |
| c | 300 |
| d | 500 |
我已经尝试过这个查询
set @a =0; set @b=0;
SELECT
(@a:=@a + 1) AS table1_serial_no,
(@b:=@b + 1) AS table2_serial_no,
table1.col1,
table1.Amount,
table2.col2,
table2.Amount
FROM
table1 left outer
JOIN
table2 ON table1_serial_no = table2_serial_no;
select * from
(
select @rn:=@rn + 1 rn from t cross join (select @rn:=0) r
union
select @rn:=@rn + 1 from t1
) allrows
left join
(select col1,amount, @rn1:=@rn1 + 1 rn from t cross join (select @rn1:=0) r) t on t.rn = allrows.rn
left join
(select col2,amount, @rn2:=@rn2 + 1 rn from t1 cross join (select @rn2:=0) r) t1 on t1.rn = allrows.rn
where col1 is not null or col2 is not null;
其中所有行子查询计算出将行号分配给最大可能行数,该行数随后用于联接到分配给表1和表2的行号。注:没有可排序的依据,因此不会对结果进行排序。。