sql - "CSS-like"SQL Server 2005中的后备级联数据

Question

我正在尝试在 SQL Server 中实现价格回退系统。我希望为某种产品提供一组越来越具体的价格(例如:按地区、商店、仓库等)，这些价格可能已定义，也可能未定义，并且能够选择最具体的价格(即:报告中定义了最多参数的一个)。

例如，我可能有以下数据:

 Region
--------
   1
   2

 Store  
--------
   1
   2
   3

Product | Region | Store | Price
--------------------------------
  Foo   | NULL   | NULL  |  1.0
  Foo   | 1      | NULL  |  2.0
  Foo   | 1      | 1     |  2.5
  Foo   | 1      | 2     |  2.3

所以如果我想知道产品 Foo 的价格...

• 在区域 1，商店 1 = 2.5
• 在区域 1 中，存储 3 = 2.0 (存储 3 未在数据中显式定义，因此结果来自区域 1 的 NULL 存储)
• 在区域 2 中，Store 4 = 1.0 (区域 2 未在数据中显式定义，因此结果来自 NULL 区域)

为了简单起见，我可以假设商店总是比区域更具体，并且商店只能存在于一个区域中。

此数据的架构如下所示:

CREATE TABLE Prices(
    ID      int   IDENTITY(1,1) NOT NULL,
    Product int   NOT NULL,
    Region  int   NULL,
    Store   int   NULL,
    Price   money NOT NULL,
    CONSTRAINT PK_Prices PRIMARY KEY CLUSTERED (ID ASC),
    CONSTRAINT IX_Prices UNIQUE NONCLUSTERED (Product ASC, Region ASC, Store ASC)
)

除了在代码中处理这些数据之外，我如何查询此表以获取基于(区域、商店)的每种产品的有效价格列表？

Answer 1

试试这个:
编辑所有产品，每个产品列出一次，即使给定的区域和商店不存在......

CREATE PROCEDURE GetPrice
     @Region int = null
    ,@Store int = null
AS

SELECT
    Product
        ,Region
        ,Store
        ,Price
    FROM (SELECT
              Product
                  ,Region AS Region
                  ,Store As Store
                  ,Price
                  ,Row_Number() OVER(PARTITION BY Product ORDER BY SortBy,Product,Region,Store,Price) AS RowNumber
              FROM (SELECT 1 AS SortBy,* FROM Prices WHERE (Region = @Region OR @Region IS NULL) AND (Store = @Store OR @Store IS NULL)
                    UNION
                    SELECT 2 AS SortBy,* FROM Prices WHERE (Region = @Region OR @Region IS NULL) 
                    UNION
                    SELECT 3 AS SortBy,* FROM Prices
                   ) Prices
         ) dt
    WHERE RowNumber=1
    ORDER BY Product

GO