SQL 기초27 CASE

# CASE

# 일종의 IF문 같은 원리라고 한다.

---

# 기본1

CASE

WHEN condition1 THEN result1

WHEN condition2 THEN result2

ELSE some_other_result

END

---

# 기본2

 

SELECT a

CASE WHEN a = 1 THEN ‘one’

WHEN a = 2 THEN ‘two’

ELSE ‘other’ AS label

END

FROM test;

---

# 예시1

 

SELECT customer_id,

CASE 

WHEN (customer_id <= 100) THEN 'Premium'

WHEN (customer_id BETWEEN 100 AND 200) THEN 'Plus'

ELSE 'Normal'

END AS customer_class

FROM customer

---

# 예시2

 

SELECT customer_id,

CASE customer_id

WHEN 2 THEN 'Winner'

WHEN 5 THEN 'Second Place'

ELSE 'Normal'

END AS raffle_results

FROM customer

---

# 예시3

 

SELECT rental_rate,

CASE rental_rate 

WHEN 0.99 THEN 1

ELSE 0

END

FROM film

---

# 예시4

 

SELECT

SUM(CASE rental_rate 

WHEN 0.99 THEN 1

ELSE 0

END) AS bargains,

SUM(CASE rental_rate

   WHEN 2.99 THEN 1

   ELSE 0

END) AS regular

FROM film

---

# 예시5

 

SELECT

SUM(CASE rental_rate 

WHEN 0.99 THEN 1

ELSE 0

END) AS bargains,

SUM(CASE rental_rate

   WHEN 2.99 THEN 1

   ELSE 0

END) AS regular,

SUM(CASE rental_rate

   WHEN 4.99 THEN 1

   ELSE 0

END) AS premium

FROM film

---

# 예시6

 

# 사실상 COUNT와 같은 개념으로 쓰였다.

# rating 컬럼에 R이라는 값이 있으면 1이라고 표기해주고, 나머지는 0

# 그리고 그 모든 것을 더했을때 값이니깐

 

SELECT

SUM(

CASE rating

WHEN 'R' THEN 1 ELSE 0

END

) AS r

FROM film

---

# 예시7

 

SELECT

SUM(

CASE rating

WHEN 'R' THEN 1 ELSE 0

END

) AS r,

 

SUM(

CASE rating

WHEN 'PG' THEN 1 ELSE 0

END

) AS pg,

 

SUM(

CASE rating

WHEN 'PG-13' THEN 1 ELSE 0

END

)AS pg13

 

FROM film