关于AdventureWorks中dbo.ufnGetProductListPrice函数的两处技术疑问

疑问1：为何要关联Product表与ProductListPriceHistory表？ #

关联Production.Product表并非多余操作，实际意义如下：

• 过滤无效数据：虽然ProductListPriceHistory的ProductID是外键，但如果外键约束被意外禁用或删除，直接查询历史表可能返回对应产品已被删除的无效价格记录。关联Product表能确保只返回当前存在的产品的价格数据。
• 明确业务语义：函数核心是获取「产品」的标价，关联Product表能强化逻辑的语义完整性——我们查询的是真实存在的产品的历史价格，而非孤立的价格条目。
• 便于后续扩展：如果后续需要在函数中返回产品名称、所属类别等额外属性，关联Product表可以直接扩展查询逻辑，无需重构函数的基础结构。
• 优化查询性能：Product表的ProductID是聚集索引主键，关联时能利用这个索引快速定位目标产品，避免在ProductListPriceHistory上进行低效的非聚集索引扫描（尤其当历史表数据量较大时）。

疑问2：多条EndDate为NULL的记录导致匹配多条结果，是否属于bug？ #

这种情况确实是函数的设计缺陷，属于业务逻辑bug。

业务上，同一产品在同一时间应该只有一条生效的标价，EndDate为NULL通常表示该价格当前仍在生效。但原函数的BETWEEN条件会匹配所有满足StartDate <= @OrderDate且EndDate为NULL的记录，当存在多条这类记录时，会返回多条结果（甚至可能导致赋值变量时只取最后一条，而非最新的那条），不符合「获取最新有效价格」的业务预期。

修复方案 #

可以通过添加排序取第一条的逻辑来修正，示例如下：

ALTER FUNCTION dbo.ufnGetProductListPrice
(
    @ProductID int,
    @OrderDate datetime
)
RETURNS money
AS
BEGIN
    DECLARE @ListPrice money;

    -- 按StartDate降序取最新的生效价格
    SELECT TOP 1 @ListPrice = plph.ListPrice
    FROM Production.Product p
    INNER JOIN Production.ProductListPriceHistory plph
        ON p.ProductID = plph.ProductID
        AND @OrderDate BETWEEN plph.StartDate AND COALESCE(plph.EndDate, CONVERT(datetime, '99991231', 112))
    WHERE p.ProductID = @ProductID
    ORDER BY plph.StartDate DESC;

    RETURN @ListPrice;
END;

也可以使用窗口函数ROW_NUMBER()实现，逻辑更清晰，适合复杂场景：

ALTER FUNCTION dbo.ufnGetProductListPrice
(
    @ProductID int,
    @OrderDate datetime
)
RETURNS money
AS
BEGIN
    DECLARE @ListPrice money;

    WITH RankedPrices AS (
        SELECT 
            plph.ListPrice,
            ROW_NUMBER() OVER (ORDER BY plph.StartDate DESC) AS PriceRank
        FROM Production.Product p
        INNER JOIN Production.ProductListPriceHistory plph
            ON p.ProductID = plph.ProductID
            AND @OrderDate BETWEEN plph.StartDate AND COALESCE(plph.EndDate, CONVERT(datetime, '99991231', 112))
        WHERE p.ProductID = @ProductID
    )
    SELECT @ListPrice = ListPrice
    FROM RankedPrices
    WHERE PriceRank = 1;

    RETURN @ListPrice;
END;

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