[해커랭크/SQL] 32. The PADS (concat, 문자열 합치기)

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

The PADS | HackerRank

 

---

 

🤔 문제 분석

1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).

 

2.Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:
where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.

💡 풀이

SELECT CONCAT(Name,"(",LEFT(Occupation,1),")") AS Name 
FROM Occupations 

UNION ALL 

SELECT CONCAT("There are a total of ",Count(Occupation)," ",Lower(Occupation),"s.") as Name 
FROM Occupations 
Group By Occupation 
Order By Name

 

**sub쿼리 활용(정답 아님)

SELECT CONCAT(Name, "(", SUBSTRING(Occupation, 1, 1), ")")
FROM (SELECT *
      FROM OCCUPATIONS 
      ORDER BY Name) AS sub1

UNION

SELECT CONCAT("There are a total of ", NUMBER, " ",OCCUPATION, "s.")
FROM(
    SELECT OCCUPATION, COUNT(*) AS NUMBER
    FROM OCCUPATIONS
    GROUP BY OCCUPATION
    ORDER BY NUMBER, OCCUPATION
) AS sub2

 

문자열 합치기: concat, concat_ws

union 사용시 각각의 테이블 정렬이 안된다.

이중으로 sub쿼리를 사용하면 된다고 해서..

복잡하긴 하지만 서브쿼리를 여러번 감싸줘서 제출했는데도 안된다..

아래 풀이는 왜 안되는지 꼭 확인하기