使用“结果表达式”来自 WHERE 子句中的 CASE 表达式

Question

是否可以通过使用 WHERE 子句中的 CASE 表达式的结果表达式 (weight_class) 来过滤 SQL Server 中的记录？我无法让它工作，因为我收到一条错误消息：

列名“weight_class”无效

代码：

SELECT 
    first_name, last_name, 
    weight_class = CASE
                      WHEN weight < 172 THEN 'Welterweight'
                      WHEN weight <= 192 THEN 'Middleweight'
                      WHEN weight <= 214 THEN 'Light heavyweight'
                      WHEN weight <= 220 THEN 'Cruiserweight'
                      ELSE 'Heavyweight'
                   END
FROM 
    athletes

这就是我想要做的：

SELECT 
    first_name, last_name, 
    weight_class = CASE
                      WHEN weight < 172 THEN 'Welterweight'
                      WHEN weight <= 192 THEN 'Middleweight'
                      WHEN weight <= 214 THEN 'Light heavyweight'
                      WHEN weight <= 220 THEN 'Cruiserweight'
                      ELSE 'Heavyweight'
                   END
FROM 
    athletes
WHERE 
    weight_class = 'Cruiserweight'

Answer 1

SQL-Server 需要表别名：

SELECT * from
 (
 SELECT first_name, last_name, 
        CASE
 WHEN weight<172 THEN 'Welterweight'
 WHEN weight<=192 THEN 'Middleweight'
 WHEN weight<=214 THEN 'Light heavyweight'
 WHEN weight<=220 THEN 'Cruiserweight'
  ELSE 'Heavyweight'
  END AS weight_class 
  FROM athletes
 ) as t
 WHERE weight_class = 'Cruiserweight'

Answer 2

 SELECT * from
 (
 SELECT first_name, last_name, 
        CASE
 WHEN weight<172 THEN 'Welterweight'
 WHEN weight<=192 THEN 'Middleweight'
 WHEN weight<=214 THEN 'Light heavyweight'
 WHEN weight<=220 THEN 'Cruiserweight'
  ELSE 'Heavyweight'
  END AS weight_class 
  FROM athletes
 ) t
 WHERE weight_class = 'Cruiserweight'

Answer 3

我认为你必须使用 HAVING 而不是 WHERE：

SELECT first_name, last_name, weight_class =
CASE
WHEN weight<172 THEN 'Welterweight'
WHEN weight<=192 THEN 'Middleweight'
WHEN weight<=214 THEN 'Light heavyweight'
WHEN weight<=220 THEN 'Cruiserweight'
ELSE 'Heavyweight'
END
FROM athletes
HAVING weight_class = 'Cruiserweight'

编辑：

顺便说一句，你为什么不直接使用：

SELECT first_name, last_name, weight_class =
CASE
WHEN weight<172 THEN 'Welterweight'
WHEN weight<=192 THEN 'Middleweight'
WHEN weight<=214 THEN 'Light heavyweight'
WHEN weight<=220 THEN 'Cruiserweight'
ELSE 'Heavyweight'
END
FROM athletes
WHERE weight > 214 AND weight <= 200