Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글
Case Statements
이런 형식이다.
다른 프로그램 언어에서의 switch문과 비슷하다.
mysql> SELECT title, released_year,
CASE WHEN released_year>= 2000 THEN 'Modern Lit'
ELSE '20th Century Lit' END AS GENRE
FROM books;
+-----------------------------------------------------+---------------+------------------+
| title | released_year | GENRE |
+-----------------------------------------------------+---------------+------------------+
| The Namesake | 2003 | Modern Lit |
| Norse Mythology | 2016 | Modern Lit |
| Interpreter of Maladies | 1996 | 20th Century Lit |
| A Hologram for the King: A Novel | 2012 | Modern Lit |
| The Circle | 2013 | Modern Lit |
| The Amazing Adventures of Kavalier & Clay | 2000 | Modern Lit |
| Just Kids | 2010 | Modern Lit |
| A Heartbreaking Work of Staggering Genius | 2001 | Modern Lit |
| Coraline | 2003 | Modern Lit |
| What We Talk About When We Talk About Love: Stories | 1981 | 20th Century Lit |
| Where I'm Calling From: Selected Stories | 1989 | 20th Century Lit |
| White Noise | 1985 | 20th Century Lit |
| Cannery Row | 1945 | 20th Century Lit |
| Oblivion: Stories | 2004 | Modern Lit |
| Consider the Lobster | 2005 | Modern Lit |
| 10% Happier | 2014 | Modern Lit |
| fake_book | 2001 | Modern Lit |
| Lincoln In The Bardo | 2017 | Modern Lit |
+-----------------------------------------------------+---------------+------------------+
18 rows in set (0.00 sec)
END 까지가 하나의 문법이고, 뒤에 AS는 생략해줘도 되지만 그럼 칼럼이름이 너무 길게나온다.
mysql> SELECT title, released_year, CASE WHEN released_year>= 2000 THEN 'Modern Lit' ELSE '20th Century Lit' END FROM books;
+-----------------------------------------------------+---------------+------------------------------------------------------------------------------+
| title | released_year | CASE WHEN released_year>= 2000 THEN 'Modern Lit' ELSE '20th Century Lit' END |
+-----------------------------------------------------+---------------+------------------------------------------------------------------------------+
| The Namesake | 2003 | Modern Lit |
| Norse Mythology | 2016 | Modern Lit |
| Interpreter of Maladies | 1996 | 20th Century Lit |
| A Hologram for the King: A Novel | 2012 | Modern Lit |
| The Circle | 2013 | Modern Lit |
| The Amazing Adventures of Kavalier & Clay | 2000 | Modern Lit |
| Just Kids | 2010 | Modern Lit |
| A Heartbreaking Work of Staggering Genius | 2001 | Modern Lit |
| Coraline | 2003 | Modern Lit |
| What We Talk About When We Talk About Love: Stories | 1981 | 20th Century Lit |
| Where I'm Calling From: Selected Stories | 1989 | 20th Century Lit |
| White Noise | 1985 | 20th Century Lit |
| Cannery Row | 1945 | 20th Century Lit |
| Oblivion: Stories | 2004 | Modern Lit |
| Consider the Lobster | 2005 | Modern Lit |
| 10% Happier | 2014 | Modern Lit |
| fake_book | 2001 | Modern Lit |
| Lincoln In The Bardo | 2017 | Modern Lit |
+-----------------------------------------------------+---------------+------------------------------------------------------------------------------+
18 rows in set (0.00 sec)ELSE는 케이스에 하나도 걸리지 않았을 때 default밸류이다. ELSE를 적어주지 않으면 케이스에 걸리지 않는 값은 NULL로 표시된다.
mysql> SELECT title, released_year,
-> CASE
-> WHEN released_year >= 2000 THEN 'Modern Lit'
-> END
-> FROM books;
+-----------------------------------------------------+---------------+-------------------------------------------------------+
| title | released_year | CASE WHEN released_year >= 2000 THEN 'Modern Lit' END |
+-----------------------------------------------------+---------------+-------------------------------------------------------+
| The Namesake | 2003 | Modern Lit |
| Norse Mythology | 2016 | Modern Lit |
| Interpreter of Maladies | 1996 | NULL |
| A Hologram for the King: A Novel | 2012 | Modern Lit |
| The Circle | 2013 | Modern Lit |
| The Amazing Adventures of Kavalier & Clay | 2000 | Modern Lit |
| Just Kids | 2010 | Modern Lit |
| A Heartbreaking Work of Staggering Genius | 2001 | Modern Lit |
| Coraline | 2003 | Modern Lit |
| What We Talk About When We Talk About Love: Stories | 1981 | NULL |
| Where I'm Calling From: Selected Stories | 1989 | NULL |
| White Noise | 1985 | NULL |
| Cannery Row | 1945 | NULL |
| Oblivion: Stories | 2004 | Modern Lit |
| Consider the Lobster | 2005 | Modern Lit |
| 10% Happier | 2014 | Modern Lit |
| fake_book | 2001 | Modern Lit |
| Lincoln In The Bardo | 2017 | Modern Lit |
+-----------------------------------------------------+---------------+-------------------------------------------------------+
18 rows in set (0.00 sec)아래와 같이 WHEN을 여러개 써서 케이스를 여러개 써줄 수도 있다.
SELECT title, stock_quantity,
CASE
WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
ELSE '***'
END AS 'STOCK'
FROM books;
+-----------------------------------------------------+----------------+-------+
| title | stock_quantity | STOCK |
+-----------------------------------------------------+----------------+-------+
| The Namesake | 32 | * |
| Norse Mythology | 43 | * |
| Interpreter of Maladies | 97 | ** |
| A Hologram for the King: A Novel | 154 | *** |
| The Circle | 26 | * |
| The Amazing Adventures of Kavalier & Clay | 68 | ** |
| Just Kids | 55 | ** |
| A Heartbreaking Work of Staggering Genius | 104 | *** |
| Coraline | 100 | ** |
| What We Talk About When We Talk About Love: Stories | 23 | * |
| Where I'm Calling From: Selected Stories | 12 | * |
| White Noise | 49 | * |
| Cannery Row | 95 | ** |
| Oblivion: Stories | 172 | *** |
| Consider the Lobster | 92 | ** |
| 10% Happier | 29 | * |
| fake_book | 287 | *** |
| Lincoln In The Bardo | 1000 | *** |
+-----------------------------------------------------+----------------+-------+
18 rows in set (0.00 sec)물론 between을 사용하지 않고, 부등호를 사용해 표시해 줄 수도 있다.
SELECT title, stock_quantity,
CASE
WHEN stock_quantity <= 50 THEN '*'
WHEN stock_quantity <= 100 THEN '**'
ELSE '***'
END AS 'STOCK'
FROM books;
+-----------------------------------------------------+----------------+-------+
| title | stock_quantity | STOCK |
+-----------------------------------------------------+----------------+-------+
| The Namesake | 32 | * |
| Norse Mythology | 43 | * |
| Interpreter of Maladies | 97 | ** |
| A Hologram for the King: A Novel | 154 | *** |
| The Circle | 26 | * |
| The Amazing Adventures of Kavalier & Clay | 68 | ** |
| Just Kids | 55 | ** |
| A Heartbreaking Work of Staggering Genius | 104 | *** |
| Coraline | 100 | ** |
| What We Talk About When We Talk About Love: Stories | 23 | * |
| Where I'm Calling From: Selected Stories | 12 | * |
| White Noise | 49 | * |
| Cannery Row | 95 | ** |
| Oblivion: Stories | 172 | *** |
| Consider the Lobster | 92 | ** |
| 10% Happier | 29 | * |
| fake_book | 287 | *** |
| Lincoln In The Bardo | 1000 | *** |
+-----------------------------------------------------+----------------+-------+
18 rows in set (0.00 sec)
임재현입니다.