問題描述

在嘗試了 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

這篇關于連接兩個具有不同數據的相同表結構的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！