SQL计算各产品总最低成本时MIN(Price)无法正确使用如何解决？

问题原因 #

你直接将SUPPLY.Price替换为MIN(SUPPLY.Price)不生效的核心原因是：MIN()是聚合函数，需要先按Part分组计算每个零件的最低采购价，再关联用量表计算产品总成本，不能直接在对Product分组的聚合逻辑里嵌套计算。

正确实现方案 #

方案1：子查询预计算零件最低价（兼容所有SQL数据库）

CREATE VIEW PRODUCT_COST(ProductID, TotalCost)
AS 
    SELECT 
        pu.Product AS ProductID,
        SUM(pu.Quantity * mp.min_price) AS TotalCost
    FROM 
        PARTS_USED pu
    INNER JOIN (
        -- 内层查询先计算每个零件的最低采购价
        SELECT Part, MIN(Price) AS min_price
        FROM SUPPLY
        GROUP BY Part
    ) mp ON pu.Part = mp.Part
    GROUP BY 
        pu.Product;

方案2：CTE实现（支持MySQL 8.0+/PostgreSQL/SQL Server等新版本数据库）

CREATE VIEW PRODUCT_COST(ProductID, TotalCost)
AS 
WITH PartMinPrice AS (
    SELECT Part, MIN(Price) AS min_price
    FROM SUPPLY
    GROUP BY Part
)
SELECT 
    pu.Product AS ProductID,
    SUM(pu.Quantity * pmp.min_price) AS TotalCost
FROM PARTS_USED pu
INNER JOIN PartMinPrice pmp ON pu.Part = pmp.Part
GROUP BY pu.Product;

扩展说明 #

如果存在零件没有任何供应商报价的情况，INNER JOIN会直接过滤掉用到该零件的产品。如果你需要保留这类产品（无报价零件成本按0计算），可以将INNER JOIN改为LEFT JOIN，并配合COALESCE处理空值：

SUM(pu.Quantity * COALESCE(pmp.min_price, 0)) AS TotalCost

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