SQL基于CASE生成派生列后CONCAT引用报无效列名问题求解

首先要明确SQL的SELECT子句内部的执行优先级：
数据库会先完成SELECT列表里所有的原始字段读取、表达式计算，最后才会给计算出来的衍生字段分配你定义的别名。
也就是说，在同一个SELECT层级里，你是没办法直接引用当前行刚定义的别名的。PRODUCT_FAMILY 不是两张物理表上原生存在的字段，是你写CASE WHEN临时算出来的衍生值，所以：

• 直接在CONCAT里写PRODUCT_FAMILY会找不到字段
• 你之前尝试给它加表名前缀，写成V_SF_OPPORTUNITY_ITEMS.PRODUCT_FAMILY也没用——因为数据库带表名匹配的时候，只会遍历视图里的原生字段列表，根本没有这个临时衍生的字段，自然抛出Invalid column name 'Product Family'的错误。

按改动量从小到大排序，你可以任选一种：

方法1：避免重复引用别名，把计算逻辑直接嵌入CONCAT #

最直接的改法，把CASE WHEN的完整逻辑原封不动放到CONCAT的参数位，不要依赖同层的别名：

select 
  V_QUOTE_CASE_OPP_FROM2020.SR_NUM,
  V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY,
  case 
    when V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY like '%Tax%' then 'Tax'
    when V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY like '%Practice Manager%' then 'Practice Manager'
    when V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY like '%Compliance%' then 'Compliance'
    else V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY end as PRODUCT_FAMILY,
  -- 把case逻辑直接嵌入concat
  CONCAT(
    V_QUOTE_CASE_OPP_FROM2020.SR_NUM,
    case 
      when V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY like '%Tax%' then 'Tax'
      when V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY like '%Practice Manager%' then 'Practice Manager'
      when V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY like '%Compliance%' then 'Compliance'
      else V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY end
  ) as RN_PRODFAM
from [COMMOPSDB].[dbo].[V_QUOTE_CASE_OPP_FROM2020]
LEFT JOIN [COMMOPSDB].[dbo].[V_SF_OPPORTUNITY_ITEMS]
  -- 必须补join关联条件，不然会生成笛卡尔积
  ON V_QUOTE_CASE_OPP_FROM2020.你的关联键 = V_SF_OPPORTUNITY_ITEMS.你的关联键

注意：这种写法的缺点是重复写了两遍相同的CASE逻辑，后续如果要调整规则要改两处，容易漏改。

方法2：用CTE/子查询固化衍生列，在外层做拼接 #

把第一层计算PRODUCT_FAMILY的逻辑放到CTE或者子查询里，等别名固化成正式列之后，外层就可以随便引用了：

WITH base_calc AS (
  select 
    V_QUOTE_CASE_OPP_FROM2020.SR_NUM,
    V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY,
    case 
      when V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY like '%Tax%' then 'Tax'
      when V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY like '%Practice Manager%' then 'Practice Manager'
      when V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY like '%Compliance%' then 'Compliance'
      else V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY end as PRODUCT_FAMILY
  from [COMMOPSDB].[dbo].[V_QUOTE_CASE_OPP_FROM2020]
  LEFT JOIN [COMMOPSDB].[dbo].[V_SF_OPPORTUNITY_ITEMS]
    ON V_QUOTE_CASE_OPP_FROM2020.你的关联键 = V_SF_OPPORTUNITY_ITEMS.你的关联键
)
SELECT 
  SR_NUM,
  PROD_FAMILY,
  PRODUCT_FAMILY,
  CONCAT(SR_NUM, PRODUCT_FAMILY) as RN_PRODFAM
FROM base_calc

方法3：用CROSS APPLY简化重复逻辑（SQL Server专属） #

如果不想写重复的CASE逻辑，又不想套两层查询，可以用CROSS APPLY先把CASE计算的结果单独取出来，后面直接引用就行：

select 
  V_QUOTE_CASE_OPP_FROM2020.SR_NUM,
  V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY,
  pf_calc.PRODUCT_FAMILY,
  CONCAT(V_QUOTE_CASE_OPP_FROM2020.SR_NUM, pf_calc.PRODUCT_FAMILY) as RN_PRODFAM
from [COMMOPSDB].[dbo].[V_QUOTE_CASE_OPP_FROM2020]
LEFT JOIN [COMMOPSDB].[dbo].[V_SF_OPPORTUNITY_ITEMS]
  ON V_QUOTE_CASE_OPP_FROM2020.你的关联键 = V_SF_OPPORTUNITY_ITEMS.你的关联键
-- 单独计算PRODUCT_FAMILY
CROSS APPLY (
  SELECT case 
    when V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY like '%Tax%' then 'Tax'
    when V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY like '%Practice Manager%' then 'Practice Manager'
    when V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY like '%Compliance%' then 'Compliance'
    else V_SF_OPPORTUNITY_ITEMS.PROD_FAMILY end as PRODUCT_FAMILY
) pf_calc

• 原SELECT开头有一个孤立的逗号（select ,V_QUOTE_CASE_OPP_FROM2020.SR_NUM），这个在SQL Server里会直接抛语法错误
• 原LEFT JOIN没有写ON关联条件，执行后会生成两个表的笛卡尔积，数据量会按行数乘积放大，结果完全不符合业务预期，一定要补上两个表的匹配关联键
• 原CASE WHEN里的PROD_FAMILY没有加表名限定，如果后续两个视图新增了同名字段，会抛出字段歧义的错误，建议所有引用的字段都补全表名前缀

内容的提问来源于stack exchange，提问作者Biggusdoggus