如何在 MySQL 中正确使用 CASE..WHEN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9588015/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I use properly CASE..WHEN in MySQL
提问by Itay Moav -Malimovka
Here is a demo query, notice it is very simple, Fetches only where base_price is 0, And still, it chooses the condition 3:
这是一个演示查询，注意它非常简单，只在 base_price 为 0 的地方获取，并且仍然选择条件 3：
SELECT
CASE course_enrollment_settings.base_price
WHEN course_enrollment_settings.base_price = 0 THEN 1
WHEN course_enrollment_settings.base_price<101 THEN 2
WHEN course_enrollment_settings.base_price>100 AND
course_enrollment_settings.base_price<201 THEN 3
ELSE 6
END AS 'calc_base_price',
course_enrollment_settings.base_price
FROM
course_enrollment_settings
WHERE course_enrollment_settings.base_price = 0
base_priceis decimal(8,0)
base_price是 decimal(8,0)
When run this on my DB, I get:
在我的数据库上运行它时，我得到：
3 0
3 0
3 0
3 0
3 0
3 0
3 0
3 0
3 0
3 0
采纳答案by NPE
Remove the course_enrollment_settings.base_priceimmediately after CASE:
course_enrollment_settings.base_price之后立即删除CASE：
SELECT
CASE
WHEN course_enrollment_settings.base_price = 0 THEN 1
...
END
CASEhas two different forms, as detailed in the manual. Here, you want the second form since you're using search conditions.
CASE有两种不同的形式，详见手册。在这里，您需要第二种形式，因为您使用的是搜索条件。
回答by kapil das
CASE case_value
WHEN when_value THEN statements
[WHEN when_value THEN statements]
ELSE statements
END
Or:
或者：
CASE
WHEN <search_condition> THEN statements
[WHEN <search_condition> THEN statements]
ELSE statements
END
here CASE is an expression in 2nd scenario search_condition will evaluate and if no search_condition is equal then execute else
这里 CASE 是第二种场景中的表达式 search_condition 将评估，如果没有 search_condition 相等，则执行 else
SELECT
CASE course_enrollment_settings.base_price
WHEN course_enrollment_settings.base_price = 0 THEN 1
should be
应该
SELECT
CASE
WHEN course_enrollment_settings.base_price = 0 THEN 1
回答by a1ex07
CASE course_enrollment_settings.base_priceis wrong here, it should be just CASE
CASE course_enrollment_settings.base_price错在这里，应该只是 CASE
SELECT
CASE
WHEN course_enrollment_settings.base_price = 0 THEN 1
WHEN course_enrollment_settings.base_price<101 THEN 2
WHEN course_enrollment_settings.base_price>100 AND
course_enrollment_settings.base_price<201 THEN 3
ELSE 6
END AS 'calc_base_price',
course_enrollment_settings.base_price
FROM
course_enrollment_settings
WHERE course_enrollment_settings.base_price = 0
Some explanations. Your original query will be executed as :
一些解释。您的原始查询将执行为：
SELECT
CASE 0
WHEN 0=0 THEN 1 -- condition evaluates to 1, then 0 (from CASE 0)compares to 1 - false
WHEN 0<1 THEN 2 -- condition evaluates to 1,then 0 (from CASE 0)compares to 1 - false
WHEN 0>100 and 0<201 THEN 3 -- evaluates to 0 ,then 0 (from CASE 0)compares to 0 - true
ELSE 6, ...
it's why you always get 3
这就是为什么你总是得到 3
回答by Teja
SELECT
CASE
WHEN course_enrollment_settings.base_price = 0 THEN 1
WHEN course_enrollment_settings.base_price>0 AND
course_enrollment_settings.base_price<=100 THEN 2
WHEN course_enrollment_settings.base_price>100 AND
course_enrollment_settings.base_price<201 THEN 3
ELSE 6
END AS 'calc_base_price',
course_enrollment_settings.base_price
FROM
course_enrollment_settings
WHERE course_enrollment_settings.base_price = 0
回答by Lightness Races in Orbit
There are two variants of CASE, and you're not using the one that you think you are.
What you're doing
你在做什么
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Each condition is loosely equivalent to a if (case_value == when_value)(pseudo-code).
每个条件大致相当于一个if (case_value == when_value)（伪代码）。
However, you've put an entire condition as when_value, leading to something like:
但是，您已将整个条件设置为when_value，从而导致以下结果：
if (case_value == (case_value > 100))
Now, (case_value > 100)evaluates to FALSE, and is the only one of your conditions to do so. So, now you have:
现在，(case_value > 100)计算为FALSE，并且是您这样做的唯一条件之一。所以，现在你有：
if (case_value == FALSE)
FALSEconverts to 0and, through the resulting full expression if (case_value == 0)you can now see why the third condition fires.
FALSE转换为0and，通过生成的完整表达式，if (case_value == 0)您现在可以看到为什么会触发第三个条件。
What you're supposed to do
你应该做什么
Drop the first course_enrollment_settingsso that there's no case_value, causing MySQL to know that you intend to use the second variant of CASE:
删除第一个course_enrollment_settings以便没有case_value，使 MySQL 知道您打算使用 的第二个变体CASE：
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
Now you can provide your full conditionals as search_condition.
现在，您可以将完整的条件提供为search_condition.
Also, please read the documentation for features that you use.
另外，请阅读有关您使用的功能的文档。
回答by Kaji
I think part of it is that you're stating the value you're selecting after CASE, and then using WHEN x = ysyntax afterward, which is a combination of two different methods of using CASE. It should either be
我认为部分原因是您在声明之后选择的值CASE，然后使用WHEN x = y语法，这是两种不同使用方法的组合CASE。它应该是
CASE X
WHEN a THEN ...
WHEN b THEN ...
or
或者
CASE
WHEN x = a THEN ...
WHEN x = b THEN ...
