How do I use properly CASE..WHEN in MySQL

Here is a demo query, notice it is very simple, Fetches only where base_price is 0, And still, it chooses the condition 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_price is decimal(8,0)

When run this on my DB, I get:

3 0
3 0
3 0
3 0
3 0

179163 次浏览
小开
最佳答案

Remove the course_enrollment_settings.base_price immediately after CASE:

SELECT
CASE
WHEN course_enrollment_settings.base_price = 0      THEN 1
...
END

CASE has two different forms, as detailed in the manual. Here, you want the second form since you're using search conditions.

小开

CASE course_enrollment_settings.base_price is wrong here, it should be just 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

小开 
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
小开

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).

However, you've put an entire condition as when_value, leading to something like:

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:

if (case_value == FALSE)

FALSE converts to 0 and, through the resulting full expression if (case_value == 0) you can now see why the third condition fires.

What you're supposed to do

Drop the first course_enrollment_settings so that there's no case_value, causing MySQL to know that you intend to use the second variant of 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.

Also, please read the documentation for features that you use.

小开

I think part of it is that you're stating the value you're selecting after CASE, and then using WHEN x = y syntax afterward, which is a combination of two different methods of using CASE. It should either be

CASE X
WHEN a THEN ...
WHEN b THEN ...

or

CASE
WHEN x = a THEN ...
WHEN x = b THEN ...
小开 
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 

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

提交答案