sql - 将查询转换为 View 或表值函数

Question

我想为所有产品获取最便宜的产品变体。对于一种产品，这很容易，但我无法全神贯注地为所有产品做这件事，而且我不想使用光标。

select top 1
    pv.VariantID
from
    ProductVariant pv
    inner join (select min(price) as Price from productvariant where ProductID = @ProductID) pr
        on pv.Price = pr.Price
where
    pv.ProductID = @ProductID

如果 2 个表具有以下结构，则此方法有效:

Product - ProductID
ProductVariant - ProductID, VariantID, Price

我知道你可以做到这一点，有人知道怎么做吗？

将上述查询转换为标量函数并使用它的最佳方法是什么？所以它会变成:

select
    ProductID
    , dbo.NewFunction(ProductID)
from
    Product

Answer 1

SELECT ProductID, MIN(Price) MinPrice
FROM Product JOIN ProductVariant ON Product.ProductID = ProductVariant.ProductID
GROUP BY ProductID

这将为您提供最低价格。如果您想要随附的 VariantID，则需要再次加入 - 但是每个 VariantID 的价格是唯一的吗？

SELECT VariantID, ProductID, MinPrice
FROM ProductVariant JOIN
     (SELECT ProductID, MIN(Price) MinPrice
     FROM Product JOIN ProductVariant ON Product.ProductID = ProductVariant.ProductID
     GROUP BY ProductID) min_price
     ON ProductVariant.ProductID = min_price.ProductID AND ProductVariant.Price = min_price.MinPrice

如果每个变体的价格不是唯一的，这将无法正常工作 - 具体来说，您会得到重复的。如果发生这种情况，您可以将其更改为:

SELECT MIN(VariantID) SmallestVariantID, ProductID, MinPrice
FROM ProductVariant JOIN
     (SELECT ProductID, MIN(Price) MinPrice
     FROM Product JOIN ProductVariant ON Product.ProductID = ProductVariant.ProductID
     GROUP BY ProductID) min_price
     ON ProductVariant.ProductID = min_price.ProductID AND ProductVariant.Price = min_price.MinPrice
GROUP BY ProductID, MinPrice

这将从重复项中选择最小的变体 ID。