SQL Join子查询使用窗口函数时查询结果丢失行问题咨询

问题原因定位 #

该问题是关联逻辑和WHERE条件的过滤规则共同导致，和窗口函数本身的使用逻辑无关，具体错误点如下：

• 你对采购单子查询PT用了LEFT JOIN，但后续WHERE子句直接写了PT.PurchaseRank = 1，相当于隐式把左连接转成了内连接。如果商品没有符合条件的采购记录，PT的所有字段都是NULL，会被这个条件直接过滤。
• 同理，你对销售单表SO用了LEFT JOIN，但WHERE子句写了SO.OrderStatus <> 'X'，也会把没有有效销售记录的商品直接过滤。你的目标商品87060大概率属于只有采购/只有销售记录，被上述两个条件误过滤。
• 你用FULL OUTER JOIN关联采购结果和销售明细表，关联条件仅匹配商品ID，会导致同商品的采购、销售记录生成笛卡尔积，后续计算销售排行的窗口函数逻辑也会受数据膨胀影响，进一步增加了结果丢失的概率。

修复方案 #

建议把采购、销售的排行逻辑都提前抽到独立子查询中完成过滤，再和商品表关联，既能避免隐式内连接的问题，也能减少JOIN后的数据量，性能比你现有写法更好，修改后代码参考：

SELECT 
    P.ProductID,
    P.ProductDescription,
    P.ProductStatusDescription,
    SO.SalesOrderID,
    SO.[SO Ship Date],
    SO.SalesQty,
    SO.[SO Branch],
    PT.PurchaseOrderID,
    PT.[PO Ship Date],
    PT.PurchaseQty,
    PT.[PO Branch]
FROM UV_Products AS P WITH (NOLOCK)
-- 预计算最新采购记录，提前过滤
LEFT JOIN (
    SELECT 
        POL.ProductID PID,
        PO.FullPurchaseOrderID PurchaseOrderID,
        PO.ReceiveBranchID 'PO Branch',
        PO.ShipDate 'PO Ship Date',
        POL.StockQuantity_LowestUM PurchaseQty
    FROM (
        SELECT 
            POL.ProductID,
            POL.FullPurchaseOrderID,
            POL.StockQuantity_LowestUM,
            ROW_NUMBER() OVER (PARTITION BY POL.ProductID ORDER BY PO.OrderDate DESC, PO.ShipDate DESC) AS PurchaseRank
        FROM UV_PurchaseOrderLines POL 
        INNER JOIN UV_PurchaseOrders PO WITH (NOLOCK) 
            ON PO.FullPurchaseOrderID = POL.FullPurchaseOrderID
            AND PO.OrderStatus <> 'X' 
    ) POL
    WHERE PurchaseRank = 1
) AS PT ON PT.PID = P.ProductID 
-- 预计算最新销售记录，提前过滤
LEFT JOIN (
    SELECT
        SOL.ProductID,
        SO.FullSalesOrderID SalesOrderID,
        SO.ShipDate 'SO Ship Date',
        SOL.StockQuantity_LowestUM SalesQty,
        SO.ShipBranchID 'SO Branch'
    FROM (
        SELECT 
            SOL.ProductID,
            SOL.FullSalesOrderID,
            SOL.StockQuantity_LowestUM,
            ROW_NUMBER() OVER (PARTITION BY SOL.ProductID ORDER BY SO.OrderDate DESC, SO.ShipDate DESC) AS SalesRank
        FROM UV_SalesOrderLines AS SOL WITH (NOLOCK)
        INNER JOIN UV_SalesOrders AS SO WITH (NOLOCK) 
            ON SOL.FullSalesOrderID = SO.FullSalesOrderID
            AND SO.OrderStatus <> 'X'
    ) SOL
    WHERE SalesRank = 1
) SO ON SO.ProductID = P.ProductID
WHERE P.ProductStatus IN ('1', '2')
-- AND P.ProductID = 87060

内容的提问来源于stack exchange，提问作者Lontiac