mysql 里使用case,如何在MySQL中正确使用CASE..WHEN

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

解决方案

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.