英文:
Putting one SQL table on top of another using oracle SQL
问题
这可能是一个非常简单的问题，但我找不到解决方法。我有两个具有相同列名的表，我希望将它们叠加在一起。我尝试过使用UNION，但似乎不起作用，我得到了错误'ORA-01790: 表达式必须与相应表达式具有相同的数据类型'
我正在使用Oracle SQL Developer访问数据。
table1 =
| column1 | column2 | column3 |
|---|---|---|
| 1111111 | 2222222 | 3333333 |
| aaaaaaa | bbbbbbb | ccccccc |
table2 =
| column1 | column2 | column3 |
|---|---|---|
| 9999999 | 8888888 | 7777777 |
| zzzzzzz | yyyyyyy | xxxxxxx |
期望的输出
| column1 | column2 | column3 |
|---|---|---|
| 1111111 | 2222222 | 3333333 |
| aaaaaaa | bbbbbbb | ccccccc |
| 9999999 | 8888888 | 7777777 |
| zzzzzzz | yyyyyyy | xxxxxxx |
我尝试了以下脚本来实现它 - 任何帮助都将不胜感激。
select * from table1
union
select * from table2
英文:
This is probably a very simple question but I cannot find the solution. I have two tables with identical column names and I wish to put one on top of the other. I have tried UNION but this appears not to work and I get the error 'ORA-01790: expression must have same datatype as corresponding expression'
I am using Oracle SQL Developer to access the data.
table1 =
| column1 | column2 | column3 |
|---|---|---|
| 1111111 | 2222222 | 3333333 |
| aaaaaaa | bbbbbbb | ccccccc |
table2 =
| column1 | column2 | column3 |
|---|---|---|
| 9999999 | 8888888 | 7777777 |
| zzzzzzz | yyyyyyy | xxxxxxx |
desired output
| column1 | column2 | column3 |
|---|---|---|
| 1111111 | 2222222 | 3333333 |
| aaaaaaa | bbbbbbb | ccccccc |
| 9999999 | 8888888 | 7777777 |
| zzzzzzz | yyyyyyy | xxxxxxx |
I have tried the following script to get it - any assistance would be appreciated.
select * from table1
union
select * from table2
答案1
得分: 1
数据类型问题可能通过在选择子句中明确列出所有列来解决。此外，您应该引入一个计算列，以维护联合两半部分在输出中的顺序。
SELECT column1, column2, column3
FROM
(
SELECT column1, column2, column3, 1 AS src
FROM table1
UNION ALL
SELECT column1, column2, column3, 2
FROM table2
) t
ORDER BY src;
英文:
The data type problem might be fixed by explicitly listing out all columns in the select clause. In addition, you should introduce a computed column which maintains the order of the two halves of the union in the output.
<!-- language: sql -->
SELECT column1, column2, column3
FROM
(
SELECT column1, column2, column3, 1 AS src
FROM table1
UNION ALL
SELECT column1, column2, column3, 2
FROM table2
) t
ORDER BY src;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论