在排序时简化相同情况的多次使用

Question

这是一个性能不佳的查询，我一直在尝试简化它，但我感到困惑。基本上，我们正在查看Dynamics-SL (所罗门)表，以确定是否已发货。通常，人们输入数量作为已发货，但事件处理是不正确的，因为它实际上并没有离开仓库。因此，我们在SOEvent表中查找'NPAK‘或'PINV’类型的商品，然后与SOShipLine表中的发货数量进行比较……问题是我们在WHERE子句中再次这样做，所以我们最终得到了这个超长的查询，我认为它可以被简化，它是(TIA)：

SELECT     

SOHeader.OrdNbr, 
SOHeader.CustID, 
SOHeader.User9 AS ShipDate,
SOLine.LineRef, 
SOLine.InvtID, 
SOLine.QtyOrd, 

CASE WHEN SOShipHeader.InvcNbr = '' THEN

CASE WHEN (
            SELECT MIN(EventTime)  
            FROM  Solomon_SOEvent SOEvent WITH (NOLOCK)   
            WHERE  (SOEvent.EventType = 'NPAK' OR SOEvent.EventType = 'PINV') 
            AND SOEvent.OrdNbr = SOHeader.OrdNbr) IS NULL 
THEN 0 

ELSE (
        SELECT MAX(SOShipLine.QtyShip) 
        FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
        WHERE SOShipLine.InvtID = SOLine.InvtID 
        AND SOShipLine.SiteID = SOLine.SiteID 
        AND SOShipHeader.ShipperID = SOShipLine.ShipperID
        )
END 

ELSE
  ISNULL(
        (SELECT MAX(SOShipLine.QtyShip) 
         FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
         WHERE SOShipLine.InvtID = SOLine.InvtID 
         AND SOShipLine.SiteID = SOLine.SiteID
         AND SOShipHeader.ShipperID = SOShipLine.ShipperID)
,0)

END

AS QtyShip_Corrected,

ISNULL(
    (SELECT MAX(SOShipLine.QtyShip) 
     FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
     WHERE SOShipLine.InvtID = SOLine.InvtID 
     AND SOShipLine.SiteID = SOLine.SiteID 
     AND SOShipHeader.ShipperID = SOShipLine.ShipperID)
   ,0)

AS SOShipLineQtyShip,

QtyShip

FROM Solomon_SOHeader SOHeader  WITH (NOLOCK) 
INNER JOIN Solomon_SOLine SOLine  WITH (NOLOCK) ON SOHeader.OrdNbr = SOLine.OrdNbr 
INNER JOIN Solomon_SOShipHeader SOShipHeader  WITH (NOLOCK) ON SOShipHeader.OrdNbr = SOHeader.OrdNbr 
AND SOShipHeader.Cancelled = 0

WHERE SOHeader.Status = 'O'
AND SOLine.QtyShip > 0
AND CASE WHEN SOShipHeader.InvcNbr = '' 

THEN

CASE WHEN (
        SELECT     MIN(EventTime)  
        FROM  Solomon_SOEvent SOEvent WITH (NOLOCK)   
        WHERE (SOEvent.EventType = 'NPAK' OR SOEvent.EventType = 'PINV') 
        AND SOEvent.OrdNbr = SOHeader.OrdNbr) IS NULL 
THEN 0 

    ELSE (
            SELECT MAX(SOShipLine.QtyShip) 
            FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
            WHERE SOShipLine.InvtID = SOLine.InvtID 
            AND SOShipLine.SiteID = SOLine.SiteID 
            AND SOShipHeader.ShipperID = SOShipLine.ShipperID
         )
    END 
ELSE

    ISNULL(
            (SELECT MAX(SOShipLine.QtyShip) 
             FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
             WHERE SOShipLine.InvtID = SOLine.InvtID 
             AND SOShipLine.SiteID = SOLine.SiteID 
             AND SOShipHeader.ShipperID = SOShipLine.ShipperID)
       ,0)

END 

  <> ISNULL(
            (SELECT MAX(SOShipLine.QtyShip) 
             FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
             WHERE SOShipLine.InvtID = SOLine.InvtID 
             AND SOShipLine.SiteID = SOLine.SiteID 
             AND SOShipHeader.ShipperID = SOShipLine.ShipperID)
    ,0)

Answer 1

去掉where子句，将顶部作为子查询。

然后，您可以对列名使用where子句。如下所示：

select * 
from  (
    SELECT     
        SOHeader.OrdNbr, 
        SOHeader.CustID, 
        SOHeader.User9 AS ShipDate,
        SOLine.LineRef, 
        SOLine.InvtID, 
        SOLine.QtyOrd, 

        CASE WHEN SOShipHeader.InvcNbr = '' THEN
            CASE WHEN (
                            SELECT MIN(EventTime)  
                            FROM  Solomon_SOEvent SOEvent WITH (NOLOCK)   
                        WHERE  (SOEvent.EventType = 'NPAK' OR SOEvent.EventType = 'PINV') 
                        AND SOEvent.OrdNbr = SOHeader.OrdNbr) IS NULL 
                THEN 0 

                ELSE (
                        SELECT MAX(SOShipLine.QtyShip) 
                        FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
                    WHERE SOShipLine.InvtID = SOLine.InvtID 
                    AND SOShipLine.SiteID = SOLine.SiteID 
                    AND SOShipHeader.ShipperID = SOShipLine.ShipperID
                    )
            END 

            ELSE
              ISNULL(
                    (SELECT MAX(SOShipLine.QtyShip) 
                     FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
                     WHERE SOShipLine.InvtID = SOLine.InvtID 
                     AND SOShipLine.SiteID = SOLine.SiteID
                     AND SOShipHeader.ShipperID = SOShipLine.ShipperID)
            ,0)
        END
        AS QtyShip_Corrected,

        ISNULL(
            (SELECT MAX(SOShipLine.QtyShip) 
             FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
             WHERE SOShipLine.InvtID = SOLine.InvtID 
             AND SOShipLine.SiteID = SOLine.SiteID 
             AND SOShipHeader.ShipperID = SOShipLine.ShipperID)
           ,0)

        AS SOShipLineQtyShip,1

        QtyShip

    FROM Solomon_SOHeader SOHeader  WITH (NOLOCK) 
    INNER JOIN Solomon_SOLine SOLine  WITH (NOLOCK) ON SOHeader.OrdNbr = SOLine.OrdNbr 
    INNER JOIN Solomon_SOShipHeader SOShipHeader  WITH (NOLOCK) ON SOShipHeader.OrdNbr = SOHeader.OrdNbr 
    AND SOShipHeader.Cancelled = 0

    WHERE SOHeader.Status = 'O'
    AND SOLine.QtyShip > 0
) a
where QtyShip_Corrected <> SOShipLineQtyShip