如何筛选匹配SKU的低价产品？SQL查询优化求助

要完成第三项去重并保留低价产品的需求，你需要先整合两家供应商的有效数据并纳入报价字段，再通过窗口函数筛选每个SKU的最低价记录。以下是修改后的SQL：

WITH CombinedProducts AS (
    -- 供应商1的有效产品，包含报价字段
    SELECT 
        Vendor,
        VendorStockCode AS SKU,
        StockDescription AS Description,
        StockAvailable AS Stock,
        CAST(DealerEx AS DECIMAL(10,2)) AS Price
    FROM Table1
    WHERE NOT EXISTS (
        SELECT sku 
        FROM Table3_product_meta_lookup
        WHERE Table1.VendorStockCode = Table3_product_meta_lookup.sku
    )
      AND PrimaryCategory NOT IN ('SERVICES', 'WARRANTY')
      AND CAST(DealerEx AS DECIMAL(10,2)) <= 15000.00

    UNION ALL -- 保留所有重复SKU，后续统一处理去重
    -- 供应商2的有效产品，需替换为Table2实际的报价字段（示例为Manufacture_Price）
    SELECT 
        Manufacture_Name AS Vendor,
        Manufacture_Code AS SKU,
        Short_Description AS Description,
        Stock_Qty AS Stock,
        CAST(Manufacture_Price AS DECIMAL(10,2)) AS Price
    FROM Table2
    WHERE NOT EXISTS (
        SELECT sku 
        FROM Table3_product_meta_lookup
        WHERE Table2.Manufacture_Code = Table3_product_meta_lookup.sku
    )
),
RankedProducts AS (
    SELECT 
        *,
        -- 按SKU分组，报价升序排序，最低价记录的行号为1
        ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY Price ASC) AS RankNum
    FROM CombinedProducts
)

-- 筛选每个SKU中报价最低的记录
SELECT Vendor, SKU, Description, Stock
FROM RankedProducts
WHERE RankNum = 1;

关键修改说明： #

• 替换UNION为UNION ALL：原SQL的UNION会自动去重，无法保留重复SKU用于价格对比，改用UNION ALL保留所有符合条件的记录。
• 新增报价字段：必须在两个供应商的查询中加入报价字段（Table1用DealerEx，Table2需替换为实际存在的报价字段），否则无法完成低价筛选。
• 窗口函数分组排序：通过ROW_NUMBER()按SKU分组、报价升序排序，让每个SKU的最低价记录行号为1，最后筛选行号为1的记录即可实现去重留低。
• 简化条件写法：将PrimaryCategory != 'SERVICES' AND PrimaryCategory != 'WARRANTY'改为PrimaryCategory NOT IN ('SERVICES', 'WARRANTY')，逻辑更简洁。

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