连接表的最佳方法是什么？

Question

这更像是一个一般性的问题。我正在寻找最好的方式加入4，也许5个不同的桌子。我正在尝试创建一个Power从IBM AS400中提取实时信息，其中客户服务可以键入我们的部件编号之一，查看库存中有多少个部件，如果没有，请查看准备时间，以及是否已经为键入的部件编号输入了任何订单。

SERI是我们的库存表，有37180张记录。(现有有效库存)

METHDM是我们的试剂盒，有37459份记录。(本表包含定制工具包的材料清单，试剂盒A123中包含不同的部件编号，其中也包括SERI。)

STKA是我们的部分交货期表，有76796份记录。(准备时间是指零件需要多长时间才能进入)

OCRI是我们的销售订单表，有6497条记录。(本表包含所有客户订单)

我有一些写查询的知识，但这个问题比我过去创建的更具有挑战性。我应该从记录最多的表开始，然后离开剩下的表吗？

来自STKA 76796记录

左连接37459条记录在STKA上左加入SERI 37180记录在STKA上

STAK上的左联接OCRI 6497记录

Select

STKA.v6part as part,
STKA.v6plnt as plant,
STKA.v6tdys as pur_leadtime,
STKA.v6prpt as Pur_PrepLeadtime,
STKA.v6lead as Mfg_leadtime,
STKA.v6prpt as Mfg_PrepLeadTime,
METHDM.AQMTLP AS COMPONENT,
METHDM.AQQPPC AS QTYNEEDED,
SERI.HTLOTN AS BATCH,
SERI.HTUNIT AS UOM,
(HTQTY - HTQTYC) as ONHAND,
OCRI.DDORD# AS SALESORDER,
OCRI.DDRDAT AS PROMISED

from stka

left join METHDM on STKA.V6PART = METHDM.AQPART
left join SERI on STKA.V6PART = SERI.HTPART
left join OCRI on STKA.V6PART = OCRI.DDPART

这是加入赌桌的最佳方式吗？

Answer 1

我想你们已经有了答案，但从概念上讲，这里有一些问题要处理，我想我会给你们举几个例子，用一些和你们类似的数据，但是大大简化了。

CREATE TABLE #STKA (V6PART INT, OTHER_DATA VARCHAR(50));
CREATE TABLE #METHDM (AQPART INT, KIT_ID INT, SOME_DATE DATETIME, OTHER_DATA VARCHAR(50));
CREATE TABLE #SERI (HTPART INT, OTHER_DATA VARCHAR(50));
CREATE TABLE #OCRI (DDPART INT, OTHER_DATA VARCHAR(50));
INSERT INTO #STKA SELECT 1, NULL UNION ALL SELECT 2, NULL UNION ALL SELECT 3, NULL; --1, 2, 3 Ids
INSERT INTO #METHDM SELECT 1, 1, '20200108 10:00', NULL UNION ALL SELECT 1, 2, '20200108 11:00', NULL UNION ALL SELECT 2, 1, '20200108 13:00', NULL; --1 Id appears twice, 2 Id once, no 3 Id
INSERT INTO #SERI SELECT 1, NULL UNION ALL SELECT 3, NULL; --1 and 3 Ids
INSERT INTO #OCRI SELECT 1, NULL UNION ALL SELECT 4, NULL; --1 and 4 Ids

因此，从根本上讲，我们这里有几个问题：

O第一个问题是表中的ID不同，一个表有一个ID #4，但在其他表中都没有；o第二个问题是，一个表中有多个相同ID的行；o第三个问题是，一些表“缺少”了其他表中的ID，您已经使用了左联接，所以我将忽略这一点。

--This will select ID 1 twice, 2 once, 3 once, and miss 4 completely
SELECT
    *
FROM
    #STKA
    LEFT JOIN #METHDM ON #METHDM.AQPART = #STKA.V6PART
    LEFT JOIN #SERI ON #SERI.HTPART = #STKA.V6PART
    LEFT JOIN #OCRI ON #OCRI.DDPART = #STKA.V6PART;

所以这里的问题是，我们的“锚”表STKA中没有每个ID，而且实际上没有一个表中有每个ID。现在您的数据在这里可能很好，但是如果不是，那么您可以简单地添加一个步骤来查找每个ID，并使用它作为锚。

--This will select each ID, but still doubles up on ID 1
WITH Ids AS (
    SELECT V6PART AS ID FROM #STKA
    UNION
    SELECT AQPART AS ID FROM #METHDM
    UNION
    SELECT HTPART AS ID FROM #SERI
    UNION
    SELECT DDPART AS ID FROM #OCRI)
SELECT
    *
FROM
    Ids I
    LEFT JOIN #STKA ON #STKA.V6PART = I.Id
    LEFT JOIN #METHDM ON #METHDM.AQPART = I.Id
    LEFT JOIN #SERI ON #SERI.HTPART = I.Id
    LEFT JOIN #OCRI ON #OCRI.DDPART = I.Id;

这使用了一个公共表表达式，但是子查询也会完成这项工作。然而，这仍然给我们留下了一个问题，ID 1在列表中出现了两次，因为它在一个子表中有多个行。

解决这一问题的一种方法是选择带有最新日期的行，或者可以应用于数据的任何其他顺序：

--Pick the best row for the table where it has multiple rows, now we get one row per ID
WITH Ids AS (
    SELECT V6PART AS ID FROM #STKA
    UNION
    SELECT AQPART AS ID FROM #METHDM
    UNION
    SELECT HTPART AS ID FROM #SERI
    UNION
    SELECT DDPART AS ID FROM #OCRI),
BestMETHDM AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY AQPART ORDER BY SOME_DATE DESC) AS ORDER_ID
    FROM
        #METHDM)
SELECT
    *
FROM
    Ids I
    LEFT JOIN #STKA ON #STKA.V6PART = I.Id
    LEFT JOIN BestMETHDM ON BestMETHDM.AQPART = I.Id AND BestMETHDM.ORDER_ID = 1
    LEFT JOIN #SERI ON #SERI.HTPART = I.Id
    LEFT JOIN #OCRI ON #OCRI.DDPART = I.Id;

当然，您也可以添加一些聚合(SUM、MAX、MIN、AVG等)。来解决这个问题(如果它确实是一个问题)。另外，我使用了一个公共表表达式，但这与子查询同样有效。

Answer 2

对关于这个问题的评论作了进一步的阐述。

我要说的是，我将从SERI开始，因为该表包含了我们工厂的全部库存，并且应该涵盖其他表。

但是问题上说

SERI是我们的库存表，有37180条记录。(可用的活动库存)

根据我的经验，active inventory并不是所有部件都一样的。

通常，在这样的查询中，我希望第一个表是包含每个可能的部件ID的某种类型的部件主表。