使用 UNION 和左連接的 SQL 數據庫查詢

我在同一個 SQL Server 2008 R2 數據庫中有 6 個表（t1、t2、t3、…、t6。我想將它們分成兩組。第 1 組（TG1）包括表 1、2 和 3。第 1 組（TG1 ) 包括表 4、5 和 6。我創建了兩個查詢，每個查詢都使用 union

SELECT TG1.*
 FROM (SELECT t1.PID AS ID, t1.FVALUE AS FVALUE, t1.TVALUE AS TVALUE
        FROM mydb.myuser.t1
        UNION 
        SELECT t2.PID AS ID, t2.FVALUE AS FVALUE, t2.TVALUE AS TVALUE
        FROM mydb.myuser.t2 
        UNION 
        SELECT t3.PID AS ID, t3.FVALUE AS FVALUE, t3.TVALUE AS TVALUE
        FROM mydb.myuser.t3) AS TG1 
SELECT TG2.*
 FROM (SELECT t4.PID AS PID, t4.VALUE AS VALUE, t4.VALUEID AS VALUEID, t4.VALUEDESC AS VALUEDESC
       FROM mydb.myuser.T4
       SELECT t4.PID AS PID, t4.VALUE AS VALUE, t4.VALUEID AS VALUEID, t4.VALUEDESC AS VALUEDESC
       FROM mydb.myuser.T4
       SELECT t4.PID AS PID, t4.VALUE AS VALUE, t4.VALUEID AS VALUEID, t4.VALUEDESC AS VALUEDESC
       FROM mydb.myuser.T4) AS TG2

我得到以下兩個表

我要創建的最終查詢如下面的螢幕截圖所示，獲取 TG1 的所有值並填充 TG2 中與 TG1 的值匹配的其他欄位。

上面螢幕截圖中的值是使用 vlookup 在 Excel 中填充的，我需要幫助才能在 SQL 查詢中執行相同的操作。我感謝您的幫助。

查看：

WITH
TG1 AS ( SELECT PID AS ID, FVALUE, TVALUE
        FROM mydb.myuser.t1
        UNION 
        SELECT PID, FVALUE, TVALUE
        FROM mydb.myuser.t2 
        UNION 
        SELECT PID, FVALUE, TVALUE
        FROM mydb.myuser.t3 ),
TG2 AS ( SELECT PID, VALUE, VALUEID, VALUEDESC
        FROM mydb.myuser.T4
        UNION 
        SELECT PID, VALUE, VALUEID, VALUEDESC
        FROM mydb.myuser.T5
        UNION 
        SELECT PID, VALUE, VALUEID, VALUEDESC
        FROM mydb.myuser.T6 )
SELECT TG1.*, 
      TG21.PID FPID,
      TG22.PID TPID,
      TG21.VALUEID FVALUEID,
      TG22.VALUEID TVALUEID,
      TG21.VALUEDESC FVALUEDESC,
      TG22.VALUEDESC TVALUEDESC
FROM TG1
JOIN TG2 TG21 ON TG1.FVALUE = TG21.VALUE
JOIN TG2 TG22 ON TG1.TVALUE = TG22.VALUE

引用自：https://dba.stackexchange.com/questions/251578