mysql - sql中表之间没有关系时如何合并两个表的行

Question

假设我们在每个表 A 和表 B 中有十行表A与单

ColA 
  1 
  2 
  3 
  4 
  5 
  6 
  7 
  8 
  9 
 10

和带有列的表B

ColB 
  11 
  12 
  13 
  14 
  15 
  16 
  17 
  18 
  19 
  20

需要的输出:

SingleColumn
   1 
  11 
   2 
  12 
   3 
  13 
   4 
  14 
   5 
  15 
   6 
  16 
   7 
  17 
   8 
  18 
   9 
  19 
  10 
  20

P.S:两个表之间没有关系。两列都是独立的。还有 1, 2...19, 20 ，它们是行 id 并且如果只考虑数据则以无序形式。

Answer 1

已更新 在 SQL Server 和 Oracle 中，您可以这样做

SELECT col
  FROM
(
  SELECT a.*
    FROM
  ( 
    SELECT cola col, 1 source, ROW_NUMBER() OVER (ORDER BY cola) rnum
      FROM tablea
  ) a 
  UNION ALL 
  SELECT b.*
    FROM
  (
    SELECT colb col, 2 source, ROW_NUMBER() OVER (ORDER BY colb) rnum
      FROM tableb
  ) b
) c
 ORDER BY rnum, source 

输出:

| COL ||-----||   1 ||  11 ||   2 ||  12 ||   3 ||  13 ||   4 ||  14 ||   5 ||  15 ||   6 ||  16 ||   7 ||  17 ||   8 ||  18 ||   9 ||  19 ||  10 ||  20 |

Here is SQLFiddle demo (SQL Server)
Here is SQLFiddle demo (Oracle)

In MySql you can do

SELECT col
  FROM
( 
  (
    SELECT cola col, 1 source, @n := @n + 1 rnum
      FROM tablea CROSS JOIN (SELECT @n := 0) i
     ORDER BY cola
   )
  UNION ALL
  (
    SELECT colb col, 2 source, @m := @m + 1 rnum
      FROM tableb CROSS JOIN (SELECT @m := 0) i
     ORDER BY colb
   )
) c
 ORDER BY rnum, source

这是 SQLFiddle 演示