如何使用公共列从多个表中检索数据

Question

我试图通过加入公共列从两个表中获取数据。但它给了我复印机。

 ;WITH LocID AS
    (
    SELECT 1 AS LocID, 1 AS CommonID 
    UNION ALL 
    SELECT 2 AS LocID, 1 AS CommonID 
    ), 

    LocAddress AS(
    SELECT 456 AS AddressID, 'Address-1'AS 'Address',  'City-1' AS City, 'State-1' AS 'State', 1 AS CommonID
    UNION ALL 
    SELECT 789 AS AddressID, 'Address-2'AS 'Address',  'City-2' AS City, 'State-2' AS 'State', 1 AS CommonID
    )

        SELECT L.LocID, A.Address, A.City, A.State FROM LocID L INNER JOIN LocAddress A ON L.CommonID = A.CommonID ;

所需的输出只有两行，如下所示。

LocID地址城市州立

1个地址-1个城市-1个州-1个

2地址-2城市-2州-2

有什么办法能让我拿到这个吗？

Answer 1

这是意料之中的。结果是正确的。如果您想要的输出与上面提到的完全一样，查询应该如下所示：

 ;WITH LocID AS
    (
    SELECT 1 AS LocID, 1 AS CommonID 
    UNION ALL 
    SELECT 2 AS LocID, 2 AS CommonID 
    ), 

    LocAddress AS(
    SELECT 456 AS AddressID, 'Address-1'AS 'Address',  'City-1' AS City, 'State-1' AS 'State', 1 AS CommonID
    UNION ALL 
    SELECT 789 AS AddressID, 'Address-2'AS 'Address',  'City-2' AS City, 'State-2' AS 'State', 2 AS CommonID
    )

        SELECT L.LocID, A.Address, A.City, A.State FROM LocID L INNER JOIN LocAddress A ON L.CommonID = A.CommonID ;

编辑的：

或者，可以考虑加入两个字段，如下所示：

 ;WITH LocID AS
    (
    SELECT 1 AS LocID, 1 AS CommonID 
    UNION ALL 
    SELECT 2 AS LocID, 1 AS CommonID 
    ), 

    LocAddress AS(
    SELECT 456 AS AddressID, 'Address-1'AS 'Address',  'City-1' AS City, 'State-1' AS 'State', 1 AS CommonID, 1 AS LocID
    UNION ALL 
    SELECT 789 AS AddressID, 'Address-2'AS 'Address',  'City-2' AS City, 'State-2' AS 'State', 1 AS CommonID, 2 AS LocID
    )

        SELECT L.LocID, A.Address, A.City, A.State FROM LocID L INNER JOIN LocAddress A ON L.CommonID = A.CommonID AND L.LocID = A.LocID ;

Answer 2

您现有的查询包含两行被联合起来的行，只需输出这些行，您就不需要使用“公共id”来执行此操作。

SELECT * 
FROM (
    SELECT 456 AS AddressID, 'Address-1'AS 'Address',  'City-1' AS City, 'State-1' AS 'State'
    UNION ALL 
    SELECT 789 AS AddressID, 'Address-2'AS 'Address',  'City-2' AS City, 'State-2' AS 'State'
    ) AS d