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

小编典典

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

sql

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

无效的列名“ 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'

阅读 170

2021-04-14

共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'

2021-04-14