时间戳

首页 > 解决方案 > 组合两个查询的结果，没有公共列

sql - 组合两个查询的结果，没有公共列

问题描述

我只需要能够编写一个 SQL 语句，允许我qty_value将从第一个查询调用的列包含到第二个查询中

这些是查询和每个查询的结果：

查询 #1：

SELECT 
    [Records].[Name], [Tables_Data].[Street_Number], [Tables_Data].[Qty_Value]
FROM  
    [Tables_Data], [Records]
WHERE 
    [Records].[Name] = 'John' AND
    [Tables_Data].[Street_Number] = '1510' AND
    [Records].[ID] = [Tables_Data].[Record_ID]

查询 #2：

SELECT 
    [Records].[Name], [Tables_Data].[Street_Number], [Tables_Data].[Description], [Tables_Data].[Account]
FROM 
    [Records], [Tables], [Tables_Data]
WHERE 
    [Records].[Name] = 'Tim' AND
    [Tables].[Name] = 'Owners' AND
    [Tables].[Record_ID] = [Records].[ID] AND
    [Tables_Data].[Title] LIKE '%Zone%' AND
    [Tables_Data].[Record_ID] = [Records].[ID] AND
    [Tables_Data].[Table_ID] = [Tables].[ID]

结果

Records.Name, Street_Number, Qty_Value
--------------------------------------
John, 1510, 6         
John, 1510, 3
John, 1510, 6

Records.Name,  Street_Number, Description, Account
--------------------------------------------------
Tim, 2121, St1, 4040
Tim, 5251, St2, 4141
Tim, 6578, St5, 4246

如您所见，没有通用列，因此我无法使用 JOIN 或 UNION。如果我使用 WITH CLAUSE，我得到了笛卡尔积（9 条记录），这不是我需要的。

我期待以下内容：

Records.Name,  Street_Number, Description, Account, Qty_Value
-------------------------------------------------------------
Tim, 2121, St1, 4040, 6
Tim, 5251, St2, 4141, 3
Tim, 6578, St5, 4246, 6

标签： sqltsql

解决方案

You can use full join and row_number():

SELECT rt.*, rtt.*
FROM (SELECT r.[Name], t.[Street_Number], t.[Qty_Value],
             ROW_NUMBER() OVER (ORDER BY r.NAME) as seqnum
      FROM [Tables_Data] t JOIN
           [Records] r
           ON  r.[ID] = t.[Record_ID]
      WHERE r.[Name] = 'John' AND
            t.[Street_Number] = '1510' 
     ) rt FULL JOIN
     (SELECT r.[Name], td.[Street_Number], td.[Description], td.[Account],
             ROW_NUMBER() OVER (ORDER BY r.NAME) as seqnum
       FROM [Records] r JOIN
            [Tables] t
            ON r.[Record_ID] = r.[ID] JOIN
            [Tables_Data] td
            ON td.[Record_ID] = r.[ID] AND
               td.[Table_ID] = t.[ID]
       WHERE r.[Name] = 'Tim' AND
             t.[Name] = 'Owners' AND
             td.[Title] LIKE '%Zone%'
      ) rtt
      ON rt.seqnum = rtt.seqnum;

I also fixed the JOIN syntax and added table aliases.

推荐阅读