[MySql] CASE 구문

Oracle에서 사용하는 CASE구문과 별차이 없다.

DECODE는 사용하지 못한다.

샘플데이터.

EMPLOYEE_ID|EMPLOYEE_NAME|JOB      |MANAGER_ID|HIREDATE           |SALARY|COMMISSION|DEPARTMENT_ID|
-----------|-------------|---------|----------|-------------------|------|----------|-------------|
       7521|WARD         |SALESMAN |      7698|1981-02-22 00:00:00|  1250|       500|           30|
       7566|JONES        |MANAGER  |      7839|1981-04-02 00:00:00|  2975|          |           20|
       7654|MARTIN       |SALESMAN |      7698|1981-09-28 00:00:00|  1250|      1400|           30|
       7698|BLAKE        |MANAGER  |      7839|1981-05-01 00:00:00|  2850|          |           30|
       7782|CLARK        |MANAGER  |      7839|1981-06-09 00:00:00|  2450|          |           10|
       7788|SCOTT        |ANALYST  |      7566|0087-02-13 00:00:00|  3000|          |           20|
       7839|KING         |PRESIDENT|          |1981-11-17 00:00:00|  5000|          |           10|
       7369|SMITH        |CLERK    |      7902|1980-12-17 00:00:00|   800|          |           20|
       7499|ALLEN        |SALESMAN |      7698|1981-02-20 00:00:00|  1600|       300|           30|
       7844|TURNER       |SALESMAN |      7698|1981-09-08 00:00:00|  1500|         0|           30|
       7876|ADAMS        |CLERK    |      7788|0087-02-13 00:00:00|  1100|          |           20|
       7900|JAMES        |CLERK    |      7698|1981-12-03 00:00:00|   950|          |           30|
       7902|FORD         |ANALYST  |      7566|1981-12-03 00:00:00|  3000|          |           20|
       7934|MILLER       |CLERK    |      7782|1982-01-23 00:00:00|  1300|          |           10|

CASE.

CASE { simple_case_expression
     | searched_case_expression
     }
     [ else_clause ]
     END

doc.

https://dev.mysql.com/doc/refman/5.7/en/case.html

사용방법.

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

OR.

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

예시.

급여가 2000 초과면 High

, 1000 이상이고 2000 이하면 Medium

, 1000 이하이면 Low인 직원 리스트를 구해보자.

SELECT EMPLOYEE_NAME
     , CASE 
         WHEN SALARY > 2000 THEN 'High'
         WHEN SALARY BETWEEN 1000 AND 2000 THEN 'Medium'
         WHEN SALARY < 1000 THEN 'Low'
        END AS GRADE
FROM EMPLOYEES

결과.

EMPLOYEE_NAME|GRADE |
-------------|------|
WARD         |Medium|
JONES        |High  |
MARTIN       |Medium|
BLAKE        |High  |
CLARK        |High  |
SCOTT        |High  |
KING         |High  |
SMITH        |Low   |
ALLEN        |Medium|
TURNER       |Medium|
ADAMS        |Medium|
JAMES        |Low   |
FORD         |High  |
MILLER       |Medium|

응용예시.

급여가 2000 초과면 High

, 1000 이상이고 2000 이하면 Medium

, 1000 이하이면 Low인 직원 수를 구해보자.

SELECT emp.GRADE
     , COUNT(emp.GRADE) AS CNT
FROM (
	SELECT CASE 
	         WHEN SALARY > 2000 THEN 'High'
	         WHEN SALARY BETWEEN 1000 AND 2000 THEN 'Medium'
	         WHEN SALARY < 1000 THEN 'Low'
	        END AS GRADE
	FROM EMPLOYEES
) emp
GROUP BY emp.GRADE;

결과.

GRADE |COUNT(GRADE)|
------|------------|
High  |           6|
Low   |           2|
Medium|           6|