SQL 如何将两个表中的列合并为一个输出？

How can I merge the columns from two tables into one output?

提问by burger

I have two tables with similar information. Let's call them items_aand items_b. They should be one, but they are coming from different sources, so they aren't. When I full-join the two table, some rows end up with data from either one or both tables. One of the columns in both tables is category_id. I would like to cross the combined table with the categories table using category_id. However, I have two category_idcolumns now (one from items_aand one from items_b). Is there a way to merge the two columns into one?

我有两个具有相似信息的表。让我们称他们为items_a和items_b。它们应该是一个，但它们来自不同的来源，所以它们不是。当我完全连接这两个表时，某些行最终会包含一个或两个表中的数据。两个表中的列之一是category_id。我想使用category_id. 但是，我category_id现在有两列（一列来自items_a一列来自items_b）。有没有办法将两列合并为一列？

I hope this isn't too confusing of a question.

我希望这不是一个太令人困惑的问题。

回答by beny23

Specifying the columns on your query should do the trick:

指定查询中的列应该可以解决问题：

select a.col1, b.col2, a.col3, b.col4, a.category_id 
from items_a a, items_b b 
where a.category_id = b.category_id

should do the trick with regards to picking the columns you want.

应该可以解决选择所需列的问题。

To get around the fact that some data is only in items_a and some data is only in items_b, you would be able to do:

要解决某些数据仅在 items_a 中而某些数据仅在 items_b 中的事实，您可以执行以下操作：

select 
  coalesce(a.col1, b.col1) as col1, 
  coalesce(a.col2, b.col2) as col2,
  coalesce(a.col3, b.col3) as col3,
  a.category_id
from items_a a, items_b b
where a.category_id = b.category_id

The coalesce function will return the first non-null value, so for each row if col1 is non null, it'll use that, otherwise it'll get the value from col2, etc.

合并函数将返回第一个非空值，因此对于每一行，如果 col1 非空，它将使用它，否则它将从 col2 获取值，等等。

回答by Esteban Küber

I guess that what you want to do is an UNIONof both tables.

我猜你想要做的是一个UNION的两个表。

If both tables have the same columns then you can just do

如果两个表都有相同的列，那么你可以做

SELECT category_id, col1, col2, col3
  FROM items_a
UNION 
SELECT category_id, col1, col2, col3 
  FROM items_b

Else, you might have to do something like

否则，你可能需要做类似的事情

SELECT category_id, col1, col2, col3
  FROM items_a 
UNION 
SELECT category_id, col_1 as col1, col_2 as col2, col_3 as col3
  FROM items_b

回答by Avinash

SELECT col1,
  col2
FROM
  (SELECT rownum X,col_table1 FROM table1) T1
INNER JOIN
  (SELECT rownum Y, col_table2 FROM table2) T2
ON T1.X=T2.Y;

回答by ArDumez

When your are three tables or more, just add union and left outer join:

当您是三个或更多表时，只需添加联合和左外连接：

select a.col1, b.col2, a.col3, b.col4, a.category_id 
from 
(
    select category_id from a
    union
    select category_id from b
) as c
left outer join a on a.category_id = c.category_id
left outer join b on b.category_id = c.category_id

回答by Reichert

I had a similar problem with a small difference: some a.category_id are not in b and some b.category_id are not in a.
To solve this problem just adapt the excelent answer from beny23 to

我有一个类似的问题，但有一点不同：有些 a.category_id 不在 b 中，有些 b.category_id 不在 a 中。
要解决这个问题，只需将 beny23 的优秀答案调整为

select a.col1, b.col2, a.col3, b.col4, a.category_id 
from items_a a
LEFT OUTER JOIN
items_b b 
on a.category_id = b.category_id

Hope this helps someone.
Regards.

希望这可以帮助某人。
问候。