SQL Server 2005中的“类似CSS”的后备级联数据

I'm trying to implement a price fallback system in SQL server. I'd like to have a set of increasingly specific prices (eg: by region, store, warehouse, etc.) for a product, that may or may not be defined, and be able to select the most specific prices (ie: the one with most parameters defined) in a report.

我正在尝试在SQL服务器中实现价格回退系统。我希望为产品设定一套越来越具体的价格(例如:按地区,商店,仓库等),可能会或可能不会定义,并且能够选择最具体的价格(即:报告中定义了大多数参数的一个)。

For example, I might have the following data:

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

 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

So if I wanted to know the price for product Foo...

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

in Region 1, Store 1 = 2.5

在1区,商店1 = 2.5

in Region 1, Store 3 = 2.0 (Store 3 is not defined explicitly in the data, so the result comes from the NULL store for Region 1)

在区域1中,存储3 = 2.0(存储3未在数据中明确定义,因此结果来自区域1的NULL存储)

in Region 2, Store 4 = 1.0 (Region 2 is not defined explicitly in the data, so the result comes from the NULL region)

在区域2中,存储4 = 1.0(区域2未在数据中明确定义,因此结果来自NULL区域)

For the sake of simplicity, I can assume that the Store is always more specific than the region, and a store can only exist in one region.

为简单起见,我可以假设Store总是比区域更具体,而商店只能存在于一个区域。

A schema for this data would be something like this:

此数据的架构如下所示:

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)
)

Aside from crunching this data in code, how can I query this table for a list of effective prices for every product, based on (Region, Store)?

除了在代码中处理这些数据之外,如何根据(Region,Store)查询此表以获取每个产品的有效价格列表?

4 个解决方案

#1

Wow, you guys are trying way too hard. Here's the easy way: use the COALESCE command, which takes the first non-null value.

哇,你们这些人都在努力。这是一种简单的方法:使用COALESCE命令,它获取第一个非空值。

SELECT COALESCE(st.Price, rg.Price, gn.Price) AS Price
  FROM dbo.Prices gn  /*General price*/
  LEFT OUTER JOIN dbo.Prices rg /*Regional price*/
    ON rg.Product = @Product AND rg.Region = @Region AND rg.Store IS NULL
  LEFT OUTER JOIN dbo.Prices st  /*Store price*/
    ON rg.Product = @Product AND rg.Region = @Region AND rg.Store = @Store
  WHERE gn.Product = @Product
    AND gn.Region IS NULL AND gn.Store IS NULL

That way, you'll get the store price if that's not null, or the regional price if that's not null, or the general price if all else fails.

这样,如果不是空的话,您将获得商店价格,如果不是空的话,您将获得区域价格,如果所有其他方法都失败,您将获得一般价格。

#2

try this:
EDIT for all products, each listed once, even where given region and store do not exist...

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

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

#3

I think your data model might be a little screwy. Is the prices table being populated just for the report? I might review my tables, but if i had to use this setup, I would do something like this:

我认为您的数据模型可能有点棘手。价格表是否仅为报告填充?我可能会查看我的表格,但如果我必须使用此设置,我会做这样的事情:

For product prices in a specified region (NOT STORE). Use this select in a sproc that accepts region as a parameter. If I did it right, it will return the region price, if there is one, and the product price, if there isn't.

对于指定区域的产品价格(非商店)。在接受区域作为参数的sproc中使用此选择。如果我做得对,它将返回区域价格(如果有的话)和产品价格(如果没有)。

Select Product, Price from dbo.prices where (Region = @region) or (region is NULL and product not in (select product from prices where region = @Region))

选择产品,价格来自dbo.prices,其中(Region = @region)或(区域为NULL且产品不在(从区域= @Region的价格中选择产品))

For product prices in a specified store. Use this select in a sproc that accepts store as a parameter. You will also need to pass in region or figure it out from the store. If I did it right, it will return the store price, if there is one, then he region price, then the product price.

对于指定商店的产品价格。在接受存储作为参数的存储区中使用此选择。您还需要传入区域或从商店中找出它。如果我做得对,它将返回商店价格,如果有,那么他区域价格,然后是产品价格。

Select product, price from dbo.prices where (store = @store) or (store is NULL AND region = @Region and product not in (select product from prices where store = @store) or (store is NULL and region is NULL and product not in (select product from prices where region = @Region))

从dbo.prices中选择产品,价格(store = @store)或(商店为NULL AND region = @Region,产品不在(从商店= @store的价格中选择产品)或(商店为NULL,区域为NULL,产品不在(从区域= @Region的价格中选择产品))

To get prices from multiple regions or stores, just execute one of the above sprocs multiple times (in a loop or cursor).

要从多个区域或商店获取价格,只需多次执行上述某个sprocs(在循环或游标中)。

#4

I guess this must work (didn't try it, though):

我想这一定必须奏效(但是没试过):

select top 1 price
from prices
where product = @product
and isnull(region, @region) = @region
and isnull(store, @store) = @store
order by region desc, store desc

A product must have least 1 Prices record. The ORDER BY .. DESC sorts original NULL values last.

产品必须至少有1个价格记录。 ORDER BY .. DESC最后对原始NULL值进行排序。

#1