连接两个具有不同数据的相同表结构 [英] join two identical table structures with different data

问题描述

在尝试了 COALESCE 方法后，我现在看到一个问题，即每个瓦特数类别的数据都重复相同的数据.第 2 列是瓦数.

After attempting the COALESCE method, I'm now seeing an issue where the data is repeating itself with the same data for each wattage category. Column 2 is wattage.

我创建了两个临时表，它们都具有完全相同的表结构.在这些表中，有多个列可能具有相同的值，然后有几个值列将具有不同的数字.其中一些将在一列中为 NULL，而在另一列中不为 null.我想将所有值放在一起，并且在具有相同站点和工厂的行上，我希望将这些值连接起来.

I've created two temp tables, both with the exact same table structure. In these tables, there are multiple columns that could have the same values, and then a few value columns that will have different numbers. Some of these will be NULL in one column, and not null in another. I want to get all the values together, and on rows with the same site and plant I'd like the values joined.

这是两个表的外观示例以及我期望的结果

Here is an example of what the two tables could look like and the result I'd expect

表 1:

SITE      PLANT          VALUE_1           VALUE 2
S1        P1             54                66
S1        P2             43                43

表 2:

SITE      PLANT         VALUE_1           VALUE_2
S1        P1            33                43
S2        P1            34                22

结果:

SITE      PLANT         t1_VALUE_1    t1_VALUE_2    t2_VALUE_1      t2_VALUE2
S1        P1            54            66            33              43
S1        P2            43            43            NULL            NULL
S2        P1            NULL          NULL          34              22

我最初的想法是完全加入.但是，这不起作用，因为在您的 select 语句中，您必须指定从何处获取列，例如站点和工厂；但同时选择 t1.site 和 t2.site 会生成两列.我得到的最接近的结果是下面的查询，但是只要 S2 中有一个站点和工厂不在 S1 中的结果，您就会收到 S1 和 S2 的空值.

My original thoughts would be a full join. However, this doesn't work because in your select statement you must specify where to grab the columns from, like site and plant; but to select both t1.site and t2.site would generate two columns. The closest thing I got was the query below, however anytime there is a result in S2 that has a site and plant not in S1, you receive null values for S1 and S2.

SELECT t1.Site, t1.Plant, t1.Value_1, t1.Value_2, t2.Value_1, t2.Value_2 
FROM table1 t1 
FULL JOIN table2 t2 
ON t1.site = t2.site
AND t1.plant = t2.plant

推荐答案

完成这个查询需要两个技巧.第一个是完全连接.完全联接将允许您组合两个表，并在不匹配联接条件时在任一表中插入空值.第二个是 COALESCE，它允许您从结果中为该行提供记录的表中获取工厂和站点.

Two tricks are needed to complete this query. The first is a FULL JOIN. A full join will allow you to combine both tables, and insert nulls in either table when you don't match the join condition. The 2nd is COALESCE, which will allow you take the plant and site from whichever of the tables provides a record for this row in the results.

SELECT Coalesce(t1.Site,t2.Site) As Site, COALESCE(t1.Plant, t2.Plant) As Plant,
    t1.Value_1 As t1_Value_1, t1.Value_2 As t1_Value_2,
    t2.Value_1 As t2_Value_1, t2.Value_2 As t2_Value_2
FROM Table1 t1
FULL JOIN Table2 t2 ON t1.Site = t2.Site AND t1.Plant = t2.Plant

这篇关于连接两个具有不同数据的相同表结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！