如何在TSQL Update语句中计算值？ [英] How can I compute values in a TSQL Update statement?

问题描述

我想创建三个临时表，然后像这样组合它们的内容：

I want to create three temp tables and then combine their contents like so:

CREATE TABLE #TEMP1
MEMBERITEMCODE VARCHAR(25),
WEEK1USAGE VARCHAR(25),
WEEK1PRICE VARCHAR(25);

INSERT INTO #TEMP1 (MEMBERITEMCODE, WEEK1USAGE, WEEK1PRICE)
SELECT MEMBERITEMCODE, SUM(QTYSHIPPED), PRICE 
FROM INVOICEDETAIL 
WHERE UNIT=@UNIT AND INVOICEDATE BETWEEN @BEGDATE AND @WEEK1END
GROUP BY MEMBERITEMCODE, PRICE

CREATE TABLE #TEMP2
MEMBERITEMCODE VARCHAR(25),
WEEK2USAGE VARCHAR(25),
WEEK2PRICE VARCHAR(25);

INSERT INTO #TEMP2 (MEMBERITEMCODE, WEEK2USAGE, WEEK2PRICE)
SELECT MEMBERITEMCODE, SUM(QTYSHIPPED), PRICE 
FROM INVOICEDETAIL 
WHERE UNIT=@UNIT AND INVOICEDATE BETWEEN @WEEK2BEGIN AND @ENDDATE
GROUP BY MEMBERITEMCODE, PRICE

CREATE TABLE #TEMP3
MEMBERITEMCODE VARCHAR(25),
DESCRIPTION VARCHAR(200),
THIS VARCHAR(25),
THAT VARCHAR(25),
THEOTHERTHING VARCHAR(25);

INSERT INTO #TEMP3 (MEMBERITEMCODE, DESCRIPTION, THIS, THAT, THEOTHERTHING)
SELECT MEMBERITEMCODE, DESCRIPTION, THIS, THAT, THEOTHERTHING 
FROM INVOICEDETAIL 
WHERE UNIT=@UNIT AND INVOICEDATE BETWEEN @BEGDATE AND @ENDDATE

CREATE TABLE #TEMPCOMBINED
MEMBERITEMCODE VARCHAR(25),
DESCRIPTION VARCHAR(200),
THIS VARCHAR(25),
THAT VARCHAR(25),
THEOTHERTHING VARCHAR(25),
WEEK1USAGE VARCHAR(25),
WEEK1PRICE VARCHAR(25);
WEEK2USAGE VARCHAR(25),
WEEK2PRICE VARCHAR(25),
USAGEVARIANCE VARCHAR(25),
PRICEVARIANCE VARCHAR(25),
PRICEVARIANCEPERCENTAGE VARCHAR(25);

INSERT INTO #TEMPCOMBINED (MEMBERITEMCODE, DESCRIPTION, THIS, THAT, THEOTHERTHING, WEEK1USAGE, WEEK1PRICE, WEEK2USAGE, WEEK2PRICE, USAGEVARIANCE,
PRICEVARIANCE, PRICEVARIANCEPERCENTAGE)
SELECT T1.MEMBERITEMCODE, T3.DESCRIPTION, T3.THIS, T3.THAT, T3.THEOTHERTHING, T1.WEEK1USAGE, T1.WEEK1PRICE, T2.WEEK2USAGE, T2.WEEK2PRICE, NULL,  NULL,  NULL
FROM #TEMP1 T1
LEFT JOIN #TEMP2 T2 ON T1.MEMBERITEMCODE = T2.MEMBERITEMCODE
LEFT JOIN #TEMP3 T3 ON T1.MEMBERITEMCODE = T3.MEMBERITEMCODE

现在，我想替换计算字段中的占位符Null，但不知道该怎么做。到目前为止，我最好的主意是：

Now I want to replace the placeholder Nulls in the calculated fields, but don't know how to do that. My best idea so far is:

UPDATE #TEMPCOMBINED
SET USAGEVARIANCE = WEEK2USAGE - WEEK1USAGE,
PRICEVARIANCE = WEEK2PRICE - WEEK1PRICE,
PRICEVARIANCEPERCENTAGE = (WEEK2PRICE - WEEK1PRICE) / WEEK1PRICE

更好的方法（例如，一种实际可行的方法）？

Is there a better way (one that actually works, for instance)?

推荐答案

使用计算列：

CREATE TABLE #TEMPCOMBINED(
  MEMBERITEMCODE VARCHAR(25),
  DESCRIPTION VARCHAR(200),
  THIS VARCHAR(25),
  THAT VARCHAR(25),
  THEOTHERTHING VARCHAR(25),
  WEEK1USAGE DECIMAL(18,10),
  WEEK1PRICE DECIMAL(18,10),
  WEEK2USAGE DECIMAL(18,10),
  WEEK2PRICE DECIMAL(18,10),
  USAGEVARIANCE   AS WEEK2USAGE - WEEK1USAGE,
  PRICEVARIANCE   AS WEEK2PRICE - WEEK1PRICE,
  PRICEVARIANCEPERCENTAGE  AS (WEEK2PRICE - WEEK1PRICE) / WEEK1PRICE
);

LiveDemo

并在 INSERT 语句中跳过它们：

INSERT INTO #TEMPCOMBINED (MEMBERITEMCODE, DESCRIPTION, THIS, THAT, THEOTHERTHING, WEEK1USAGE, WEEK1PRICE, WEEK2USAGE, WEEK2PRICE)
SELECT T1.MEMBERITEMCODE, T3.DESCRIPTION, T3.THIS, T3.THAT, T3.THEOTHERTHING, T1.WEEK1USAGE, T1.WEEK1PRICE, T2.WEEK2USAGE, T2.WEEK2PRICE
FROM #TEMP1 T1
LEFT JOIN #TEMP2 T2 ON T1.MEMBERITEMCODE = T2.MEMBERITEMCODE
LEFT JOIN #TEMP3 T3 ON T1.MEMBERITEMCODE = T3.MEMBERITEMCODE

警告：

除以 WEEK1PRICE ，因此您可以添加
（WEEK2PRICE-WEEK1PRICE）/ NULLIF（WEEK1PRICE，0）以避免数学异常，或添加 CHECK 约束以确保 WEEK1PRICE 不为0。

Division by WEEK1PRICE so you could add (WEEK2PRICE - WEEK1PRICE) / NULLIF(WEEK1PRICE,0) to avoid Math exception or add CHECK constraint to ensure that WEEK1PRICE is not 0.

这篇关于如何在TSQL Update语句中计算值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！