主页 / dba / 问题 / 127688
Accepted
Kaishu
Asked: 2016-01-29 23:28:58 +0800 CST

如何在 SQL 的 CASE 子句中使用 SET 操作？

  • 772

我有以下结构，我SET在一个子句中进行操作，CASE但它产生了一个错误：

附近的语法不正确SET

询问：

DECLARE @tbl_Result TABLE
(
    RowID INT IDENTITY(1,1),
    TtlNOfCmpSale7to12 INT,
    TtlNOfCmpSale4to6 INT,
    TtlNOfCmpSale0to3 INT,
    TtlNOfCmpSaleSpanFirst SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanSecond SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanThird SMALLINT DEFAULT 0
);

DECLARE 
    @Declining FLOAT,
    @StableStart SMALLINT,
    @StableEnd SMALLINT,
    @Increasing SMALLINT;

SELECT
    @Declining = 0.90,
    @StableStart = 90,
    @StableEnd = 110,
    @Increasing = 110;

DECLARE @TrendValue FLOAT; 

UPDATE R
SET R.TtlNOfCmpSaleSpanFirst =
    CASE
        WHEN R.TtlNOfCmpSale7to12 != 0 THEN
            SET @TrendValue = R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12
            (
                CASE 
                    WHEN @TrendValue > @Increasing THEN 0
                    WHEN @TrendValue >  @StableStart 
                        AND @TrendValue <  @StableEND THEN 1
                    WHEN @TrendValue < @Declining THEN 2
                END
            )
    END,
    R.TtlNOfCmpSaleSpanSecond = 
        CASE 
            WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6
                (
                    CASE 
                        WHEN @TrendValue > @Increasing THEN 0
                        WHEN @TrendValue >  @StableStart 
                            AND @TrendValue <  @StableEND THEN 1
                        WHEN @TrendValue < @Declining THEN 2
                    END
                )
        END,
    R.TtlNOfCmpSaleSpanThird = 
        CASE 
            WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12
                (
                    CASE 
                        WHEN @TrendValue > @Increasing THEN 0
                        WHEN @TrendValue >  @StableStart 
                            AND @TrendValue <  @StableEND THEN 1
                        WHEN @TrendValue < @Declining THEN 2
                    END
                )
        END
FROM Result R;

如果有另一种方法将表达式的结果存储到@TrendValue 中，这样就无需每次都计算该表达式。

避免除以零错误也很好（这就是我添加各种R.TtlNOfCmpSale7to12 != 0检查的原因）。

sql-server sql-server-2012

2 个回答

  1. Best Answer

    根据您的评论，您似乎正在寻找一种方法来避免一遍又一遍地重复相同的计算。

    您可以使用APPLY VALUES一次来计算所需的值，然后在其余计算中使用别名列。

    DECLARE @Declining FLOAT,
@StableStart SMALLINT,
@StableEnd SMALLINT,
@Increasing SMALLINT

SELECT  @Declining = 0.90,
@StableStart = 90,
@StableEnd = 110,
@Increasing = 110

DECLARE @TrendValue FLOAT; 

UPDATE R
SET
 R.TtlNOfCmpSaleSpanFirst = 
            CASE 
                WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                  CASE WHEN Trends.TrendFirst > @Increasing THEN 0
                        WHEN Trends.TrendFirst >  @StableStart AND Trends.TrendFirst <  @StableEND THEN 1
                        WHEN Trends.TrendFirst < @Declining THEN 2
                  END
            END,
 R.TtlNOfCmpSaleSpanSecond = 
            CASE 
                WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                  CASE WHEN Trends.TrendSecond > @Increasing THEN 0
                        WHEN Trends.TrendSecond >  @StableStart AND Trends.TrendSecond <  @StableEND THEN 1
                        WHEN Trends.TrendSecond < @Declining THEN 2
                  END
            END,

 R.TtlNOfCmpSaleSpanThird = 
            CASE 
                WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                  CASE WHEN Trends.TrendThird > @Increasing THEN 0
                        WHEN Trends.TrendThird >  @StableStart AND Trends.TrendThird <  @StableEND THEN 1
                        WHEN Trends.TrendThird < @Declining THEN 2
                  END
            END

FROM Result R
CROSS APPLY (
    VALUES (IIF(R.TtlNOfCmpSale7to12 <> 0, R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12, 0)
              IIF(R.TtlNOfCmpSale4to6 <> 0, R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6, 0) 
              IIF(R.TtlNOfCmpSale7to12 <> 0, R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12, 0)
) AS Trends(TrendFirst, TrendSecond, TrendThird);
    • 4

  2. 这应该有效：

    WITH data AS(
    SELECT TtlNOfCmpSaleSpanFirst, TtlNOfCmpSaleSpanSecond, TtlNOfCmpSaleSpanThird
        , TtlNOfCmpSale0to3, TtlNOfCmpSale4to6, TtlNOfCmpSale7to12
        , TrendValueFirst = IIF(R.TtlNOfCmpSale7to12 = 0, 0, R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12)
        , TrendValueSecond = IIF(R.TtlNOfCmpSale4to6 = 0, 0, R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6)
        , TrendValueThird = IIF(R.TtlNOfCmpSale7to12 = 0, 0, R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12)
    FROM @tbl_Resul R
)
-- SELECT
UPDATE R SET 
    TtlNOfCmpSaleSpanFirst = CASE 
            WHEN TrendValueFirst > @Increasing THEN 0
            WHEN TrendValueFirst >  @StableStart AND TrendValueFirst <  @StableEND THEN 1
            WHEN TrendValueFirst < @Declining THEN 2
        END
    , TtlNOfCmpSaleSpanSecond = CASE 
            WHEN TrendValueSecond > @Increasing THEN 0
            WHEN TrendValueSecond >  @StableStart AND TrendValueSecond <  @StableEND THEN 1
            WHEN TrendValueSecond < @Declining THEN 2
        END
    , TtlNOfCmpSaleSpanThird = CASE 
            WHEN TrendValueThird > @Increasing THEN 0
            WHEN TrendValueThird >  @StableStart AND TrendValueThird <  @StableEND THEN 1
            WHEN TrendValueThird < @Declining THEN 2
        END
FROM data R;

    当分频器等于 0 时，它使用IIF将 TrendValue 设置为 0。

    CTE 从您的表中获取所有行并计算 3 个趋势值。然后，您可以从中选择或更新 CTE 中使用的主表中的列。这就像视图中的更新一样。

    示例数据和输出如下所示。

    样本：

    DECLARE @tbl_Resul TABLE(
    RowID INT IDENTITY(1,1),
    TtlNOfCmpSale7to12 INT,
    TtlNOfCmpSale4to6 INT,
    TtlNOfCmpSale0to3 INT,
    TtlNOfCmpSaleSpanFirst SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanSecond SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanThird SMALLINT DEFAULT 0
);

INSERT INTO @tbl_Resul(TtlNOfCmpSaleSpanFirst, TtlNOfCmpSaleSpanSecond, TtlNOfCmpSaleSpanThird, TtlNOfCmpSale0to3, TtlNOfCmpSale4to6, TtlNOfCmpSale7to12) VALUES
    (null, null, null, 9505, 85, 415)
    , (null, null, null, 850, 700, 15)
    , (null, null, null, 650, 20, 4)
    , (null, null, null, 650, 20, 0);

DECLARE @Declining FLOAT = 0.90;
DECLARE @StableStart SMALLINT = 90;
DECLARE @StableEnd SMALLINT = 110;
DECLARE @Increasing SMALLINT = 110;

    输出：

    RowID   TtlNOfCmpSale7to12  TtlNOfCmpSale4to6   TtlNOfCmpSale0to3   TtlNOfCmpSaleSpanFirst  TtlNOfCmpSaleSpanSecond TtlNOfCmpSaleSpanThird
1       415                 85                  9505                2                       0                       NULL
2       15                  700                 850                 1                       NULL                    NULL
3       4                   20                  650                 NULL                    NULL                    0
4       0                   20                  650                 NULL                    NULL                    NULL
    • 3

相关问题