假设我们在每个表A
和表B
中有十行
表A单
ColA
1
2
3
4
5
6
7
8
9
10
带列的表
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
两张桌子之间没有关系。两列都是独立的。同样是1,2…19,20,它们是行
B
s,如果只考虑数据,那么它们是无序的。 最佳答案
在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演示
关于mysql - 在sql中的表之间没有关系时如何合并两个表的行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18504292/