3种方式与子查询和多个where子句联接

我相信您想要的就是我下面的内容。无论查询如何，您都希望将INV行保持在ITEM_CODE等于ASM.EN.CD的位置deliveries_picks*。

OR通常会降低查询速度。但我认为造成这种情况变慢的原因很可能是因为在子查询中包含额外条件会导致更多的行匹配，因为它与外部表（INV）相关联。换句话说，当该额外条件为true时，子查询表中的所有行都将通过过滤器。优化器可能尚未制定出可以充分利用您只想查看“第一个”事实的计划。

请注意，在这些子查询中TOP不使用时，您确实会遇到问题ORDER BY。真的没有第一行没有第一行。顺便说一句，您对所有联接列的使用对我来说似乎都令人怀疑。如果您可以描述表之间的关系，则可能会得到更好的查询。最后，这些“条形码”条件可能更简单，@barcode in (BARCODE1, BARCODE2)因为您不必重复串联。

SELECT TOP 1 
    INV.SUBINVENTORY_CODE, INV.LOCATOR_CODE, INV.ITEM_CODE,
    dp.DESCRIPTION, dp.barcode1, dp.barcode2, INV.QTY,  
    dp.FROM_SUB_INVENTORY_CODE, dp.FROM_LOCATOR_CODE,
    INV.SUBINVENTORY_CODE, Inv.INVENTORY_ITEM_ID, dp_hist.barcode1, 
    dp_hist.barcode2, dp_hist.DESCRIPTION,
    dp_hist.FROM_SUB_INVENTORY_CODE, dp_hist.FROM_LOCATOR_CODE 
FROM 
    INVENTORY AS INV
    FULL OUTER JOIN deliveries_picks_hist AS dp_hist ON Inv.item_code = dp_hist.item_code 
    FULL OUTER JOIN deliveries_picks AS dp ON dp_hist.item_code = dp.ITEM_CODE 
WHERE 
    INV.LOCATOR_CODE = 'BMS.S.T.G'
    AND INV.ITEM_CODE in (
        (
            select TOP 1 ITEM_CODE from deliveries_picks_hist
            where BARCODE1 = '" & barcode & "' or BARCODE2 = '" & barcode & "'
        ),    
        (
            select TOP 1 ITEM_CODE from deliveries_picks
            where BARCODE1 = '" & barcode & "' or BARCODE2 = '" & barcode & "'
        ),
        'ASM.EN.CD'
    )
group by 
    INV.INVENTORY_ITEM_ID, INV.LOCATOR_CODE, INV.ITEM_CODE, INV.QTY,
    dp_hist.barcode1, dp_hist.barcode2, dp.DESCRIPTION, 
    dp.FROM_LOCATOR_CODE,  dp.FROM_SUB_INVENTORY_CODE,
    INV.SUBINVENTORY_CODE, DP.BARCODE1, DP.BARCODE2, dp_hist.DESCRIPTION,
    dp_hist.FROM_SUB_INVENTORY_CODE, dp_hist.FROM_LOCATOR_CODE 
order by 
    dp_hist.DESCRIPTION

如果CASE不起作用，使用应该可以“推迟”子查询的执行：

    AND
    CASE
        WHEN INV.ITEM_CODE = 'ASM.EN.CD' THEN 1
        WHEN INV.ITEM_CODE IN (
            (
                select TOP 1 ITEM_CODE from deliveries_picks_hist
                where BARCODE1 = '" & barcode & "' or BARCODE2 = '" & barcode & "'
            ),    
            (
                select TOP 1 ITEM_CODE from deliveries_picks
                where BARCODE1 = '" & barcode & "' or BARCODE2 = '" & barcode & "'
            )
        ) THEN 1
    END = 1

我的猜测是，这是您对联接逻辑的意图：

FROM 
    INVENTORY AS INV LEFT OUTER JOIN (
        deliveries_picks AS dp
        FULL OUTER JOIN
        deliveries_picks_hist AS dp_hist
            ON dp_hist.ITEM_CODE = dp.ITEM_CODE
    )
        ON INV.ITEM_CODE = COALESCE(dp.ITEM_CODE, dp_hist.ITEM_CODE)

通常，当您看到完全连接时，您还会看到很多COALESCE()操作。