代码之家 › 专栏 › 技术社区 › Jamie Marshall

不使用联接语法从联接中获取特定列?

tsql sql-server

Jamie Marshall · 技术社区 · 6 年前

还有别的方法写这个吗?

SELECT src.ID, factDeviceBuild.ID
    FROM #factDeviceBuild as src
    INNER JOIN AppsFlyer.FactDeviceBuild AS factDeviceBuild
    ON src.[DimDevice_Id] = factDeviceBuild.[DimDevice_Id] AND
        src.[DimDeviceModel_Id] = factDeviceBuild.[DimDeviceModel_Id] AND
        src.[DimPlatform_Id] = factDeviceBuild.[DimPlatform_Id] AND
        src.[DimOSVersion_Id] = factDeviceBuild.[DimOSVersion_Id] AND
        src.[DimSDKVersion_Id] = factDeviceBuild.[DimSDKVersion_Id] AND
        src.[DimCarrier_Id] = factDeviceBuild.[DimCarrier_Id] AND
        src.[DimOperator_Id] = factDeviceBuild.[DimOperator_Id]

我一直在尝试做一些不同的事情(不起作用)像这样

SELECT *, factDeviceBuild.ID
    FROM #factDeviceBuild
    WHERE EXISTS (
        SELECT [DimDevice_Id], [DimDeviceModel_Id], [DimPlatform_Id],
            [DimOSVersion_Id], [DimSDKVersion_Id], [DimCarrier_Id],
            [DimOperator_Id]
        FROM AppsFlyer.FactDeviceBuild AS factDeviceBuild
        )

SELECT factDeviceBuild.ID, 
        factDeviceBuild.[ID]
    FROM (
        SELECT [DimDevice_Id], [DimDeviceModel_Id], [DimPlatform_Id],
            [DimOSVersion_Id], [DimSDKVersion_Id], [DimCarrier_Id],
            [DimOperator_Id]
        FROM AppsFlyer.FactDeviceBuild AS factDeviceBuild
        INTERSECT
        SELECT [DimDevice_Id], [DimDeviceModel_Id], [DimPlatform_Id],
            [DimOSVersion_Id], [DimSDKVersion_Id], [DimCarrier_Id],
            [DimOperator_Id]
        FROM AppsFlyer.#factDeviceBuild AS factDeviceBuild
    ) AS A

我只是在玩一些查询调优。 EXCEPT 和 INTERSECT 因为他们对待孩子的方式特别有趣 NULLS .

显然我需要一个 CROSS JOIN OUTER JOIN INNER JOIN 从零开始,但我看不出有什么特别的收获。

3 回复 | 直到 6 年前

Razvan Socol 6 年前

我相信你正在寻找这样的东西:

SELECT src.ID, fact.ID
FROM #factDeviceBuild as src
INNER JOIN AppsFlyer.FactDeviceBuild AS fact
ON EXISTS (
    SELECT src.DimDevice_Id, src.DimDeviceModel_Id, src.DimPlatform_Id,
        src.DimOSVersion_Id, src.DimSDKVersion_Id, src.DimCarrier_Id,
        src.DimOperator_Id
    INTERSECT
    SELECT fact.DimDevice_Id, fact.DimDeviceModel_Id, fact.DimPlatform_Id,
        fact.DimOSVersion_Id, fact.DimSDKVersion_Id, fact.DimCarrier_Id,
        fact.DimOperator_Id
)

用这个 INTERSECT 语法(而不是通常的条件)的优点是将NULL-s视为相同的值。例如,如果 DimCarrier_Id 和 DimOperator_Id 列将允许NULL-s,等效条件为:

SELECT src.ID, fact.ID
FROM #factDeviceBuild as src
INNER JOIN AppsFlyer.FactDeviceBuild AS fact
ON src.DimDevice_Id = fact.DimDevice_Id AND
    src.DimDeviceModel_Id = fact.DimDeviceModel_Id AND
    src.DimPlatform_Id = fact.DimPlatform_Id AND
    src.DimOSVersion_Id = fact.DimOSVersion_Id AND
    src.DimSDKVersion_Id = fact.DimSDKVersion_Id AND
    (src.DimCarrier_Id = fact.DimCarrier_Id OR src.DimCarrier_Id IS NULL AND fact.DimCarrier_Id IS NULL) AND
    (src.DimOperator_Id = fact.DimOperator_Id OR src.DimOperator_Id IS NULL AND fact.DimOperator_Id IS NULL)

Eralper 6 年前

以下是相同的

SELECT src.ID, factDeviceBuild.ID
    FROM #factDeviceBuild as src, AppsFlyer.FactDeviceBuild AS factDeviceBuild
    WHERE
        src.[DimDevice_Id] = factDeviceBuild.[DimDevice_Id] AND
        src.[DimDeviceModel_Id] = factDeviceBuild.[DimDeviceModel_Id] AND
        src.[DimPlatform_Id] = factDeviceBuild.[DimPlatform_Id] AND
        src.[DimOSVersion_Id] = factDeviceBuild.[DimOSVersion_Id] AND
        src.[DimSDKVersion_Id] = factDeviceBuild.[DimSDKVersion_Id] AND
        src.[DimCarrier_Id] = factDeviceBuild.[DimCarrier_Id] AND
        src.[DimOperator_Id] = factDeviceBuild.[DimOperator_Id]

Adrian Maxwell 6 年前

如果没有数据或预期结果的可视化,我猜您需要将7个id类型“unpivot”到更少的列中,这降低了连接语法的复杂性。例如。:

select
     src.id, f.fact_id, ca.id_type, ca.id_value
from #factDeviceBuild as src
cross apply (
    values
       ('DimDevice_Id',src.[DimDevice_Id])
      ,('DimDeviceModel_Id',src.[DimDeviceModel_Id])
      ,('DimPlatform_Id',src.[DimPlatform_Id])
      ,('DimOSVersion_Id',src.[DimOSVersion_Id])
      ,('DimSDKVersion_Id',src.[DimSDKVersion_Id])
      ,('DimCarrier_Id',src.[DimCarrier_Id])
      ,('DimOperator_Id',src.[DimOperator_Id])
    ) ca (id_type, id_value)
inner join (
    select
         fact.id fact_id, ca.id_type, ca.id_value
    from AppsFlyer.FactDeviceBuild AS fact
    cross apply (
        values
           ('DimDevice_Id',fact.[DimDevice_Id])
          ,('DimDeviceModel_Id',fact.[DimDeviceModel_Id])
          ,('DimPlatform_Id',fact.[DimPlatform_Id])
          ,('DimOSVersion_Id',fact.[DimOSVersion_Id])
          ,('DimSDKVersion_Id',fact.[DimSDKVersion_Id])
          ,('DimCarrier_Id',fact.[DimCarrier_Id])
          ,('DimOperator_Id',fact.[DimOperator_Id])
        ) ca (id_type, id_value)
    where ca.id_value IS NOT NULL
    ) as f on ca.id_type = f.id_type and ca.id_value = f.id_value

注意我没有使用TSQL的“unpivot”特性,因为我更喜欢上面的语法。使用此选项时没有其他性能缺点 apply/values

注意:所有7个id类型列都必须是“兼容”数据类型,“unpivot”才能正常工作。例如,所有7都是整数,这将使 id_value 一列整数。