如何在MySQL中正确使用CASE..WHEN [英] How do I use properly CASE..WHEN in MySQL

问题描述

这是一个演示查询，请注意，它非常简单，仅在base_price为0的情况下提取， 仍然选择条件3:

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是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

推荐答案

在CASE之后立即删除course_enrollment_settings.base_price:

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

CASE具有两种不同的形式，如手册中所述.在这里，由于要使用搜索条件

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

这篇关于如何在MySQL中正确使用CASE..WHEN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！