問題描述

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.

這篇關于可以在另一個計算列中使用計算列嗎?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！