时间戳

首页 > 解决方案 > 如何在查询中多次重复使用复杂的公式

sql - 如何在查询中多次重复使用复杂的公式

问题描述

我有几个半复杂的公式查询。我删除了很多额外的内容，并将其归结为与我的问题相关的内容。

DECLARE @Company              AS NVARCHAR(8) = 'Acme'
                  , @Plant                AS VARCHAR(20)  = 'Albuquerque'
                  , @Warehouse            AS NVARCHAR(8) = 'TNT-WH1'
                  , @DaysAhead            AS INT         = 10
                  , @FulfillmentThreshold AS INT         = 90;

            SELECT
                @Company AS 'Company'                    
                , orel.SellingReqQty
                , orel.OurJobShippedQty + orel.OurStockShippedQty AS 'ShippedQty'
                , orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty) AS 'ReqQtyRemaining'
                , CASE
                    WHEN ((pw.OnHandQty - (pw.AllocatedQty + pw.ReservedQty))
                            / (orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty)) * 100)
                            >= 100 THEN '100'
                    ELSE
                        ((pw.OnHandQty - (pw.AllocatedQty + pw.ReservedQty))
                            / (orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty)) * 100)
                END
                AS 'PercentAvailToFulfill'

            FROM
                dbo.OrderRel AS orel WITH (NOLOCK)

                INNER JOIN erp.PartWhse AS pw WITH (NOLOCK)
                    ON (pw.Company = orel.Company AND
                        pw.PartNum = orel.PartNum AND
                        pw.WarehouseCode = @Warehouse)

            WHERE
                orel.Company = @Company                
            AND orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty) > 0 -- ReqQtyRemaining
             AND (orel.ReqDate <= DATEADD(DAY, @DaysAhead, GETDATE())
                  OR
                  orel.RushPart_c = 1 )

            AND ((pw.OnHandQty - (pw.AllocatedQty + pw.ReservedQty))
                / (orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty)) * 100)
                > @FulfillmentThreshold

            AND (orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty)) > (pw.AllocatedQty + pw.ReservedQty)

如您所见，表示剩余数量的这段代码重复了很多次，但对于返回的每条记录都需要计算：

orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty)

我正在寻找一种通过将其更改为单个变量来使其更具可读性的方法。是否可以将其设置为为每一行计算的变量，以便我可以将其称为@ReqQtyRemaining？

标签： sqlsql-servertsql

解决方案

在您的情况下，如果所有这些列都在同一个表中，您可以使用COMPUTEDcolumn.

因此，您只需修改表结构以添加新的计算列。

ALTER TABLE dbo.OrderRel ADD ReqQtyRemaining AS (SellingReqQty - OurJobShippedQty + OurStockShippedQty)

一旦你创建了一个计算列，这个列作为一个名字说将总是自动更新，它可以在查询中作为所有其他列使用，你不能手动更新这个列。

然后你不需要在查询中进行计算，而且你也可以在这个列上放置一个索引。

更多信息在这里： https ://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver15

如果您无法更改表结构，您可以尝试使用CTE (Common Table Expressions). 但是，您不会看到任何具体的性能改进，您只会提高相同代码的可读性和可重用性。因此，您的查询应如下所示：

DECLARE @Company                AS NVARCHAR(8)  = 'Acme'
        , @Plant                AS VARCHAR(20)  = 'Albuquerque'
        , @Warehouse            AS NVARCHAR(8)  = 'TNT-WH1'
        , @DaysAhead            AS INT          = 10
        , @FulfillmentThreshold AS INT          = 90;

;WITH OrderRelWithComputedCTE AS
(
    SELECT *, (SellingReqQty - OurJobShippedQty + OurStockShippedQty) ReqQtyRemaining
    FROM dbo.OrderRel WITH (NOLOCK)
)
SELECT
    @Company AS 'Company'                    
    , orel.SellingReqQty
    , orel.OurJobShippedQty + orel.OurStockShippedQty AS 'ShippedQty'
    , orel.ReqQtyRemaining
    , CASE
        WHEN ((pw.OnHandQty - (pw.AllocatedQty + pw.ReservedQty))
                / orel.ReqQtyRemaining * 100)
                >= 100 THEN '100'
        ELSE
            ((pw.OnHandQty - (pw.AllocatedQty + pw.ReservedQty))
                / orel.ReqQtyRemaining * 100)
    END
    AS 'PercentAvailToFulfill'

FROM
    OrderRelWithComputedCTE AS orel
    INNER JOIN erp.PartWhse AS pw WITH (NOLOCK)
               ON (pw.Company = orel.Company AND
                   pw.PartNum = orel.PartNum AND
                   pw.WarehouseCode = @Warehouse)
WHERE
    orel.Company = @Company                
AND orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty) > 0 -- ReqQtyRemaining
 AND (orel.ReqDate <= DATEADD(DAY, @DaysAhead, GETDATE())
      OR
      orel.RushPart_c = 1 )

AND ((pw.OnHandQty - (pw.AllocatedQty + pw.ReservedQty))
    / orel.ReqQtyRemaining * 100)
    > @FulfillmentThreshold

AND orel.ReqQtyRemaining > (pw.AllocatedQty + pw.ReservedQty)

注意CTESELECT 语句顶部和这一行的命令：

FROM OrderRelWithComputedCTE AS orel

推荐阅读