조건문 및 SUBSTR로 문제 쉽게 풀기

SQL

취업 스터디 모임

 

 

https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=true

The PADS | HackerRank

select concat(name,
              case when occupation = "actor" then '(A)'
                   when occupation = 'doctor' then '(D)'
                   when occupation = 'professor' then '(P)'
                   when occupation = 'singer' then '(S)' end 1) d
from occupations
union
select concat("there are a total of", count(*), occupation)
from occupations
group by occupations

윗부분 end 뒤의 1때문에 작동 안 됐음...

 

좀 더 생각해보고 답을 봐보니 위에 적은 답이 사소한 걸로 틀렸을 뿐만 아니라, 괜히 길게 썼다는 것을 깨닫게 됨. 즉 어차피 첫 글자만 따면 되는데 뭣하러 case when 을 사용했을까

ㅎㅎ

 

select concat(name, '(', substr(occupation, 1, 1), ')') txt
from occupations
union
(
select concat("There are a total of ", count(*), ' ', lower(occupation), 's.') txt
from occupations
group by occupation
)
order by txt