MySQL 해커랭크 | New Companies ( 테이블 조인, LENGTH, SUBSTRING_INDEX, CAST )

 

문제

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

New Companies | HackerRank

[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

 

풀이과정

# 전체 테이블 조인

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

 

_ 테이블을 조인할 때 JOIN~ON~절 대신에 FROM절에 전체 테이블을 넣고, WHERE절에 조건을 넣어도 됨 

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

 

# 오답

ORDER BY절 때문에 틀림

문제에서 원하는 결과 값은 company_code가 C1, C10, C100, C11, .. , 이런 순서로 오는 것이었는데

나는 결과 값이 C1, C2,.. C9, C10,.. 이런 순서로 와야 되는 줄 알고 ORDER BY절에

company_code의 길이로 먼저 오름차순을 해줘서 C 뒤의 숫자가 [일, 십, 백] 중 어느 자리의 숫자인지에 따라 정렬하게 함

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

 

# 정답

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

 

다른 풀이

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

SELECT절 내 서브쿼리로 문제를 풂

 

배운점

_ C1, C2,.. C9, C10,.. 순서 정렬을 어떻게 만들어야 할 지 고민하다가 알게 된 함수들

• SUBSTRING_INDEX('문자열', '구분자', '구분자 INDEX')
  • SUBSTRING_INDEX('가-나-다-라-마-바-사', '-', 4)  >> 결과 : 가-나-다-라
  • SUBSTRING_INDEX( '가-나-다-라-마-바-사', '-', -3)  >> 결과 : 사-바-마
• REPLACE('문자열', '바꾸고 싶은 문자열', '바뀌게 될 모습')
• CAST(데이터 AS 데이터 타입)
  • 데이터 타입
    • CHAR : 정수 >> 문자
    • SIGNED : 문자 >> 정수 (음수, 양수 구분O)   ↔ UNSIGNED (오직 양수만 OK)
    • DATE : 문자, 정수 >> 날짜

SUBSTRING_INDEX('문자열', '구분자', '구분자 INDEX')  # 구분자를 이용해 문자열을 잘라오는 함수
      
# C1, C2, ... 이런 데이터에서 
# C를 없애고 
# 남아있는 1, 2,...를 SIGNED 타입으로 데이터 형 변환  
ORDER BY CAST(REPLACE(c.company_code, 'C', '') AS SIGNED)

---

https://for-my-wealthy-life.tistory.com/5

LEFT JOIN 중복제거 DISTINCT와 GROUP BY