sql-server - 计算新变量

Question

我有以下代码，并希望创建一个计算 FCT_MAX_CHARGES 和 CPT_MAX_CHARGES 变量之间差异的结果列。我正在寻找可以在下表中生成 CPT_FCT DELTA 列的代码。

USE EMTCQIData

SELECT CONC.[ID Number] as FIN, CONC.table,

   CASE
          WHEN CONC.table like '%level 5%' THEN '99285' 
          WHEN CONC.table like '%level 4%' THEN '99284'

   END FCT_MAX,

   CASE
          WHEN CONC.table like '%level 5%' THEN $200 
          WHEN CONC.table like '%level 4%' THEN $100

   END FCT_MAX_CHARGES,

   CPT.CPT,

   CASE
          WHEN CPT.CPT like '99285%' THEN $200
          WHEN CPT.CPT like '99284' THEN $100

   END CPT_MAX_CHARGES

FROM CPT_MASTER as CPT 


                          Expected Results
ID | FCT_MAX| FCT_MAX_CHARGGES| CPT   | CPT_MAX_CHARGES| CPT_FCT DELTA
 1    99284     200             99284       100              100

标签： sql-server

Answer 1

您不能在同一范围内引用另一个表达式中的别名，但解决此问题的一种方法是在另一个级别执行第一组表达式，例如 CTE 或子查询：

;WITH cte AS
(
  SELECT 
    CONC.[ID Number] as FIN, 
    CONC.table,
    CASE
      WHEN CONC.table like '%level 5%' THEN '99285' 
      WHEN CONC.table like '%level 4%' THEN '99284'
    END AS FCT_MAX,
    CASE
      WHEN CONC.table like '%level 5%' THEN $200 
      WHEN CONC.table like '%level 4%' THEN $100
   END AS FCT_MAX_CHARGES,
   CPT.CPT,
   CASE
     WHEN CPT.CPT like '99285%' THEN $200
     WHEN CPT.CPT like '99284' THEN $100
   END AS CPT_MAX_CHARGES
   FROM CPT_MASTER as CPT
)
SELECT 
  FIN,
  [table],
  FCT_MAX,
  CPT,
  FCT_MAX_CHARGES,
  CPT_MAX_CHARGES,
  CPT_FCT DELTA = FCT_MAX_CHARGES - CPT_MAX_CHARGES
FROM cte;