可以在另一个计算列中使用计算列吗?

2021-09-10 00:00:00 sql tsql sql-server 
SELECT ExchangeRatePrice = CASE pp.Price
     WHEN NULL THEN 0
       ELSE (CASE WHEN c.CurrencyId = 1 THEN pp.Price 
       ELSE CONVERT(DECIMAL(9, 2), (pp.Price * c.ExchangeRate)) END)
     END , 

     price as OriginalPriceInDB,
     10 * Price as CalculatedPrice,
    c.currencyid as Currency

FROM ProductPrice pp, currency c

替代文字 http://img682.imageshack.us/img682/3692/exchangerate.png

我希望在CalculatedPrice 中使用计算列(ExchangeRatePrice).我可以直接使用它吗?我需要再次转换它吗?

我使用 10 * Price 只是为了向您展示示例，因为如果我在这里使用ExchangeRatePrice"，它会给出错误列名无效"

I have used 10 * Price just to show you the example because if I use 'ExchangeRatePrice' here it will give error 'Invalid Column name'

推荐答案

在 SQL 中引用 pre-calculate a value 通常意味着在内部查询(AKA inline view)中执行计算:

To reference pre-calculate a value in SQL usually means performing the calculation in an inner query (AKA inline view):

SELECT x.exchangerateprice,
       x.OriginalPriceInDB,
       10 * x.exchangerateprice AS CalculatedPrice,
       x.currencyid
  FROM (SELECT CASE 
                 WHEN pp.price IS NULL THEN
                   0
                 WHEN c.CurrencyId = 1 THEN 
                   pp.Price
                 ELSE 
                   CONVERT(DECIMAL(9, 2), (pp.Price * c.ExchangeRate)) 
               END AS ExchangeRatePrice,
              price as OriginalPriceInDB,
              c.currencyid as Currency
         FROM PRODUCTPRICE pp, 
              CURRENCY c) x

这等效于使用 WITH 子句(在 SQL Server 2005+ 中可用) - 性能没有区别.

This is the equivalent to using the WITH clause (available in SQL Server 2005+) - there's no difference in performance.

你可以复制逻辑:

SELECT CASE 
         WHEN pp.price IS NULL THEN
           0
         WHEN c.CurrencyId = 1 THEN 
           pp.Price
         ELSE 
           CONVERT(DECIMAL(9, 2), (pp.Price * c.ExchangeRate)) 
       END AS ExchangeRatePrice,
       price as OriginalPriceInDB,
       CASE 
          WHEN pp.price IS NULL THEN
            0
          WHEN c.CurrencyId = 1 THEN 
            pp.Price
          ELSE 
            CONVERT(DECIMAL(9, 2), (pp.Price * c.ExchangeRate)) 
        END * 10 AS CalculatedPrice,
        c.currencyid as Currency
   FROM PRODUCTPRICE pp, 
        CURRENCY c

..但这意味着重复&有人不更新两个实例以保持同步的固有风险.

..but that means duplication & inherent risk of someone not updating both instances to keep in sync.

相关文章