MySQL - case문과 if함수 사용하기 / 조건문

INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)
VALUES
('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),
('Norse Mythology', 'Neil', 'Gaiman',2016, 43, 304),
('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),
('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),
('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),
('The Circle', 'Dave', 'Eggers', 2013, 26, 504),
('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),
('Just Kids', 'Patti', 'Smith', 2010, 55, 304),
('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),
('Coraline', 'Neil', 'Gaiman', 2003, 100, 208),
('What We Talk About When We Talk About Love: Stories', 'Raymond', 'Carver', 1981, 23, 176),
("Where I'm Calling From: Selected Stories", 'Raymond', 'Carver', 1989, 12, 526),
('White Noise', 'Don', 'DeLillo', 1985, 49, 320),
('Cannery Row', 'John', 'Steinbeck', 1945, 95, 181),
('Oblivion: Stories', 'David', 'Foster Wallace', 2004, 172, 329),
('Consider the Lobster', 'David', 'Foster Wallace', 2005, 92, 343),
('10% Happier', 'Dan', 'Harris', 2014, 29, 256),
('fake_book', 'Freida', 'Harris', 2001, 287, 428),
('Lincoln In the Bardo', 'George', 'Saunders', 2017, 156, 375);

1. case 문으로 처리하는 방법

-- 년도가 2000년 이상이면, 'Modern Book'이라고 하고,
-- 그렇지 않으면 '20th Book'이라고 새로운 Genre 컬럼을 만드세요.

요구사항은 다음과 같습니다.

select *,
	case
		when released_year >= 2000 then 'Modern Book' -- 년도가 2000년보다 크거나 같으면 'ModernBook'
        else '20th Book' -- 아니라면 '20th Book'
    end as Genre -- case문 종료, 컬럼이름은 Genre로 하겠다.
from books;

 

 

2. if 함수로 처리하는 방법

-- 년도가 2000년 이상이면, 'Modern Book'이라고 하고,
-- 그렇지 않으면 '20th Book'이라고 새로운 Genre 컬럼을 만드세요.

 

select * , if (released_year>=2000,'Modern Book','20th Book') as genre
from books;

 

 

예제) 아래 사진과 같이 만들어주세요.

 

위 요구사항을 만족시키기 위해 case문과 if함수 두 가지 사용하는 방법 모두 해보겠습니다.

 

 

1. case문 

select title,author_lname,	
	case
		when count(title) =1 then '1 book'
        else concat(count(title),' books')
	end as COUNT
from books
group by author_lname
order by author_lname;

2. if 함수

select title,author_lname,	
	if( count(title)=1,'1 book', concat(count(title),' books')) as COUNT
from books
group by author_lname
order by author_lname;