主页 / dba / 问题 / 251578
Accepted
Hab G.
Asked: 2019-10-22 05:33:27 +0800 CST

使用 UNION 和左连接的 SQL 数据库查询

  • 772

我在同一个 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 查询中执行相同的操作。我感谢您的帮助。

query union

1 个回答

  1. Best Answer

    查看：

    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
    • 0

相关问题