MySQL에서 CASE..WHEN을 올바르게 사용하는 방법

MySQL에서 CASE..WHEN을 올바르게 사용하는 방법

---

여기 데모 쿼리가 있습니다. 매우 간단합니다. 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_price 이다 decimal(8,0)

내 DB에서 실행하면 다음을 얻습니다.

3 0
3 0
3 0
3 0
3 0

---

course_enrollment_settings.base_price즉시 제거 CASE:

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

CASE설명서에 자세히 설명 된대로 두 가지 형식이 있습니다 . 여기에서는 검색 조건을 사용하고 있으므로 두 번째 양식이 필요합니다 .

---

CASE case_value
    WHEN when_value THEN statements
    [WHEN when_value THEN statements]
    ELSE statements
END 

또는:

CASE
WHEN <search_condition> THEN statements
[WHEN <search_condition> THEN statements] 
ELSE statements
END 

여기서 CASE는 두 번째 시나리오의 표현식입니다. search_condition이 평가되고 search_condition이 같지 않으면 else 실행

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

해야한다

SELECT
   CASE 
    WHEN course_enrollment_settings.base_price = 0      THEN 1

---

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 

몇 가지 설명. 원래 쿼리는 다음과 같이 실행됩니다.

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

그것이 당신이 항상 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 ...

참고URL : https://stackoverflow.com/questions/9588015/how-do-i-use-properly-case-when-in-mysql