MySQL 해커랭크 | New Companies ( 테이블 조인, LENGTH, SUBSTRING_INDEX, CAST )
문제
https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true
New Companies | HackerRank
Find total number of employees.
www.hackerrank.com
[corporation hierarchy]
Founder → Lead_Manager → Senior_Manager → Manager → Employee
- print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, total number of employees
- order output by ascending company_code
- The tables may contain duplicate records
- The company_code is string, so the sorting should not be numeric.
For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.
테이블
Company | ![]() |
Lead_Manager | ![]() |
Senior_Manager | ![]() |
Manager | ![]() |
Employee | ![]() |
풀이과정
# 전체 테이블 조인
1 2 3 4 5 6 | SELECT * FROM Company c LEFT JOIN Lead_Manager lm ON c.company_code = lm.company_code LEFT JOIN Senior_Manager sm ON c.company_code = sm.company_code LEFT JOIN Manager m ON c.company_code = m.company_code LEFT JOIN Employee e ON c.company_code = e.company_code | cs |
_ 테이블을 조인할 때 JOIN~ON~절 대신에 FROM절에 전체 테이블을 넣고, WHERE절에 조건을 넣어도 됨
1 2 3 4 5 6 7 8 9 10 | SELECT * FROM Company c , Lead_Manager lm, Senior_Manager sm, Manager m, Employee e WHERE c.company_code = lm.company_code AND c.company_code = sm.company_code AND c.company_code = m.company_code AND c.company_code = e.company_code | cs |
# 오답
ORDER BY절 때문에 틀림
문제에서 원하는 결과 값은 company_code가 C1, C10, C100, C11, .. , 이런 순서로 오는 것이었는데
나는 결과 값이 C1, C2,.. C9, C10,.. 이런 순서로 와야 되는 줄 알고 ORDER BY절에
company_code의 길이로 먼저 오름차순을 해줘서 C 뒤의 숫자가 [일, 십, 백] 중 어느 자리의 숫자인지에 따라 정렬하게 함
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT c.company_code, c.founder, COUNT(DISTINCT e.lead_manager_code), COUNT(DISTINCT e.senior_manager_code), COUNT(DISTINCT e.manager_code), COUNT(DISTINCT e.employee_code) FROM Company c LEFT JOIN Lead_Manager lm ON c.company_code = lm.company_code LEFT JOIN Senior_Manager sm ON c.company_code = sm.company_code LEFT JOIN Manager m ON c.company_code = m.company_code LEFT JOIN Employee e ON c.company_code = e.company_code GROUP BY 1, 2 ORDER BY LENGTH(c.company_code), c.company_code | cs |
# 정답
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT c.company_code, c.founder, COUNT(DISTINCT e.lead_manager_code), COUNT(DISTINCT e.senior_manager_code), COUNT(DISTINCT e.manager_code), COUNT(DISTINCT e.employee_code) FROM Company c LEFT JOIN Lead_Manager lm ON c.company_code = lm.company_code LEFT JOIN Senior_Manager sm ON c.company_code = sm.company_code LEFT JOIN Manager m ON c.company_code = m.company_code LEFT JOIN Employee e ON c.company_code = e.company_code GROUP BY 1, 2 ORDER BY c.company_code | cs |
다른 풀이
1 2 3 4 5 6 | select c.company_code,c.founder, (select count(*) from Lead_Manager where company_code=c.company_code ) , (select count(*) from Senior_Manager where company_code=c.company_code ), (select count(*) from Manager where company_code=c.company_code ), (select count(*) from Employee where company_code=c.company_code ) from Company as c order by c.company_code | cs |
SELECT절 내 서브쿼리로 문제를 풂
배운점
_ C1, C2,.. C9, C10,.. 순서 정렬을 어떻게 만들어야 할 지 고민하다가 알게 된 함수들
- SUBSTRING_INDEX('문자열', '구분자', '구분자 INDEX')
- SUBSTRING_INDEX('가-나-다-라-마-바-사', '-', 4) >> 결과 : 가-나-다-라
- SUBSTRING_INDEX( '가-나-다-라-마-바-사', '-', -3) >> 결과 : 사-바-마
- REPLACE('문자열', '바꾸고 싶은 문자열', '바뀌게 될 모습')
- CAST(데이터 AS 데이터 타입)
- 데이터 타입
- CHAR : 정수 >> 문자
- SIGNED : 문자 >> 정수 (음수, 양수 구분O) ↔ UNSIGNED (오직 양수만 OK)
- DATE : 문자, 정수 >> 날짜
- 데이터 타입
1 2 3 4 5 6 | SUBSTRING_INDEX('문자열', '구분자', '구분자 INDEX') # 구분자를 이용해 문자열을 잘라오는 함수 # C1, C2, ... 이런 데이터에서 # C를 없애고 # 남아있는 1, 2,...를 SIGNED 타입으로 데이터 형 변환 ORDER BY CAST(REPLACE(c.company_code, 'C', '') AS SIGNED) | cs |
https://for-my-wealthy-life.tistory.com/5
LEFT JOIN 중복제거 DISTINCT와 GROUP BY
LEFT JOIN은 아마도 SQL 쿼리문을 짜면서 가장 많이 쓰이는 문법 중 하나가 아닐까 싶다. JOIN에는 여러 종류가 있다. 크게 나누자면 1.LEFT 2.INNER 3.RIGHT인데 사실 RIGHT JOIN은 거의 사용하지 않고 대부분
for-my-wealthy-life.tistory.com