查询客户最新发票价格与报价并筛选价格差异结果

你的原查询存在两个核心问题：

• 全局取最大交易日期，没有按客户+商品分组获取各自的最新记录
• 未筛选报价表的最新数据，且关联时仅匹配客户，未匹配商品

正确SQL语句 #

WITH LatestInvoices AS (
    SELECT 
        Customer, 
        Item, 
        Price AS InvPrice,
        ROW_NUMBER() OVER (PARTITION BY Customer, Item ORDER BY TranDate DESC) AS rn
    FROM Invoices
),
LatestQuotes AS (
    SELECT 
        Customer, 
        Item, 
        Price AS QuotePrice,
        ROW_NUMBER() OVER (PARTITION BY Customer, Item ORDER BY TranDate DESC) AS rn
    FROM Quotes
)
SELECT 
    li.Customer,
    li.Item,
    li.InvPrice,
    lq.QuotePrice
FROM LatestInvoices li
JOIN LatestQuotes lq 
    ON li.Customer = lq.Customer 
    AND li.Item = lq.Item
WHERE li.rn = 1 
    AND lq.rn = 1
    AND li.InvPrice <> lq.QuotePrice
ORDER BY li.Customer, li.Item;

逻辑说明 #

1. 提取最新发票记录：
   用ROW_NUMBER()按Customer和Item分组，每组内按交易日期倒序编号，编号为1的就是该客户该商品的最新发票。
2. 提取最新报价记录：
   同样用ROW_NUMBER()处理报价表，得到每个客户每个商品的最新报价。
3. 关联并筛选：
   将两个结果集按客户+商品关联，只保留双方最新记录，且价格不同的数据。

测试结果 #

执行上述SQL后，会得到你预期的结果：

原查询问题点拆解 #

• (SELECT MAX(TranDate) FROM Invoices)是全局最大日期，不是按客户商品分组的，一旦有其他客户商品的最新日期不同，就会漏数据或错匹配
• 未处理报价表的最新数据，会把所有报价和发票关联，导致结果冗余或错误
• 关联仅匹配Customer，同一客户多商品时会出现跨商品的错误关联

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