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

本文介绍了如何将两个表中的列合并为一个输出？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个表，它们的信息相似。我们称它们为 items_a 和 items_b 。它们应该是一个，但是它们来自不同的来源，所以不是。当我完全联接两个表时，某些行最终会来自一个或两个表中的数据。两个表中的列之一是 category_id 。我想使用 category_id 将分类表与类别表交叉。但是，我现在有两个 category_id 列（一个来自 items_a ，另一个来自 items_b ）。有没有办法将两列合并为一个？

I have two tables with similar information. Let's call them items_a and 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_id columns now (one from items_a and one from items_b). Is there a way to merge the two columns into one?

我希望这不会对一个问题造成混淆。

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

推荐答案

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

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.

围绕某些数据仅在items_a中并且某些数据仅在items_b中的事实，您可以执行以下操作：

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:

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

coalcece函数将返回第一个非空值，因此对于col1不为空的每一行，它将使用，否则它将从col2等获取值。

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.