本文介绍了使用“结果表达式"从WHERE子句中的CASE表达式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是否可以通过使用WHERE
子句中的CASE
表达式的结果表达式(weight_class)来过滤SQL Server中的记录?我无法使它正常工作,因为出现错误消息:
Is it possible to filter records in SQL Server by using a result expression (weight_class) from a CASE
expression in the WHERE
clause? I can't get it to work because I get an error saying:
代码:
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'
推荐答案
SQL Server需要表别名:
SQL-Server requires a table alias:
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'
这篇关于使用“结果表达式"从WHERE子句中的CASE表达式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!