引用 SELECT 列表中其他地方的别名

2022-01-16 00:00:00 sql sql-server sql-server-2008-r2

我有以下疑问:

    select FirstName, LastName,
    Case
     When LastName = 'Jones'
     then 'N/A'
    End as Other,
    Case 
      When Other is not null 
      then 1 
    else 0 as Flag

Flag 值取决于 Other，但由于 Other 是别名，因此 Flag 字段无法识别 Other.

The Flag value depends on Other but as Other is an alias, the Flag field does not recognize Other.

我想，我可以在选择中使用选择.Flag 是否有更好的方法来识别其他别名列?

I guess, I can use a select within a select. Is there a better way for Flag to recognize the Other alias column?

推荐答案

由于解析查询的方式，您不能引用 SELECT 和 ORDER BY 之外的别名.典型的解决方法是将其隐藏在派生表中:

You can't refer to an alias outside of SELECT and ORDER BY because of the way a query is parsed. Typical workaround is to bury it in a derived table:

SELECT 
  FirstName, LastName, Other, 
  Flag = CASE WHEN Other IS NOT NULL THEN 1 ELSE 0 END
FROM 
(
  SELECT FirstName, LastName,
    CASE WHEN LastName = 'Jones'
     THEN 'N/A'
    END AS Other
  FROM dbo.table_name
) AS x;

相关文章