sql - 数据库设计 - 重写值

Question

我正在尝试确定我当前的设计是否最有意义。我有一个定价表，它始终具有给定产品 (ProductId) 的默认值。根据多种环境，价格可能会被覆盖。

给定这张表:

我想根据给定的城市、县、州或默认值获得适当的价格。这有点做作，因为 Type 和 Priority 表示相同的东西，并且可能仅限于 int 和索引(不需要字符串)。我使用 INT 是因为它允许我对从内部查询的 TOP 中选择哪一个进行排序和优先级排序。使用此 SQL:

SELECT DISTINCT ProductId, prices.Price
FROM [CCC].[Prices] p CROSS APPLY
(
   SELECT TOP 1 Price 
   FROM [CCC].[Prices] p2 
   WHERE p.ProductId = p2.ProductId 
     AND (Type = 'Default' 
       OR (Type = 'City' AND TypeId = 553) 
       OR (Type = 'State' AND TypeId = '4'))
   ORDER BY [Priority] DESC
) prices

得到我正在寻找的东西:

---

我正在考虑的两种方法是:

全部在应用程序代码中(具有所有可用值，然后根据空值找到最合适的价格。

在 SQL 中(如上所示)，取 desc 中按优先级排序的最高值并在 SQL 中使用交叉应用来获取所有价格(相对于单个记录)。 Coalesce(解决已发布的答案)也是一种解决方案，但我过去发现 coalesce 可以扩展。我更喜欢这个版本。

我应该考虑其他方法或模式吗？

Answer 1

如果您打算使用交叉应用，您可以在 SQL Server 中完成这项工作。像这样的方法是一种方法:

select t.*, p.price
from t cross join
     (select
      from pricing p
      where p.productid = t.productid and
            (p.city = t.city or
             p.county = t.county and p.city is null or
             p.state = t.state and p.county is null and t.county is null or
             p.state is null
            )
      order by (case when p.city is not null then 1
                     when p.county is not null then 2
                     when p.state is not null then 3
                     else 4
                end)
     ) p