MySQL - SQL문 CASE WHEN THEN ELSE END

SQL 문에서도 개발언어처럼 조건문이 사용 가능하다.

CASE 값

WHEN 조건

THEN 참일때값

WHEN 조건

THEN 참일때값

ELSE 거짓일때값

END

예)

SELECT CASE (A.STATUS_CNT * 100 / B.LOCATION_CNT) WHEN 100 THEN 0 ELSE 1 END result,

CASE (C.ALARM_CNT * 100 / B.LOCATION_CNT) WHEN 100 THEN 0 ELSE 1 END result2

FROM

(SELECT COUNT(*) STATUS_CNT FROM tb_hms_metrics_map WHERE location_id='2' AND status='0') A,

(SELECT COUNT(*) LOCATION_CNT FROM tb_hms_metrics_map WHERE location_id='2') B,

(SELECT COUNT(*) ALARM_CNT FROM tb_hms_metrics_map WHERE location_id='2' AND alarm_status='0') C;

결과

result	result2
1	0