sql - 稍后在查询中使用 'AS ColumnName' 的值

Question

我正在创建一个存储过程，我需要使用之前设置的值。我不太擅长解释这一点，所以我将使用一个例子:

  CASE 
     WHEN ((select top 1 stuksweergeven from componenten
            where componentid = componentlink.componentid) = 1) and
          ((select opbrengstperkilo from componenten
            where componentid = componentlink.componentid) <> 0) THEN 
        amount1 * (select opbrengstperkilo from componenten
                   where componentid = componentlink.componentid)
     ELSE 
        amount1
  END AS Total,
Amount1 * Total *(SELECT dbo.SelectReceptenLinkGewicht(Componentid,0)) AS TotalWeight

我做了一个CASE，其结果为Total。之后我想使用 Total 来计算 TotalWeight。

抱歉我的英语不好。

Answer 1

问题是，SELECT 列表中的所有表达式都以一次性 方式求值。这就是为什么您需要复制代码。但是您可以为其创建子查询或cte，例如:

with cte as(
             select Amount1,
                    ComponentID,
                    CASE 
                       WHEN ((select top 1 stuksweergeven from componenten where componentid = componentlink.componentid) = 1) and  ((select opbrengstperkilo from componenten where componentid = componentlink.componentid) <> 0) 
                       THEN amount1 * (select opbrengstperkilo from componenten where componentid = componentlink.componentid)
                       ELSE amount1
                    END AS Total
             from SomeTable)

select Total,
       Amount1 * Total *(SELECT dbo.SelectReceptenLinkGewicht(Componentid,0)) AS TotalWeight
from cte

或者:

select Total,
       Amount1 * Total *(SELECT dbo.SelectReceptenLinkGewicht(Componentid,0)) AS TotalWeight
from (
             select Amount1,
                    ComponentID,
                    CASE 
                       WHEN ((select top 1 stuksweergeven from componenten where componentid = componentlink.componentid) = 1) and  ((select opbrengstperkilo from componenten where componentid = componentlink.componentid) <> 0) 
                       THEN amount1 * (select opbrengstperkilo from componenten where componentid = componentlink.componentid)
                       ELSE amount1
                    END AS Total
             from SomeTable) t