SQL 视图中的表达式

Question

我是 SQL 视图的新手，所以要温柔！

我有以下 SQL 视图：

SELECT        dbo.product.name AS [Product Name], 
              ROUND(CASE [vat] WHEN 1 THEN [packcost] * 1.2 ELSE [packcost] END, 2) AS [Pack Cost],            
              ROUND(CASE [vat] WHEN 1 THEN ([packcost] * 1.2) / [units] ELSE [packcost] / [units] END, 2) AS [Unit Cost],
              dbo.purchase.unitsaleprice * dbo.product.units AS [ Pack Sale Price], dbo.purchase.unitsaleprice AS [Unit Sale Price],
              dbo.product.units * (dbo.purchase.unitsaleprice - ROUND(CASE [vat] WHEN 1 THEN ([packcost] * 1.2) / [units] ELSE [packcost] / [units] END, 2)) AS [Pack Profit], 
              dbo.purchase.unitsaleprice - ROUND(CASE [vat] WHEN 1 THEN ([packcost] * 1.2) / [units] ELSE [packcost] / [units] END, 2) AS [Unit Profit] 
           FROM dbo.product INNER JOIN
              dbo.purchase ON dbo.product.id = dbo.purchase.productID

但它似乎效率低下，因为我正在重写很多内容。

例如，我想定义 [Pack Cost] 列：

ROUND(CASE [vat] WHEN 1 THEN [packcost] * 1.2 ELSE [packcost] END, 2) AS [Pack Cost]

在其他地方使用而不是重写它。

例如，这样我可以使用：

[Pack Cost] / [Units]

定义单位成本，而不是：

ROUND(CASE [vat] WHEN 1 THEN ([packcost] * 1.2) / [units] ELSE [packcost] / [units] END, 2) AS [Unit Cost]

不确定我是否得到了正确的结果，或者是否适合这样做。

Answer 1

创建一个新的用户定义标量值函数，如下所示：

CREATE FUNCTION [dbo].[GetPackCost] 
(
    @vat int
    ,@packcost decimal
)
RETURNS decimal
AS
BEGIN
    DECLARE @packcost_calculated decimal

    SELECT @packcost_calculated = ROUND(CASE @vat WHEN 1 THEN @packcost * 1.2 ELSE @packcost END, 2) 

    RETURN @packcost_calculated

END

GO

在查询中，您将选择如下：

SELECT        dbo.product.name AS [Product Name], 
              dbo.GetPackCost([vat],[packcost]) AS [Pack Cost],            
              dbo.GetPackCost([vat],[packcost]) / [units] AS [Unit Cost],
              dbo.purchase.unitsaleprice * dbo.product.units AS [ Pack Sale Price], dbo.purchase.unitsaleprice AS [Unit Sale Price],
              dbo.product.units * (dbo.purchase.unitsaleprice - ROUND(CASE [vat] WHEN 1 THEN ([packcost] * 1.2) / [units] ELSE [packcost] / [units] END, 2)) AS [Pack Profit], 
              dbo.purchase.unitsaleprice - ROUND(CASE [vat] WHEN 1 THEN ([packcost] * 1.2) / [units] ELSE [packcost] / [units] END, 2) AS [Unit Profit] 
           FROM dbo.product INNER JOIN
              dbo.purchase ON dbo.product.id = dbo.purchase.productID

Answer 2

您可以创建一个标量函数来为您执行该逻辑。然后你就可以调用你视图中的函数了。

Answer 3

使用计算逻辑创建中间视图。称之为（例如）ProductEx。该视图可以为您计算并命名 PackCost 列。然后根据视图 ProductEx 而不是表 Product 编写所有其他视图。

Answer 4

您可以在子查询中定义它

SELECT p.name AS [Product Name], 
 P.[Pack Cost] AS [Pack Cost], 
 Round(P.[Pack Cost] / [units],2) AS [Unit Cost],
 dbo.purchase.unitsaleprice * p.units AS [ Pack Sale Price], dbo.purchase.unitsaleprice AS [Unit Sale Price],
 p.units * (dbo.purchase.unitsaleprice - Round(P.[Pack Cost] / [units],2)) AS [Pack Profit], 
 dbo.purchase.unitsaleprice - Round(P.[Pack Cost] / [units],2) AS [Unit Profit] 
FROM 
 (SELECT *, [Pack Cost] = ROUND(CASE [vat] WHEN 1 THEN [packcost] * 1.2 ELSE [packcost] END, 2) FROM dbo.product) p
INNER JOIN dbo.purchase ON p.id = dbo.purchase.productID

Answer 5

我的经验是，与执行简单计算的成本相比，获取行的“成本”是如此之大，以至于我从不关心它。
我想说可读性和/或抽象性更值得关注。

另外，您的 dbms 可能已经在幕后执行了这些优化，但您应该测量这两种方式以确保确定。

Answer 6

或者，如果您要经常进行计算，您可以将一个计算字段添加到名为 PackCost 的原始表中。那么 caclis 仅在数据输入时完成，或者不会随每个查询而更改。